Importing Named Excel worksheets to Access (2000 (9.0.6926 SP-3))

[Niven]

Hi!

I am going to be receiving a workbook with a number of worksheet tabs on a weekly basis from a new client. The tab I am interested in will be titled "ADD TO JNL DDMMYY" (Where DDMMYY represent a weekly date). If the tab is arranged at the front of the workbook then the "DoCmd.TransferSpreadsheet " function will import the tab without having to add a range at the end. I have no control as to the order of how the worksheets will be arranged and as such need to be able to select the Tab in question using VB. The command I am using is:-

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ADD TO JNL 070606", "J:HAssocBroomleighDanny Hall" & strFileName, True, "'ADD TO JNL 070606'!A1:R1000"

If I omit the single quotes around the range then I get a run time error '3129' with an Invalid SQL statement etc message, but with the quotes I get a run time error '3011' with the message "The Microsoft Jet Database Engine could not find the object "ADD TO JNL 070606'$A1:R1000'. Make sure the object exists and that you spelt its name and the path correctly." I don't have a problem with tabs which don't have any spaces between the words.

Can anyone help here, please?

Cheers,

Niven

[HansV]

Try
<code>
..., "'ADD TO JNL 070606$'"
</code>
i.e. add a $ at the end of the sheet name, and omit the range.

[Niven]

Top Stuff! Probably me, but I couldn't seem to find anything anywhere that resolved the spaces in Tab names situation.

Cheers again Hans and are you interested in the world cup?

Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[HansV]

> are you interested in the world cup?

No <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>