Excel Filesize


I currently have a macro in a spreadsheet that copies and pastes one worksheet into a new workbook on its own and the saves this as a macro enabled document (large version attached - I have deleted all contents). This is usually around 390kb in size. Please note: I do need it to be xlsm.

Today the macro was tested out on another machine and the small version (attached) was produced instead. All the data pulled through correctly and this would certainly be sufficient for what is required .... this version was on around 30kb!!

I'm trying to find out what is different between the two spreadsheets, but at the moment I can't find out what is.  When I do find out, I would love to add into the original macro the info that is needed to save the file as the smaller version, rather than the larger version. At the moment the part of the macro i have that saves this is:

        Application.CutCopyMode = False
        strFileName = .Sheets(1).Range("FO9").Value

        .SaveAs wbA.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
        .Close SaveChanges:=False
    End With

    wbA.Sheets("NCS - Copy Values Skip Blanks").Visible = False

Open in new window

Any help to figure out why one filesize is so different would be really appreciated!
Dan FullerAsked:
Who is Participating?
Rob HensonFinance AnalystCommented:
Your large version has lots of duplicate formatting styles.
Styles list
Rob HensonFinance AnalystCommented:
What version of Excel are the two machines using? I wouldn't be surprised if they are using different versions?

I don't recall which version started it, but current versions of Excel now effectively compress the file when saved.
Dan FullerAuthor Commented:
Hi Rob,

Both versions have been saved using Excel 2010.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dan FullerAuthor Commented:
This looks good ..... so basically when I copy the worksheet into a new workbook, I currently paste values and formats (as below). I then delete all the name ranges.

What would be the best way you would suggest to delete the duplicate formatting styles?

Sub SaveAsA0()
Dim wbA As Workbook
Dim wbB As Workbook
Dim strFileName As String

    Application.ScreenUpdating = False

    Set wbA = ThisWorkbook

    wbA.Sheets("NCS - Copy Values Skip Blanks").Visible = True
    wbA.Sheets("NCS - Copy Values Skip Blanks").Copy

    Set wbB = ActiveWorkbook

    With wbB
        With .Sheets(1).UsedRange
            .PasteSpecial xlValues
            .PasteSpecial xlFormats
        End With
Dim nm As Name
Dim DeleteCount As Long

'Loop through each name and delete
  For Each nm In ActiveWorkbook.Names
    On Error GoTo Skip
    If SkipPrintAreas = True And Right(nm.Name, 10) = "Print_Area" Then GoTo Skip
    DeleteCount = DeleteCount + 1

'Reset Error Handler
  On Error GoTo 0
'Report Result
  If DeleteCount = 1 Then
    MsgBox "[1] name was removed from this workbook."
    MsgBox "[" & DeleteCount & "] names were removed from this workbook."
  End If

Open in new window

Rob HensonFinance AnalystCommented:
That, I don't know. I suspect it is something that has been inherited somewhere along the line on the machine that made the large version.

I have been able to reduce the size of the larger file but only by trial and error rather than a known method.

In Windows Explorer browse to the file and change the extension from .xlsm to .zip. Confirm the change and then double click on the file. The file will then open up to various folders. One of the folders is called xl, double click on that to open it. In that folder there is a file called styles.xml; this is the formatting styles data. You will notice that it is a rather large file.

If you then open a separate Windows Explorer session and do the same with the smaller version you will see the size difference.

In my system settings I changed the default application for xml files to Notepad.

I then opened the two style.xml files in Notepad and copied the text from the smaller version into the larger version, overwriting everything else in there. I saved the larger version .xml file  but it created a separate .xml file. Then in Windows Explorer I was able to copy the new .xml file and pasted into the folder to replace the old one.

Go back up the structure until you see the .zip file and change the extension back to .xlsm so that it then opens in Excel. Double click the file to open it and it will open in Excel, there will be an error message and Excel will do some repairs.

I stress at this point that I don't know what else this would effect.
Fabrice LambertFabrice LambertCommented:

If you're only interrested in cells values (no formula, neither formats, color, graphics or controls), instead of playing with the copy and paste method, you can load ("copy") your data in an array, and write it ("paste") in the new workbook.
Excel is pretty good at processing arrays, that might be faster than a traditional copy / paste:
Public Sub copyWorksheetData(ByRef src As Excel.Worksheet, ByRef trg As Excel.Worksheet)
On Error GoTo Error
    Dim data() As Variant
    Dim rng As Excel.Range
    Dim firstCell As Excel.Range
    Dim totalRows As Long
    Dim totalCols As Long
        '// "copy" data
    data = src.UsedRange.Value
        '// count rows and columns
    totalRows = UBound(data, 1) - LBound(data, 1)
    totalCols = UBound(data, 2) - LBound(data, 2)
        '// compute the destination range
    Set firstCell = src.UsedRange.Cells(1)
    Set rng = trg.Cells(firstCell.Row, firstCell.Column)
    Set firstCell = Nothing
    Set rng = trg.Range(rng, rng.Offset(totalRows, totalCols))
        '// "paste" data
    rng.Value = data
    Set rng = Nothing
Exit Sub
    If Not (firstCell Is Nothing) Then
        Set firstCell = Nothing
    End If
    If Not (rng Is Nothing) Then
        Set rng = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Sub

Open in new window

Dan FullerAuthor Commented:
Thanks all,

I managed to run a styles tool on the original document and reduced the 52000 styles that were showing to exactly the numbers shown in the smaller 2nd sheet.  All macros now work brilliantly, and have just re-run the worksheet into new workbook and come out at 26kb .. so perfect!

Thanks for your help
Dan FullerAuthor Commented:
Finding this out is what helped me  .... thanks Rob.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.