Using TransferSpreadsheet Macro to import a single sheet from exce

  • Thread starter Thread starter PerryK
  • Start date Start date
P

PerryK

I have an excel workbook with multiple sheets of data.

I am trying to use a TransferSpreadsheet Macro to import 2 individual sheets
to seperate tables in access. (I have a seperate Macro for each sheet)

What is the correct "format" to identify the individual sheet in the Path?

As an example the file path is:
C:\Documents and Settings\My Documents\Monthly reporting\Monthy Reports for
Access Import.xls

One of the sheets is labled "Financial Data" - How do I add this to the end
of the path so it imports this sheet?

The error I get is "not a valid path" if I try to add the sheet name to end
of the path



Thanks in advance,
Perry K
 
I have an excel workbook with multiple sheets of data.

I am trying to use a TransferSpreadsheet Macro to import 2 individual sheets
to seperate tables in access. (I have a seperate Macro for each sheet)

What is the correct "format" to identify the individual sheet in the Path?

As an example the file path is:
C:\Documents and Settings\My Documents\Monthly reporting\Monthy Reports for
Access Import.xls

One of the sheets is labled "Financial Data" - How do I add this to the end
of the path so it imports this sheet?

The error I get is "not a valid path" if I try to add the sheet name to end
of the path


Thanks in advance,
Perry K
Click to expand...

Use the Range argument.
Did you not read VBA help on the TransferSpreadsheet method?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyTableName", "c:\FolderName\SpreadsheetName.xls", False, "Financial
Data!A3:A100"

The above will import Column A, rows 3 through 100.
 
Back
Top