ADODB import of Excel data results in "Run-time error -2147467259: <sheet> is not a valid name."

[misaak]

Hello,
I apologize ahead of time if my description is too verbose

I have an Excel-based tool that has to regularly import data from 50+ user-selected, large CSV, XLS, and XLSX files into a single Excel workbook, and I am encountering a difficulty that would appear to be caused by Excel not releasing memory because performing multiple imports that replace the existing data will still eventually result in something as simple as launching a UserForm to fail with a message stating there is insufficient storage space.

I already have all of the various objects set to Nothing at the end of the various functions, and there are only three Boolean global variables in the code so I went searching online for ideas. One of the possible ideas was that the import of the XLS and XLSX files could be switched from using CopyFromRecordSet to using a SQL INSERT INTO command.

Unfortunately, my experience with SQL is almost nil, and my attempts to use the INSERT INTO encounters problems with unknown 'F#' field names (I think maybe caused by the imported worksheets having blanks for most of the first row columns) so I am trying to at least get SELECT * INTO to work to see if the memory issue might at least improve. Unfortunately, my attempts to implement the SELECT * INTO command result in the following error:

Run-time error '-2147467259 (80004005)':
'Temp$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

NOTE: 'Temp' is just a sample name that I tried using, of which I have tried multiple. The exact message naturally reflects whatever worksheet name I have entered into the SQL string. When trying the SELECT * INTO, I made sure that there was not already a worksheet present with the name.

The code is run from the destination workbook into which the desired data needs to be copied from the closed source file(s); located in various directories on the same computer so no networking involved.

I am not allowed to post all of my code, but the below pieces comprise the core initial set related to the import attempt. The error occurs when attempting to execute the "rsCon.Execute szSQL" command.

Dim rsCon As Object  'the ADODB connection object
Dim szConnect As String  'the ADODB connection string
Dim szSQL As String  'the SQL query string
Dim FileArray() As String  'the full path and file name for each of the files to be imported
Dim FileSheet() As String  'contains the list of worksheet names to be imported already with a $ at the end
Dim i As Long  'a general loop counter
Dim j As Long  'a general loop counter

Set rsCon = CreateObject("ADODB.Connection")
szConnect = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & FileArray(i) & ";" & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""
rsCon.Open szConnect
szSQL = "SELECT * INTO [Temp$] IN '' [Excel 8.0;Database=" & ThisWorkbook.FullName & "] FROM [" & FileSheet(j) & "];"
rsCon.Execute szSQL

NOTES:
Example FileArray(i) could be "C:\Users\misaak\Desktop\TestData.xls"
Example FileSheet(j) could be "JON$"
Example ThisWorkbook.FullName could be "C:\Users\misaak\Desktop\TestCode.xlsm"

If a solution is identified for the JET engine import of the XLS files, then I hope to also apply it to the ACE engine import of the XLSX files (CSV files are imported using a FileSystemObject approach that splits the lines into the columns of a large array).

The version of Excel is the 32-bit 2013 version running in a 64-bit Windows 7. (The tool has to function using 32-bit since that is what the users have installed, and the tool has to remain solely in Excel.)

The current References that I have selected are the following, although I might not need all of them:
- Visual Basic for Applications
- Microsoft Excel 15.0 Object Library
- Microsoft ActiveX Data Objects 2.8 Library
- Microsoft Forms 2.0 Object Library
- Microsoft Windows Common Controls 6.0 (SP6)
- Microsoft Scripting Runtime
- OLE Automation

I have exhausted all of the ideas that I can think to try so I would greatly appreciate any ideas that someone might have. If you need any additional information, then please let me know. Thank you very much for any help that someone might provide.

Kind Regards,
Markus

 

[Macropod]

Cross-posted at: ADODB import of Excel data results in "Run-time error -2147467259: <sheet> is not a valid name."
Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[RoryA]

Welcome to the forum.

You need to remove the $ sign from the sheet name.

 

[misaak]

Good day,

Thank you for the suggestion. When I don't have the '$' in the sheet name and just use the below string, then I receive the following error that the object could not be found:
szSQL = "SELECT * INTO [Temp] IN '' [Excel 8.0;Database=" & ThisWorkbook.FullName & "] FROM [" & FileSheet(j) & "];"

"Run-time error '-2147467259 (80004005)':
The Microsoft Jet database engine could not find the object 'Temp'. Make sure the object exists and that you spell its name and the path correctly."

As I understand the use of the SELECT * INTO from my reading, the worksheet name can't be the same as an existing sheet name which there is no "Temp" sheet so the name part has to be correct. If I were to change the name to a sheet that already exists, e.g. "Dates", and run the following string, then I receive an error that the sheet already exists.
szSQL = "SELECT * INTO [Dates$] IN '' [Excel 8.0;Database=" & ThisWorkbook.FullName & "] FROM [" & FileSheet(j) & "];"

Run-time error '-2147217900 (80040e14)':
Table 'Dates$' already exists.

Since changing the sheet name to one that already exists results in an error to that effect, then that should mean that the path portion also has to be correct. Since the name and path portions both appear to be correct, I am at a loss as to what to try next. Thank you again.

Kind Regards,
Markus

 

[RoryA]

Ah, I didn't look at your code closely enough. You can't do a SELECT INTO query when the destination workbook is open (it will work if it's closed and you omit the $ sign as I mentioned). However, you don't need to anyway - just add the sheet using Sheets.Add, name it whatever you like, then copy the recordset into it.

 

[misaak]

Ah, I did not realize that about the SELECT * INTO command. Is the same true for the INSERT INTO command?

I am currently importing the data using CopyFromRecordSet command, but I keep randomly encountering problems with Excel appearing to either not release all of the memory after the code has finished or there is a memory leak somewhere because repeated executions of the code will cause Excel to eventually stop being able to even launch a simple UserForm stating that there insufficient storage to do so. One of the possible ideas that I found online was to try the INSERT INTO command, but that doesn't work for the reason stated in the original post so I thought I would at least try out the SELECT * INTO command. However, if the SELECT * INTO command won't work when the destination is open, then I won't be able to try it. The same is true for the INSERT INTO command.

Thank you again,
Markus

 

[RoryA]

There are known memory leak issues with using ADO on open workbooks.

INSERT INTO will work with an open workbook but you'll have the same issues, as far as I know.

 

[misaak]

Is there a better way to import data into Excel from closed XLS and XLSX files that doesn't have the potential for memory leaks and is still fast?

I need to be able to import data regularly from 50+ sometimes 40-50+ MB files whose names and locations and will change regularly so the user has to manually select the files, which I currently have the code doing. The worksheets inside the files also change names and quantities so I also have to obtain the worksheet names inside each file before the import which I also have the code doing from the schema.

I realize that there are limitations to Excel and especially to 32-bit Excel, and I am trying to get as much reliable functionality as possible given the requirements and constraints. We can transition to something more powerful and robust later, but I need to at least get something working semi-reliably now.

Thank you,

Markus

 

[RoryA]

Importing to a closed workbook would be the simplest change.

However, if you have the possibility of mixed data types in your columns, I wouldn't recommend ADO. I'd stick to actually opening the workbooks.

 

[misaak]

Thank you very much for the help. I hope to avoid having to launch each of the source files because doing so would significantly increase the amount of time the import/cleanup procedure takes, let alone all the analysis and other work that has to be done; just the import/cleanup already takes anywhere from 6 to 23 minutes depending on the amount of data and number of files involved (I am really pushing 32-bit Excel to the limit).

I will have to consider what to do: either change the approach of how the code and import should operate to maybe support using a closed intermediary workbook or live with the memory issue until everything gets ported over to something more capable of handling the massive amount of data (I have already put in place some error handling and other things to try to handle any problems as gracefully as possible).

Thank you again for the suggestions.

Kind Regards,

Markus