I've always use DoCmd.TransferSpreadsheet to link to Excel files, so I'm not
sure of the details in doing it via DAO. However, I think on this line
Quote:
> .SourceTableName = Ceny
if "Ceny" is the name of the worksheet, it should be enclosed in quotes. If
it's a variable, I don't see where it's defined, so you may be getting an
error there.
I don't know if you need to define the fields or not -- the example in the
help file under "Connect and SourceTableName Properties Example" doesn't do
that, but I'm unsure of the ramifications.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
I use the followong code to link a table to an external
Excel file but the linked table is empty although the
Excel file contains values? Am I missing anything?
Dim wsp As Workspace
Dim dbsCurrent As Database
Dim Tabulka As TableDef
Dim ConnData As Connection
Set dbsCurrent = CurrentDb
Set Tabulka = dbsCurrent.CreateTableDef("Cenk_test")
With Tabulka
.Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=P:\Uzvěrka\Česky
Telecom\International\05_2002\CT_cenk_od_04_2002_test.xls"
.SourceTableName = Ceny
End With
Set Sloupec = Tabulka.CreateField("Destinace", dbText)
Tabulka.Fields.Append Sloupec
Set Sloupec = Tabulka.CreateField("CENA (Kč/min)",
dbText)
Tabulka.Fields.Append Sloupec
dbsCurrent.TableDefs.Append Tabulka
linked table empty