Geek Answers Handbook

Excel worksheet name does not allow data to be imported into Access using VBA - DAO

My goal is to import some data into an access database from an Excel file. For this, I use DAO as follows:

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 string variables, respectively, 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 name of the source Excel file ends with an underscore, the SQL Select query does not work and VBA crashes. For example, if the sheet from which I want to get data is called EEX_EXC_Scheduled_PWR_AUT_H1_A_ , and c_sqlRetrieveAustriaNetImports is c_sqlRetrieveAustriaNetImports SELECT * FROM [EEX_EXC_Scheduled_PWR_AUT_H1_A_$A1:H65536] , then VBA crashes with the message

  • Runtime error '3011' Microsoft Database Database Engine could not find the object 'EEX_EXC_Scheduled_PWR_AUT_H1_A_ $ A1: H65536' Make sure the object exists and you entered its name and path correctly.

If I change the sheet name and SQL expression by deleting the final underscore, it will work. I also tried to open the recordset using

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

but I still get the same error message.

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

Thanks.

+4
source share
2 answers

You should be able to just save the table under a different name. I do this when importing CSV files, but it is really 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
+1
source

If you can import a named range instead of a worksheet, this works. I have a table that I import daily, where the worksheet name is the date (changes daily), but the table is a named range.

If this is not a named range, you can create code to CREATE the named range, save, and then import the range that you created.

0
source

More articles:


All Articles