How to create a link table at runtime to a sheet in an Excel workbook

[PAUL RIDINGS]

Hi

I have a 25 sheet excel workbook and I need to access the different sheets
of the workbook to update it from access 97. I didn't want to create 25
link tables, so I thought it would be better if I created the link table to
the sheet I wanted to update then removed it afterwards.

Has anyone got some code to create a link table, or is it only possible from
the database window?

Thanks

Paul

 

[Tom Wickerath]

Hi Paul,

If you send me a private e-mail, with a valid return address, I will send you a zipped Access 97
database that can create either linked or local tables on the fly, by selecting a text file using
a common dialog (API code based common dialog). I don't think it would be that difficult to
modify the code to allow creating links to an excel workbook in code. There is probably code
available somewhere to iterate a workbook for all existing worksheet names, which one could then
pump into a listbox.

In answer to your question, it is definitely possible to create linked tables using VBA code.

Tom
______________________________________

Hi

I have a 25 sheet excel workbook and I need to access the different sheets
of the workbook to update it from access 97. I didn't want to create 25
link tables, so I thought it would be better if I created the link table to
the sheet I wanted to update then removed it afterwards.

Has anyone got some code to create a link table, or is it only possible from
the database window?

Thanks

Paul