Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi,
I am trying to load excel sheet data into grid using dataset. but I am getting this error "The Microsoft Access database engine could not find the object 'Sheet1$'."
 
below is the code. could anyone tell me tell where i am doing wrong. the excel sheet I am trying to load already has Sheet1. Also connection string is correct.
oledbConn.Open()
            Dim comm As OleDbCommand = New OleDbCommand("Select * from [Sheet1$]", oledbConn)
            Dim oleda As New OleDbDataAdapter(comm)
            oleda.SelectCommand() = comm
 
            Dim ds As New DataSet()
            oleda.Fill(ds)
            gdExcel.DataSource = ds.Tables(0).DefaultView
            gdExcel.DataBind()
        Catch ex As Exception
            MsgBox("error", vbInformation)
 

        Finally
            oledbConn.Close()
Posted 26-Nov-12 8:39am
Comments
richcb at 26-Nov-12 13:55pm
   
Are you sure that the sheet name is "Sheet1"? If it says anything but that it will not be found.
phil.o at 4-Sep-13 8:51am
   
You are trying to open an Excel file using an Access data-access component. It's obvious that it cannot work :)
almounkez at 26-Nov-12 16:10pm
   
it depend on the ms office version
if the excel file is 2010 and the processor is 64 bit you need a new jet driver
almounkez at 26-Nov-12 16:12pm
   
If "JetVersion" = 8 Then
excelConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", m_filename)
Else
excelConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", m_filename)
End If

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

USE this code hope you got your ans.
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0 Xml;HDR=YES""";
                OleDbConnection myData = new OleDbConnection(strConn);
                try
                {
                    myData.Open();
                }
                catch (OleDbException ex)
                {
                }
int i = 0;
                foreach (DataRow row in myData.GetSchema("tables").Rows)
                    try
                    {
                        i++;
                        string name = row[2].ToString().Replace("''", "'").TrimEnd('_');
                        DataSet ds = new DataSet();
                        OleDbDataAdapter d = new OleDbDataAdapter("SELECT * from [" + name + "]", strConn);                       
                        d.Fill(ds);                       
                        
                    }
                    catch (Exception ex)
                    {
                    }
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 4 Sep 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100