Excel VBA - Compressing a Workbook

[Guest]

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).

 

[Guest]

BlockNinja:

Use SpecialCells method

try,

Dim MyRange As Range, rng As Range
Set MyRange = Cells.SpecialCells(xlCellTypeFormulas)
For Each rng In MyRange
rng.Formula = rng.Value
Next rng

 

[Norman Jones]

Hi BlockNinja,

Try something like:

'==========>>
Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<==== CHANGE

For Each SH In WB.Worksheets
With SH.UsedRange
.Copy
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
'<<==========

 

[Norman Jones]

Hi BlockNinja,

Correcting for two missing lines and turning off/on Screen updating and
calculation, try:

'============>>
Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set WB = ActiveWorkbook '<<==== CHANGE

For Each SH In WB.Worksheets
With SH.UsedRange
.Copy
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
Next SH

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<============

 

[NickHK]

BlockNinja,
Why not PasteSpecial back into the original location on each sheet ?
OK, you lose your calculations, but assuming the report should not change in
the future, that would be a good thing.

Or print it to PDF and drop the temp calculation WB.

NickHK

 

[Dave Peterson]

It looks like you're just looping through the formula cells and converting them
to values.

Maybe you could just copy|paste special values for each sheet:

dim wks as workksheet
for each wks in MyWorkbook.worksheets
with wks.cells
.copy
.pastespecial paste:=xlpastevalues
end with
next wks

 

[Guest]

This one worked perfectly! Thank you chijanzen. This brought down the
compression time from about 2 hours to 1 hour. I can keep worrying about
trying to get it faster, but I think my strategy will be to write out an
uncompressed formula-based report every day for review and send out the
automated email, then when the compressed version gets done send out an
official email saying that the information in the compressed should be
published/archived.

Thank you so much!

 

[Norman Jones]

Hi BlockNinja,

If you turned off Screen updating and calculation, I would expect a
significant in speed.

I would also suggest that performing a single formulas to values conversion
( as suggested both by Dave and by me) would also represent an efficiency
improvement.