John W Colby
jwcolby at gmail.com
Thu Mar 20 10:04:00 CDT 2014
I have not followed this thread but I used to work on a spreadsheet I wrote that had the modules for Excel itself inside of the spreadsheet. I would then open the workbook and call code in the spreadsheet. I did this because the sheet was formatted and stuff and then the code was local access / cell references instead of building all that code out in Access. It worked just fine. John W. Colby Reality is what refuses to go away when you do not believe in it On 3/20/2014 10:58 AM, Bill Benson wrote: > My first response may have missed the fact (?) you were automating Excel > from Access? But then again you write that the code modules are in Excel > which seems like you are starting from the Excel workbook. > > But the you would not be writing about shutting down Excel from Access. > > But then again you write it is an Access FE to a SQL Server BE, so that > indicates you are working from Access and automating Excel after all. > > ??? > > I am really confused Darryl... > > Can you clear up your runtime environment and hand offs for my slow brain? > On Mar 19, 2014 8:14 PM, "Darryl Collins" <darryl at whittleconsulting.com.au> > wrote: > >> This idea has merit, although I was going to just the INSERT INTO to >> create all of the exported xtabs as individual files rather than a text >> file. Once they are all created I can then get the XL template to sweep >> them up from there and do its formatting and cleaning up etc. >> >> This approach is still an option, probably the one I will end up taking if >> I don't get a better solution. Annoying as 98% of the time this thing is >> fast, stable and agile. >> >> It is only a couple of the most extreme reporting scenarios I am hitting >> this issue. Meh! >> >> Cheers >> Darryl. >> >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com [mailto: >> accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan >> Sent: Thursday, 20 March 2014 10:55 AM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] CopyFromRS memory Leak in Excel VBA >> >> How important is formatting? >> >> An alternative could be to dump the data to a tab delimited file and then >> use Office automation to open/save the file in Excel. >> >> On 19 Mar 2014 at 23:18, Darryl Collins wrote: >> >>> Hi Guys and Girls. >>> >>> Never had a problem with CopyFromRS before, but now I seem to bumped >>> into an issue with Excel bleeding memory until it jams up whole >>> system. It only happens on the (few and rare) larger reports. By >>> larger I mean I will be performing CopyFromRS several hundred times in >>> a row, and some of the RS are large (100K plus rows x 30 columns - >>> these are x-tabs imported from Access - not all of them are that big >>> of course virtually none of them will be close to fully populated). >>> The resulting XL file isn't that big though (maybe 50 MB at the end). >>> >>> MS Access is just a FE to a SQL Server Database. Nearly all the data >>> is in SQL Server and it is really only the x-tabs reports in the >>> Access FE that we refer too in this instance. >>> >>> Watching the performance manager whilst this is running you can see >>> the memory constantly creeping up until it is full (where XL dies with >>> an "out of Memory" Error. This is a known issue too according to Prof >>> Google and Microsoft, but it looks like MS have been too busy making >>> things look pretty rather than fixing bugs for the last decade or so. >>> >>> Things which were already in place: >>> >>> In the existing solution, all RS and connections are opened, close and >>> set to nothing correctly - although according the MS that doesn't help >>> (as it is part of the problem, XL doesn't release the memory correctly >>> once the object is closed and removed). >>> >>> The only solution MS offer is to close the XL application and the >>> reopen it. This will require a fair bit of dicking around on my >>> behalf - mostly moving all of the existing XL code modules into Access >>> and retweaking them. Then I can close the XL app, reopen it and pick >>> up from where I left off. Whilst this is doable, it is a lot of time >>> and effort to fix an issue that only deals with 2% or so of all the >>> reporting needs we have. Hmmmmm.... >>> >>> >>> Changes I have put in which have helped (so far): >>> >>> Replaced all the CopyFromRS code with an INSERT INTO function instead, >>> which pushes the x-tab data into a new Excel Workbook, from where I >>> copy it from there to the working XL workbook and then use KILL to >>> remove the temp XL file created by the INSERT INTO. That slowed down >>> the memory creep, but it is still present. >>> >>> I added in a few regular saves in the process , particularly after >>> adding in new worksheets. Whilst this slows things down it did slow >>> down the memory creep, but still no cigar. >>> >>> So... >>> >>> Open to ideas here. Anyone got anything they can suggest or I might >>> have missed I am all eyes and ears. >>> >>> Cheers >>> Darryl >>> >>> >>> >>> >>> >>> Darryl Collins >>> Whittle Consulting Pty Ltd >>> Suite 8, 660 Canterbury Rd >>> Surrey Hills, VIC, 3127 >>> >>> p: +61 3 9898 3242 >>> m: +61 418 381 548 >>> f: +61 3 9898 1855 >>> e: >>> darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au >>>> w: >>> www.whittleconsulting.com.au<http://www.whittleconsulting.com.au/> >>> >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com