Excel Sheet Name does not allow to import data into Access using VBA - DAO

Question

My objective is to import some data into an Access DB from an Excel file. In order to do so, I am using DAO like this:

Debug.Print "Starting process to import the " & numberOfDaysToImport & " missing days"
Set sourceDB = OpenDatabase(c_sourceFile_austrianNetImports, False, True, "Excel 8.0;")
DoEvents
Set rs = sourceDB.OpenRecordset(c_sqlRetrieveAustriaNetImports)
Debug.Print "Recordset Field Count = " & rs.Fields.Count

c_source_austrianNetImports and c_sqlRetrieveAustriaNetImports are, respectively, string variables containing the path to the Excel source file and the SQL Select statement used to retrieve the data.

My problem is that, when the sheet's name of the Excel source file finishes with an underscore, the SQL Select query does not work and VBA crashes. For instance, if the sheet from which I want to retrieve the data is called EEX_EXC_Scheduled_PWR_AUT_H1_A_ and c_sqlRetrieveAustriaNetImports is SELECT * FROM [EEX_EXC_Scheduled_PWR_AUT_H1_A_$A1:H65536] then VBA crashes with the message

Runtime Error '3011' The Microsoft jet Database Engine could not find the object 'EEX_EXC_Scheduled_PWR_AUT_H1_A_$A1:H65536' Make sure the object exists and that you spelled its name and path name correctly.

If I modify the name of the sheet and the SQL Statement by deleting the final underscore, then it works. I have also tried to open the recordset by using

Set rs = sourceDB.OpenRecordset("Sheet$13")

but I still get the same error message.

I am not allowed to modify the name of the sheet. How could I get the data using the original name (the one that includes the _ at the end)?

Thank you.

I see a couple of threads on the net with similar problems, but no real solutions. One person thinks they fixed it by compacting and repairing their database. — Doug Glancy

Steve Gon Steve Gon · Accepted Answer · 2014-01-03T18:21:41

You should be able to just save the spreadsheet under a different name. I do this when importing CSV files, but it's actually easier with an Excel file:

bRename = false
while right(c_source_austrianNetImports) = "_" ' rename workbook
    c_source_austrianNetImports = left(c_source_austrianNetImports,len(c_source_austrianNetImports-1)
    bRename = True
wend 
if bRename then 
    ActiveWorkbook.SaveAs c_source_austrianNetImportst, xlWorkbookNormal 
endif

Excel Sheet Name does not allow to import data into Access using VBA - DAO

2 Answers