Hi! I am interested in querying an Excel workbook from Access. Currently the spreadsheet that I am querying is in all text format(including the numbers) and I want to query the spreadsheet from Access and append the data to a table with the correct data types. Is this possible? Know any where I can take a look at some sample code that does this?
Here is some code to read an excel spreadsheet, change the cell range, path, file name and sheet name to match yours. I have printed the results to the immediate window so change this to write to your table.
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim strWSht As String
Dim strWBk As String
Dim n As Long
It contains the connection string for the current access database. Although we are bringin back data from excel the syntax expects a connection string, so the CurrentProject.Connection is simply the connection settings of the database