DoCmd.TransferSpreadsheet

[aziz rasul]

I have a spreadsheet, which I have no control over, that typically has worksheet names like

Ireland
UK
etc.

The UK example has spaces before and after.

I have the worksheet names in a table. I'm trying to create a DoCmd.TransferSpreadsheet statement which will import all the worksheets in to their respectives tables. Here's what I have so far.

Do While Not rstCountry.EOF
CurrentDb.Execute "DELETE * FROM tblConsolidatedInvoices" & rstCountry!country & "Temp;"
CurrentDb.Execute "DELETE * FROM tblConsolidatedInvoices" & rstCountry!country & ";"
' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblConsolidatedInvoices" & rstCountry!country & "Temp", txtFilepath, False, "' UK !'"
' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblConsolidatedInvoices" & rstCountry!country & "Temp", txtFilepath, False, "Ireland!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblConsolidatedInvoices" & rstCountry!country & "Temp", txtFilepath, False, rstCountry!countryname & "!"
rstCountry.MoveNext
Loop

I can get it to work for Ireland but not for " UK ". I can get them both to work individually (see the commented code) but I need them to work thru the recordset.

 

[The_Doc_Man]

Access won't like the spaces too much unless they are quoted or bracketed.

I would take the approach of trimming the field name before using it in another statement. Look up RTrim and LTrim and Trim in the Help files, then use an intermediate string for the name of the table.

 

[aziz rasul]

But I have no control over the worksheet names.

 

[RCurtin]

You don't have to refer to the worksheets by name:

Dim appExcel As Excel.Application
Dim wk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
    
Set appExcel = New Excel.Application
    theFile= "C:folder\thefile.xls"
    With appExcel
        Set wk = .Workbooks.Open(theFile)
        Set xlSheet = wk.Worksheets(1) 'this is the first worksheet - this may work if the order of the sheets is consistent
End With

 

[aziz rasul]

Can you use DoCmd.TransferSpreadsheet and import each worksheet while the spreadsheet is open? Also I would have to clear the Excel instance AFTER importing each worksheet.