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.

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

Cross-posted at: http://www.mrexcel.com/forum/excel-q...alid-name.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for the notice about the cross-posting etiquette and for adding the link for me

A discussion was held in the other aforementioned location, and the resultant information (provided by RoryA on Jun 20th, 2016) is as follows:

SELECT * INTO and INSERT INTO will only work when the destination workbook is closed whereas I am attempting to import data into an open workbook.

I am already importing the data using ADO CopyFromRecordSet command that works with an open destination workbook, 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.

Since there are known memory leak issues with using ADO on open workbooks, I will have to consider what to do: change the approach of how the code and import should operate to maybe support using a closed intermediary workbook, open each source file and then obtain the data without using ADO, or live with the memory issue until everything gets ported over to something more capable of handling the massive amount of data.