0

The problem

Although one might assume this is a de rigeur problem with a mismanaged workbook, please hear me out. I'm working with a relatively modest (80-100kb) excel file. It has minimal formatting, very few formulas, and it's vba code doesn't do anything too fancy. I've been working with vba for a few months now and have had this problem exactly once before. As this tool will be used in producing reports for a while to come, and as I've limited time, I'd rather not rebuild the whole thing or turn in an unwieldy project.

Details

My code loops through several dozen 200+kb .xls files using Workbooks.Open method and grabs 1-2 values depending on what it's opened. It's been working through over a half dozen iterations with no change in size. In my latest version it's balloned from 100kb to 10mb. No tabs have been added, I've looked extensively for all sorts of formatting and other traditional pitfalls—it's not my first rodeo in that respect. I am new-ish to vba though, and so often step through code and have to pause or stop debugging before resuming to correct syntax. Sometimes this will happen before any Workbooks.Close method.

I think, inexplicably, during some combination of saves/crashes/debugging, excel has 'saved' some of the opened workbooks in this file's memory. It gets weirder: if I copy all of my worksheets, as well as my code, to a new workbook, the file size falls back down to 100kb or so. I've had this problem once before. Using a 20kb workbook I was trying to pull values from a 135mb workbook; after one save I noticed that my workbook was now 135mb and change, and I was only able to get it back down to 20kb by copying everything one sheet at a time to a new workbook. Does anyone know what's going on?

Attempting to google this produces dozens of pages on extraneous formatting, formulas, and VBA code unrelated to my issue.

I could post pseudo-code if it would be useful to see, but I'm pretty sure it wouldn't be too enlightening, and that my problem is some memory issue that occurs between .Open and .Close methods.

Edit: after looking through some of the workbooks that I'm opening, quite a few of them are 'around' 10mb. It seems suspicious to me that my tool would increase in size by about as much as one of the .xlsx files I'm opening.

halfer
  • 19,824
  • 17
  • 99
  • 186
Zachary Stoddard
  • 1
  • 1
  • It's probably just going to be speculation without seeing the code. Are you using temporary worksheets for anything? Embedded OLE objects? External data connections? – Comintern Aug 23 '18 at 21:27
  • Check for hidden worksheets? – AJD Aug 23 '18 at 21:32
  • 2
    If you take a look at the [XML file structure](https://stackoverflow.com/questions/46846678/how-to-open-and-view-the-xml-structure-of-an-excel-file) you should be able to see if the problem is in one particular sheet. One thing to try if the sheet "looks OK", is to select all rows below your data and `Clear All`, then do the same will all columns to the right of your data. – cybernetic.nomad Aug 23 '18 at 21:39
  • Nothing temporary, just good ole Workbooks.Open, Mywb.Sheets.Rng.Value = Oldwb.Sheets.Rng.Value, Oldwb.Close. Working to view the XML file structure now, that's probably on the right track. I've already gone through and cleared all cells besides the few I'm using, tried ctrl+end to see if I'm missing something, deleted and re-imported my output tab...nothing so far. – Zachary Stoddard Aug 23 '18 at 22:00
  • Alright, after finding out that if I inserted a blank sheet and deleted all others the filesize dropped to 50kb I tried deleting combinations of other sheets. If I left anything around with any formula that was there at the time of the explosion (or was heavily tied up in it), it was still the same filesize. It's also weird that I'd successfully run this macro without a problem many times without a blowup. It's still small enough at 10mb that no one here will have trouble opening it so I might just move on. Thanks for the advice all. – Zachary Stoddard Aug 23 '18 at 22:13

0 Answers0