Click here to Skip to main content
15,877,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have many Excel files which I need to import to access several times. The sheet names are also different, like it's not sheet every time.

Can you please help me with the below code and make so that it is possible to access the files without knowing the sheet name, so that I can transfer data?

What I have tried:

Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filename + ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
	Using cmd As New OleDbCommand()
		cmd.Connection = conn
		cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [Sheet1$]"
	End Using
Updated 28-Jan-22 6:05am

One way is you get all the worksheets of your workbook and after that traverse the workbook and insert your data like:
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\\YourWorkbook.xlsx");

foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)
         cmd.Connection = conn
         cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [" + wSheet.Name + "]";

Second way is, If your worksheets name is same as your database tables then you can get schema of you database and traverse on the tables like:
var excelWorksheets = conn.GetSchema("Tables");
foreach (var row in excelWorksheets.Rows)
        cmd.Connection = conn
        cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [" + row["TABLE_NAME"] + "]";

Choice is yours. Happy Coding!!
Share this answer
You can get the names of all the WorkSheets in OLEDB with the following:
oleDbConnection = new OleDbConnection(ConnectionString);
DataTable dtSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// Use LINQ to get the table (i.e. sheet) names from the spreadsheet schema
List<object> worksheets = (from dr in dtSchema.AsEnumerable()   // returns an IEnumerable<DataRow> collection
                           select dr["TABLE_NAME"]).ToList();   // gets the sheet name from each schema entry

List<string> sheets = new List<string>();
foreach (string name in worksheets)
    if (!name.StartsWith("_"))

I know this is C# code, but VB.NET is much the same.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900