Link tables by code

[Antoon Vanderplancke]

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(strWo rkBook)
Dim appXLS as Excel.Applicati on
Dim wrk as Excel.Workbook
Dim sht as Excel.WorkSheet

Dim tdf as DAO.tableDef

Set appXLS = New Excel.Applicati on
Set wrb = xlApp.Workbooks .Open(strWorkbo ok)

For Each sht In wrb.Worksheets
Set tdf = CurrentDb.Creat eTableDef(sht.n ame)
tdf.Connect = "Excel 5.0;HDR=YES;IME X=2;DATABASE=" & strWorkbook
tdf.SourceTable Name = sht.name
CurrentDb.Table Defs.Append tdf
Next

CurrentDb.Table Defs.Refresh
Application.Ref reshDatabaseWin dow

[MacDermott]

Re: Link tables by code

Try this:
tdf.SourceTable Name = sht.name & "$"

"Antoon Vanderplancke" <avdplanc@boter bloem.be> wrote in message
news:436bcdec$0 $5359$ba620e4c@ news.skynet.be. ..[color=blue]
> 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(strWo rkBook)
> Dim appXLS as Excel.Applicati on
> Dim wrk as Excel.Workbook
> Dim sht as Excel.WorkSheet
>
> Dim tdf as DAO.tableDef
>
> Set appXLS = New Excel.Applicati on
> Set wrb = xlApp.Workbooks .Open(strWorkbo ok)
>
> For Each sht In wrb.Worksheets
> Set tdf = CurrentDb.Creat eTableDef(sht.n ame)
> tdf.Connect = "Excel 5.0;HDR=YES;IME X=2;DATABASE=" & strWorkbook
> tdf.SourceTable Name = sht.name
> CurrentDb.Table Defs.Append tdf
> Next
>
> CurrentDb.Table Defs.Refresh
> Application.Ref reshDatabaseWin dow
>
>[/color]

[Antoon Vanderplancke]

Re: Link tables by code

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 " <macdermott@nos pam.com> schreef in bericht
news:J3cbf.5883 $AS6.3661@newsr ead3.news.atl.e arthlink.net...[color=blue]
> Try this:
> tdf.SourceTable Name = sht.name & "$"
>
> "Antoon Vanderplancke" <avdplanc@boter bloem.be> wrote in message
> news:436bcdec$0 $5359$ba620e4c@ news.skynet.be. ..[color=green]
>> 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(strWo rkBook)
>> Dim appXLS as Excel.Applicati on
>> Dim wrk as Excel.Workbook
>> Dim sht as Excel.WorkSheet
>>
>> Dim tdf as DAO.tableDef
>>
>> Set appXLS = New Excel.Applicati on
>> Set wrb = xlApp.Workbooks .Open(strWorkbo ok)
>>
>> For Each sht In wrb.Worksheets
>> Set tdf = CurrentDb.Creat eTableDef(sht.n ame)
>> tdf.Connect = "Excel 5.0;HDR=YES;IME X=2;DATABASE=" & strWorkbook
>> tdf.SourceTable Name = sht.name
>> CurrentDb.Table Defs.Append tdf
>> Next
>>
>> CurrentDb.Table Defs.Refresh
>> Application.Ref reshDatabaseWin dow
>>
>>[/color]
>
>[/color]

[MacDermott]

Re: Link tables by code

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 programmaticall y. It's often not in
exactly the format you need, but it can provide some useful insights.



"Antoon Vanderplancke" <avdplanc@boter bloem.be> wrote in message
news:436e5ea3$0 $5447$ba620e4c@ news.skynet.be. ..[color=blue]
> 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 " <macdermott@nos pam.com> schreef in bericht
> news:J3cbf.5883 $AS6.3661@newsr ead3.news.atl.e arthlink.net...[color=green]
> > Try this:
> > tdf.SourceTable Name = sht.name & "$"
> >
> > "Antoon Vanderplancke" <avdplanc@boter bloem.be> wrote in message
> > news:436bcdec$0 $5359$ba620e4c@ news.skynet.be. ..[color=darkred]
> >> 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(strWo rkBook)
> >> Dim appXLS as Excel.Applicati on
> >> Dim wrk as Excel.Workbook
> >> Dim sht as Excel.WorkSheet
> >>
> >> Dim tdf as DAO.tableDef
> >>
> >> Set appXLS = New Excel.Applicati on
> >> Set wrb = xlApp.Workbooks .Open(strWorkbo ok)
> >>
> >> For Each sht In wrb.Worksheets
> >> Set tdf = CurrentDb.Creat eTableDef(sht.n ame)
> >> tdf.Connect = "Excel 5.0;HDR=YES;IME X=2;DATABASE=" & strWorkbook
> >> tdf.SourceTable Name = sht.name
> >> CurrentDb.Table Defs.Append tdf
> >> Next
> >>
> >> CurrentDb.Table Defs.Refresh
> >> Application.Ref reshDatabaseWin dow
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]