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
LVL 2
Chi Is CurrentAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
It is something like:

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

/gustav
0
 
Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

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

Open in new window

Regards
0
 
Chi Is CurrentAuthor Commented:
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!

???
0
 
Chi Is CurrentAuthor Commented:
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...
0
 
Chi Is CurrentAuthor Commented:
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???
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.