Vb code to link tables to Excel

[Hcoms]

Hello,

I want to use vb code to mimick the "get external data" -> Link tables ->
Excel sheet .
I have a number of excel sheets within a number of excel workbooks and i
would like to automate this process. Any ideas on the code to link excel
docs to an access database? I am using access 2000

Cheers

 

[Steve]

Use the TransferSpreadsheet command;

DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel97, "**TABLE
NAME**", "**FILENAME**", True

Replace **TABLE NAME** and **FILENAME** with the table
name for the Link and Filename for the spreadsheet.

The True at the end denotes wether the spreadsheets have
Column names or not....

Steve.

 

[Hcoms]

Thanks for the quick response! I have now figured out thanks to your help
how to import a sheet e.g. Jan04! , How can import just column B in sheet
Jan04?

Cheers

 

[Steve]

You will need to give Column B a range name on your
spreadsheet.

Then in the TransferSpreadsheet command, specify the range
name after Spreadsheet has column names;

DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel97, "**TABLE
NAME**", "**FILENAME**", True, "**RANGE NAME**"

 

[Hcoms]

Cheers , i actual figured this one out by doing the following:
Sheetname!B:B
as the range