Link table from a spreadsheet in Excel to access 1

[Cogivina2004]

I use this code to import the table from Excel to access:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "MyTable", "C:\MyFile.xls", True

How do I write code to link the MyTable to access instead?

Thanks.

 

[SuicidED]

Hi
DoCmd.TransferSpreadsheet acLink,8, "MyTable", "C:\Myfile.xls", True, ""

 

[Cogivina2004]

Thanks, SuicidEd.

I have two tables, MyTable1 and MyTable2, in the spreadsheet. The link works for both but the problem is the MyTable2 link is the same as MyTable1. On MyTabl2 link, it contains all the MyTable1 value.

DoCmd.TransferSpreadsheet acLink,8, "MyTable1", "C:\Myfile.xls", True, ""

DoCmd.TransferSpreadsheet acLink,8, "MyTable2", "C:\Myfile.xls", True, ""

What does the 8 stand or use for?

Thanks again.

 

[SuicidED]

The 8 stands for excel 97.

 

[Cogivina2004]

How do I get the two tables from Excel linked to access? It seems like it works for the spreadsheet with only one table.

DoCmd.TransferSpreadsheet acLink,8, "MyTable1", "C:\Myfile.xls", True, ""

DoCmd.TransferSpreadsheet acLink,8, "MyTable2", "C:\Myfile.xls", True, ""

 

[SuicidED]

As a quick reply

The excel spreadsheets you are linking have the same name. Surely they must have different names?

 

[Cogivina2004]

In the Excel MyFile.xls spreadsheets, I have several tabs (MyTable1, MyTable2,..). I'd like to use the same names in access after they are linked.

Can I link multi tables within a spreadsheet in access?

 

[SuicidED]

Hi
DoCmd.TransferSpreadsheet acLink, 8, "My Table", "C:\Temp\1601000", True, "abc$"
DoCmd.TransferSpreadsheet acLink, 8, "My Table", "C:\Temp\1601000", True, "def$"

The last bit being the names of your worksheet followed by a dollar sign $