Solved: Error when making Excel Worksheet into Linked Table

[CreganTur]

I've got some code that opens a file dialog window so that the user can select Excel files. The code then grabs the filepath of the Excel spreadsheet, grabs the name of the worksheets in the spreadsheet, and then creates a Linked Table out of the specified filepath and worksheet name.

This code works perfectly, but only for the first worksheet in the collection. If there is more than 1 worksheet then the code will throw error 3011 when it tries to make the second worksheet into a linked table.

Here's the code for getting the worksheet name and the code for creating a linked table:

Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbas As Object, tempTable As String, spaceIn As Integer
Dim count As Integer
excelname = strFilepath
Set Wkb = AppExcel.Workbooks.Open(strFilepath)
For Each Wksh In Wkb.Worksheets

'--------------------
'make the selected Excel file into a Linked Table
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Dim LinkExcel As Variant
Set myDB = CurrentDb()
stSource = Wksh.Name
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
'Make chosen Excel file into a Linked Table titled mySheet
Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True

I've also attached a sample database that shows the entire process in action. It includes 2 sample spreadsheets (the data in them is fake). If you look at the database, then be sure to click on the "Load Data From Customers Excel Spreadsheet" button- it has this code behind it. The other button has an older version of code.

[matthewspatrick]

Rather than trying to do it by creating a new TableDef, have you tried using DoCmd.TransferSpreadsheet?

[CreganTur]

Rather than trying to do it by creating a new TableDef, have you tried using DoCmd.TransferSpreadsheet?

I can't do it that way because I only need 6 columns from the spreadsheet(that has 20). Linking the table allows me to validate it quickly (by seeing if the 6 required tables exist) and then pull in only the data I want (via an update query that takes data from the linked table and uses it to update an existing table).

[CreganTur]

I finally figured out what the problem was.

Using the above method causes the "$" to be dropped from the end of the worksheet name. That's what was causing the error. I fixed it by using:


Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbas As Object, tempTable As String, spaceIn As Integer
Dim count As Integer
excelname = strFilepath
Set Wkb = AppExcel.Workbooks.Open(strFilepath)
For Each Wksh In Wkb.Worksheets


'--------------------
'make the selected Excel file into a Linked Table
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String
Dim LinkExcel As Variant
Set myDB = CurrentDb()
stSource = Wksh.Name & "$"
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFilepath
'Make chosen Excel file into a Linked Table titled mySheet
Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource
myDB.TableDefs.Append tbl
LinkExcel = True