I am having an issue with Excel regarding the filesize of a workbook that has
formulas in it.

Currently, I run an automated daily report, that is started up by Access/VBA
and spits out an Excel Spreadsheet based on some comparison numbers. This
process was originally invented by another coder, and of course, the
calculations are wrong. So I have come back through, and now I am letting
Excel do all of the percentage calculations etc etc and just throwing out my
sum numbers to this Spreadsheet to be calculated by Excel itself for accuracy
(and a cleaner process, I shortened about 3000 lines of code down to about
200-300). I have everything currently working right now, but my new process
makes the Excel file jump from ~5MB to ~27MB because I'm plugging in formulas
instead of values...

So, considering that management will obviously not like this change in
filesize, and will want to know how I'm justifying the waste of server
space... and that the suggestion of automatic zipping of the files into
date/timestamped zips in an archive directory will create too much of a
"hassle" for the people looking at this report, I need a good solution to
getting this Excel report back to all values after all of the calculations
have completed.

Right now I have some code that does it, I can't show you the full code as
the report part alone takes 30 minutes to run (did I mention I'm on a Xeon
too?...) and I'm rerunning it right now, but here is some pseudocode
essentially to see my formulas-values conversion.

for each sheet in MyWorkbook
yMax = sheet.usedRange.rows.Count
xMax = sheet.usedRange.columns.Count
for y = 1 to yMax
for x = 1 to xMax
if sheet.cells(y,x).HasFormula = true then
tempvalue = sheet.cells(y,x).Value
sheet.cells(y,x).Formula = ""
sheet.cells(y,x).Value = tempvalue
end if
next
next
next

This is all fine and good, and works... but it takes at least 2 hours to
run! (maybe even more, I never successfully finished a full compression run
with ALL of the data of the report) These guys want this report daily around
noon-ish as well, when my files usually don't come in to do this report until
about 11-ish. So you can see where this is becoming a major problem.

If anyone has a faster method of doing what I'm attempting to do here,
please let me know.

Also, I tried just messing with Excel with the end-result report and seeing
if I could do Paste Special into a temp sheet with just the values, it gave
me some error about that I couldn't paste if the merged cells were not all
the same size or something to that effect. There are merged cells in the
header fields on my sheet in places (which are copied throughout the report
in various rows that I don't know until runtime).