Vb code to link tables to Excel

  • Thread starter Thread starter Hcoms
  • Start date Start date
H

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
 
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.
 
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
 
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**"
 
Back
Top