Excel Filesize

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.

Hi Rob,

Both versions have been saved using Excel 2010.

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
            .Copy
            .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
    
    nm.Delete
    DeleteCount = DeleteCount + 1

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

Select allOpen in new window

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.

hi,

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
Error:
    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

Select allOpen in new window

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

Finding this out is what helped me  .... thanks Rob.