asked on

Access VBA Reference Specific Excel Worksheet

Hello ~ I'm looking for the syntax to reference a specific worksheet within an Excel file, "Song.xlsx", to import into an Access table. The worksheet is the #2 tab and is named "Tune".

I have tried:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTest", "U:\Song.xlsx.Sheets(2)", True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTest", "U:\Song.xlsx.Sheets(Tune)", True

No Joy!

'Would appreciate ideas.
'Will ck back in AM.

Jacob

Rgonzo1971

Hi,

pls try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTest", "U:\Song.xlsx", True, "Tune!"

Open in new window

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTest", "U:\Song.xlsx", True, "Tune!A1:C5"

Open in new window

Regards

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chi Is Current

ASKER

Hello Rgonzo1971 and /Gustav ~

Thank you both for your replies.

Examples: #1 and #3, above, DO run, DO create a new table and DO find the "Tune" tab (!);
however both print only column names and neither imports any of the data on the spreadsheet!

???

Chi Is Current

ASKER

Ahhhhhhhhh......
Looking at the import Errors log, I'm seeing many "Type Conversion Failure" errors.

I'm surprised, as the tblTEST is being created each time...

Chi Is Current

ASKER

OOOOOOOOOOK, I am seeing "Type Conversion Failure" errors for data in fields that does not conform to datatype of the target table's fields.

Even when all target table fields' datatypes are set to TEXT, import fails for records containing HYPHENS in some fields....

Any way to allow importing hyphens into text fields???