Click here to Skip to main content
12,633,764 members (32,379 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB 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 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 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 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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 4 Sep 2013
Copyright © CodeProject, 1999-2016
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