I have a database that links to an Excel spreadsheet. When adding the linked table manually, I have no problems (I just follow the little wizard and the table is created perfectly). I am trying to automate that using VBA:
Dim db as DAO.Database
Dim tdf as TableDef
Set db = CurrentDB
Set tdf = db.CreateTableDef("linked_table")
tdf.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Me.txtExcelFile ' references form field
db.TableDefs.Append tdf ' Here's where I get a run time error
Unfortunately I get run-time error 3264 on the last line and it says, "No field defined--cannot append TableDefs or Index." I'm not even sure why the last line is needed (it seems from the documentation I've found that it's to add the table to the TableDefs collection of the database). Any suggestions on resolving the error?
You are missing:
tdf.SourceTableName = "Sheet1$"
Or whatever range or sheet name you wish to use as the table.
So, in all, this works for me:
Dim db as DAO.Database
Dim tdf as TableDef
Set db = CurrentDB
Set tdf = db.CreateTableDef("linked_table")
tdf.SourceTableName = "Sheet1$"
tdf.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Me.txtExcelFile ' references form field
db.TableDefs.Append tdf
You can also link Excel using TransferSpreadsheet
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With