Link to home
Start Free TrialLog in
Avatar of shahjagat
shahjagatFlag for United States of America

asked on

Read .xlsx file

I'm trying to read .xlsx file and populate a DataTable.
I've added test.xlsx  file to the project.
Error: The Microsoft Office Access database engine could not find the object 'Sheet1'.  Make sure the object exists and that you spell its name and the path name correctly.

  <connectionStrings>
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=Excel 12.0"/>  
  </connectionStrings>

Private xlsx As String = ConfigurationManager.ConnectionStrings("xlsx").ConnectionString

Public Function GetData() as DataTable
 Dim dt As New DataTable
        Dim oleDbCon As New OleDbConnection(xlsx)
        oleDbCon.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1]", oleDbCon)
        Dim oleda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        oleda.Fill(dt)
oleDbCon.Close()
return dt
End Sub

Any help would be greatly appreciated!!
ASKER CERTIFIED SOLUTION
Avatar of Imran Javed Zia
Imran Javed Zia
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
as suggested by IJZ, what's important is the missing "$" at the end of the sheet name, so this would work also:
SELECT * FROM [Sheet1$]

Open in new window