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?