Macro saves document with a larger size than the document actually is.

[enz_dducharme]

Hello,

I have a document that imports data from several files then exports it in a xlsx file without macros or formulas. I've been working on it for the past few years and generally didn't have any issues I couldn't figure out or solve myself. Naturally, this is a confidential work document so I am not comfortable sharing it. I would however like to know if anyone would be familiar with the issue I'm having:

At some point, I re-wrote a lot of code (for optimization and because we were experiencing issues occasionally) and although everything is much more efficient and fast now, my exported documents are much larger than they were before.

Now, before I say anything else, this is not an issue with the document being larger than I would like it to be. This is the reason I am asking this myself because people not understanding why their files are so large is all I could find. The difference with my issue is that my files usually are around 60 KB when exported but are now around 350 kb when exported... But if I close the document, open it again and just hit save, it goes back to 60 KB.

Now, to give a little context, 350 KB is an arbitrary number that depends on the amount of data that was imported. The document imports a lot of data, displays it in charts then on export keeps only calculation of the data (min, max, average etc.) converted to values and charts are converted to bitmaps. This results in files that would originally be over 1 MB drop to a consistent 50-60 KB.

I've tried going through my code or the changes I've made between when it saved with the correct size and when it saved it that "fake" size without success. My only solution that I absolutely hate was to include an extra bit of code that closes the document, opens it then saves it again... It doesn't take long at all to do so since the document is small but I feel horrible having something so stupid happening in my code. I would much rather solve the problem at its core.

Now my first assumption was that the charts somehow take more space than they should (which doesn't really make sense if the size lowers when I just save the document a second time). Here's the subroutine I use to convert all my charts to pictures:

Private Sub ChartsToBitmap()
Dim cht As ChartObject
    For Each cht In Sheets("Report").ChartObjects
        cht.CopyPicture xlScreen, xlBitmap
        cht.TopLeftCell.Select
        cht.Delete
        ActiveWorkbook.Sheets("Report").Paste
    Next cht
End Sub

(As a side note, this code doesn't work in my macro if I use .paste instead of ActiveWorkbook.Sheets("Report").Paste and I'm not sure why.) I've tried the method posted there too actually: https://www.mrexcel.com/forum/excel-questions/403172-convert-all-charts-worksheet-pictures.html and had the same result. Both will leave my document larger than it should be. I've tried have the macro only delete the charts and same thing.

I've changed my code from making a copy of the original workbook then deleting the raw data (only keeping the charts and values) to only copying what I want... And still the file is bloated.

I'm really not sure of what could cause this as it wasn't the case before. Has anyone ever had a similar issue?

 

[JLGWhiz]

Depending on your software and type of file, the underlying application which creates the file might store additional undisplayed data as part of the file. As an example, borders require underlying instructions to tell the program where to display them, how the lines are oriented. where their relative position on the screen is, etc. So some graphics parameters could be part of the file, but as I said, it depends on your what you are working with. I am not enough of a techie to give a good detailed explanation, but other than showing a bloated file size, it usually does not interfere with performance.

 

[enz_dducharme]

I haven't noticed any issue with performance but my problem is that I send a couple of those via e-mail everyday. So at 60 kb it's never an issue but at 300+ KB I can get 20-30 MB attachments sometimes.

I understand your explanation but what I don't understand is how the file size would change when I open and save the file without doing anything else. Why is it that saving through my macro the size is bloated but otherwise not? Very strange. Especially considering I use a very simple .SaveAs and it's never been an issue before.

 

[JLGWhiz]

Again, it is all in how the underlying application software processes whatever your file is made up of. And even that changes over time as application software is updated. The technology is in a constant state of change and what might be filtered during a saveas action today, could be different after a future update. Unless it is causing a real problem, I wouldn't worry too much about it.

 

[enz_dducharme]

Hmm, well I use Excel 2016 and it hasn't been updated in a long time as far as I'm aware. As I said the bloat is the result of code change but I can't identify what. Using my old code doesn't result in bloated files. It is most definitely a problem as things need to go as fast as possible here and I want to avoid redundant code (open, save, close extra). There is clearly something wrong and I would be really happy if someone could help me identify what it is. But thank you for trying to help

 

[enz_dducharme]

Just managed to figure out the problem after all (might sound like it was fast but I've been trying to figure this out for a month).

Just for reference purposes, in case anyone every has this problem, this occurred simply because of the order in which my subroutines were executed. I have this following code that removes all links from the document:

Sub BreakLinks()
    Dim Links As Variant, i As Integer
    Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = 1 To UBound(Links)
    ActiveWorkbook.BreakLink Name:=Links(i), Type:=xlLinkTypeExcelLinks
    Next i
End Sub

and this one which converts all charts to pictures:

Private Sub ChartsToBitmap()
Dim cht As ChartObject
    For Each cht In Sheets("Report").ChartObjects
        cht.CopyPicture xlScreen, xlBitmap
        cht.TopLeftCell.Select
        cht.Delete
        ActiveWorkbook.Sheets("Report").Paste
    Next cht
End Sub

For some reason, if I convert charts then break links, it seems like charts take more space then they should. But if I break all links first then convert charts, the file size is correct and everything works fine. I had switched their orders because my macro would export blank charts and that's the only way I had found to fix it but now everything works fine again.

I can't claim I understand why it happened and why it's resolved very well, but that was my solution.

 

[JLGWhiz]

Just managed to figure out the problem after all (might sound like it was fast but I've been trying to figure this out for a month).

Just for reference purposes, in case anyone every has this problem, this occurred simply because of the order in which my subroutines were executed. I have this following code that removes all links from the document:

Sub BreakLinks()
    Dim Links As Variant, i As Integer
    Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = 1 To UBound(Links)
    ActiveWorkbook.BreakLink Name:=Links(i), Type:=xlLinkTypeExcelLinks
    Next i
End Sub

and this one which converts all charts to pictures:

Private Sub ChartsToBitmap()
Dim cht As ChartObject
    For Each cht In Sheets("Report").ChartObjects
        cht.CopyPicture xlScreen, xlBitmap
        cht.TopLeftCell.Select
        cht.Delete
        ActiveWorkbook.Sheets("Report").Paste
    Next cht
End Sub

For some reason, if I convert charts then break links, it seems like charts take more space then they should. But if I break all links first then convert charts, the file size is correct and everything works fine. I had switched their orders because my macro would export blank charts and that's the only way I had found to fix it but now everything works fine again.

I can't claim I understand why it happened and why it's resolved very well, but that was my solution.

Because the underlying application software associates the linked graphics to your file until the links are removed. As I said preiviously, it is the underlying software that determines what constitutes a file, not what you can see. I don't understand it all either and that is why I am not a software engineer.

 

[enz_dducharme]

Yeah, that's definitely what it sounds like. Still sounds like I stumbled upon a very rare scenario though, since doing this manually (replacing charts with pictures and saving the document) won't result in a bigger file. But I do understand how somehow the document wouldn't break the link with the charts before saving. Very odd.