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