I have a project where I have to import certain data from excel spreadsheets into Access. I have everything working except for one snafu. Some of the Excel workbooks have multiple sheets and those sheets have spaces in the name like Page 1, Page 2, etc.
These Excel workbooks are created by converting pdfs.
The program I am using is great, it converts them exactly as I need them except for the space in the sheet name.
I cannot manually change the sheet names because there are over 5000 individual pdfs (with anywhere from 1 to 8 sheets each) so I really need to automate this.
I have tried putting single quotes around the name and range ('Page 1!A8:A8'), single quotes around just the page name ('Page 1'!A8:A8), and all sorts of other things (like brackets).
Oh I am using the docmd.transferspreadsheet acimport (this was my latest try):
mysheetname was already set to Page 1 earlier since during the loop it will have to be set to Page 2 then Page 3 etc
RangeExcel = "!A8:A8"
ComboSR = "[" & mySheetName & "]" & RangeExcel
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Test of Importing QUotes", "C:\Users\New Laptop\Desktop\Optigrate\QuotesPDF\PdfToExcel\PdfToExcel\Q-2014-0929-MUn-1", False, ComboSR