Transfer data from Excel to Access

[CyberDwarf]

Hi



I need to pull data from an Excel worksheet to a data table, using Access &
VBA.



I am trying to use the "DoCmd.TransferSpreadsheet acImport" method, but...



My problem is that the sheet I need to get at is one of four in the workbook
& is called "finance codes". I can pick up the data from sheet 1, but I
can't seem to grab the data from the sheet I need....



All help gratefully received



TIA



Steve

 

[Bill Edwards]

Try adding the bang operator after the name of the Worksheet ("Finance
Codes!")
Example:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblTestImport", strFileName, True, "Finance Codes!"

 

[CyberDwarf]

Bill

Thx for your reply

Tried that already... doesn't seem to like the syntax somehow. Mebbe
something to do with the space in the sheet name?

TIA

Steve

 

[dbahooker]

use a real ETL tool; SQL Server let's you use DTS for example;
MDB-based transferspreadsheet is for babies

for the record; I think that this is the real syntax:
C:\MySpreadsheet.xls$Sheet1
C:\MySpreadsheet.xls$Sheet2
C:\MySpreadsheet.xls$Sheet3

 

[Ken Snell \(MVP\)]

When a sheet name has a space in it, you must delimit it with ' characters:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblTestImport", strFileName, True, "'Finance Codes'!"