TransferSpreadsheet command - use of Ampersand in Worksheet names

[AndrewB]

I have a facility whereby I import data from Excel spreadsheets into Access using some VBA code.

The users have the ability to add new worksheets to their files (seldom used in practice) and I add these new names to my master list, together with some other attributes, and all sheets are thereafter automatically imported using DoCmd.Transferspreadsheet.

The problem for me is that someone has added a worksheet name with an ampersand in it. VBA then fails to import it (I've suppressed the messages so can't remember what (if anything) was reported when it failed) and chugs onto the next sheet.

Is there any way around this - should I hold the sheet name with a double ampersand in my master list? Otherwise, should I just keep asking users not to give Excel sheets name with an ampersand in them?

 

[llkhoutx]

Assuming that you mean a worksheet in and Excel file, use the worksheet number when importing.

 

[AndrewB]

Assuming that you mean a worksheet in and Excel file, use the worksheet number when importing.

Thanks for the reply. Yes, I meant a worksheet in an Excel file.

Can you reference a worksheet using a number? My code is set up as

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, Tablename, importstring, No, RangeString
where the variable RangeString has the spreadsheet name and the range of cells to be imported.

I presume you are suggesting that the spreadsheet name is replaced by a number? Is the number a relative reference (1, 2, 3 ...max number of worksheets) or something held internally.

Hope that makes sense.
Andrew

 

[llkhoutx]

Yes, just like you'd reference a relative sheet number in Excel, e.g. Sheet1, Sheet2, etc.