Prev: IIF(AND) statement
Next: Exporting Access Report to Excel
From: PerryK on 7 Apr 2010 16:25
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
From: fredg on 7 Apr 2010 18:15
On Wed, 7 Apr 2010 13:25:01 -0700, PerryK wrote:

> 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

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
 | 
Pages: 1
Prev: IIF(AND) statement
Next: Exporting Access Report to Excel