By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,501 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Link tables by code

P: n/a
I want to link several worksheets of a workbook as linked tables.
This is the vba-code I use, which don't work

Any suggestions are welcome

sub LinkExcel(strWorkBook)
Dim appXLS as Excel.Application
Dim wrk as Excel.Workbook
Dim sht as Excel.WorkSheet

Dim tdf as DAO.tableDef

Set appXLS = New Excel.Application
Set wrb = xlApp.Workbooks.Open(strWorkbook)

For Each sht In wrb.Worksheets
Set tdf = CurrentDb.CreateTableDef(sht.name)
tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strWorkbook
tdf.SourceTableName = sht.name
CurrentDb.TableDefs.Append tdf
Next

CurrentDb.TableDefs.Refresh
Application.RefreshDatabaseWindow
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try this:
tdf.SourceTableName = sht.name & "$"

"Antoon Vanderplancke" <av******@boterbloem.be> wrote in message
news:43**********************@news.skynet.be...
I want to link several worksheets of a workbook as linked tables.
This is the vba-code I use, which don't work

Any suggestions are welcome

sub LinkExcel(strWorkBook)
Dim appXLS as Excel.Application
Dim wrk as Excel.Workbook
Dim sht as Excel.WorkSheet

Dim tdf as DAO.tableDef

Set appXLS = New Excel.Application
Set wrb = xlApp.Workbooks.Open(strWorkbook)

For Each sht In wrb.Worksheets
Set tdf = CurrentDb.CreateTableDef(sht.name)
tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strWorkbook
tdf.SourceTableName = sht.name
CurrentDb.TableDefs.Append tdf
Next

CurrentDb.TableDefs.Refresh
Application.RefreshDatabaseWindow

Nov 13 '05 #2

P: n/a
Wonderful ! Yes indeed all worksheets are linked now. I did a lot of
searching but I can't remember I saw something like the trailing "$"
Thank you very much !

"MacDermott" <ma********@nospam.com> schreef in bericht
news:J3*****************@newsread3.news.atl.earthl ink.net...
Try this:
tdf.SourceTableName = sht.name & "$"

"Antoon Vanderplancke" <av******@boterbloem.be> wrote in message
news:43**********************@news.skynet.be...
I want to link several worksheets of a workbook as linked tables.
This is the vba-code I use, which don't work

Any suggestions are welcome

sub LinkExcel(strWorkBook)
Dim appXLS as Excel.Application
Dim wrk as Excel.Workbook
Dim sht as Excel.WorkSheet

Dim tdf as DAO.tableDef

Set appXLS = New Excel.Application
Set wrb = xlApp.Workbooks.Open(strWorkbook)

For Each sht In wrb.Worksheets
Set tdf = CurrentDb.CreateTableDef(sht.name)
tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strWorkbook
tdf.SourceTableName = sht.name
CurrentDb.TableDefs.Append tdf
Next

CurrentDb.TableDefs.Refresh
Application.RefreshDatabaseWindow


Nov 13 '05 #3

P: n/a
Open any linked table in Design View (you'll be warned that you can't make
changes to the design). Display the table's Property Sheet. If it hasn't
been overwritten, the Description property will show information which can
be useful in creating such a link programmatically. It's often not in
exactly the format you need, but it can provide some useful insights.

"Antoon Vanderplancke" <av******@boterbloem.be> wrote in message
news:43**********************@news.skynet.be...
Wonderful ! Yes indeed all worksheets are linked now. I did a lot of
searching but I can't remember I saw something like the trailing "$"
Thank you very much !

"MacDermott" <ma********@nospam.com> schreef in bericht
news:J3*****************@newsread3.news.atl.earthl ink.net...
Try this:
tdf.SourceTableName = sht.name & "$"

"Antoon Vanderplancke" <av******@boterbloem.be> wrote in message
news:43**********************@news.skynet.be...
I want to link several worksheets of a workbook as linked tables.
This is the vba-code I use, which don't work

Any suggestions are welcome

sub LinkExcel(strWorkBook)
Dim appXLS as Excel.Application
Dim wrk as Excel.Workbook
Dim sht as Excel.WorkSheet

Dim tdf as DAO.tableDef

Set appXLS = New Excel.Application
Set wrb = xlApp.Workbooks.Open(strWorkbook)

For Each sht In wrb.Worksheets
Set tdf = CurrentDb.CreateTableDef(sht.name)
tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strWorkbook
tdf.SourceTableName = sht.name
CurrentDb.TableDefs.Append tdf
Next

CurrentDb.TableDefs.Refresh
Application.RefreshDatabaseWindow



Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.