5

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?

rryanp
  • 1,027
  • 8
  • 26
  • 45

1 Answers1

4

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • @Remou--you have done it again! I feel like I should add you to my holiday card list. :) I appreciate it. Out of curiosity, what is the "$" at the end of the sheet name for? I used it, and it worked--just curious. – rryanp Sep 12 '12 at 20:29
  • 2
    The $ means use the whole sheet to get the table, although it only selects the used range. The alternatives would be a named range or, say, `Sheet1$a1:x24` – Fionnuala Sep 12 '12 at 20:51