Active X Excel Memory Usage Too Much? 1

[SalemGrafix]

Several times in the past, I've used the Active X control from Excel in Visual Basic to populate a spreadsheet and export the data (usually pulled from a database, to store the information for later usage or for certain report formatting).

Currently I'm trying to use it, and if the Query I'm pulling from is small (1k records or less), it functions just fine (although a little sluggish towards the end), but when I go over 3k records, it not only comes to a crawl, but eventually runs itself out of resources.

I was wondering if anyone has ran into this issue in the past, and if a solution was found, or a better way to implement this (such as exporting to a flat file instead, which is the last resort at this time).

For a more precise explaination, I'm running a query against our database, and using those fields (some with calculations) to populate an Excel spreadsheet (via Active X) and the program then exports the spreadsheet automatically to be saved for later usage (a snapshot of the database if you will).

Thanks for any input on this one.

 

[ca8msm]

Have you tried exporting it to a csv file or is this not really an option? It may be quicker as you can just export the data the same way as a flat file but seperate the values with a comma. This may increase the speed...

 

[SalemGrafix]

Yes, that was also an option, but was hoping to find a solution to the resource/memory usage of the Active X control. It may very well be that I have to go this route.

The other option I had thought about, was saving as a CSV file (or any delimiter) and then using the Active X to "LoadText" and save the file at completion of the import.

This would be a longer way around the problem, but here's why I would prefer it to be completely automated:

I won't be the one running the final product, and although I can handle exporting to a CSV, and then importing it and saving the new file format (ie. Excel), others who will be running this report usually don't grasp these concepts, they "need" automation.

If there are any other responses, I will look at them tomorrow, but I will definately keep those as backup options.

Thanks for the response.

 

[SalemGrafix]

I did turn off some of the options in the spreadsheet this morning, such as screen updates, undo feature, and also turned the spreadsheet invisible (as it's not really to be displayed on screen, just used for exporting) and it did speed things up, but not significant enough to make this a viable option yet.

Anyone else that might have any idea or improvements that can be made with using the Active X Control?

Thanks.

 

[SalemGrafix]

Okay, last update on this issue:

I exported to a delimited file (using chr$(164) so that it's nothing that will ever appear in our database), and then used "spreadsheet1.LoadText" to import the file, and this was extremely fast.

The report would take about an hour and a half to run with populating the Excel spreadsheet then exporting the worksheet. The new method (with same data criteria), exporting to a delimited file, importing that into the spreadsheet, and then finally exporting (or saving) the data to a completed Excel spreadsheet file took less than a minute to execute.

So, if anyone else happens to run across this, don't hesitate to export to a flat file first, then import that into excel (LoadText allows you to specify the delimiter character as well) and save the complete spreadsheet.