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.

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

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

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

Any help would be greatly appreciated!!
Who is Participating?
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Please try this

Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Sheet1$", oleDbCon)

further more use connection string as
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"
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

Éric MoreauSenior .Net ConsultantCommented:
I prefer to use tools such as to manipulate Excel files
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.