Excel Filesize

Hi,

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!
Version--Large-22-11-2017--Data-Cap.xlsm
Version--Small-22-11-2017--Data-Cap.xlsm
Dan FullerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
Dan FullerAuthor Commented:
Hi Rob,

Both versions have been saved using Excel 2010.
0
Rob HensonFinance AnalystCommented:
Your large version has lots of duplicate formatting styles.
Styles list
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
            .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

Open in new window

0
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.
0
Fabrice LambertFabrice LambertCommented:
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

Open in new window

0
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
0
Dan FullerAuthor Commented:
Finding this out is what helped me  .... thanks Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.