Huge file size doesnt make any sense

[Skagga5]

Hi All

I have got a VBA model that performs our monthly reporting, I then have a command button on an Userform which allows the user to email four of the sheets in the workbook, will refer to this as the extracted workbook.

The masterfile is 18MB, the extracted workbook that gets emailed is 3MB big, when I copy each individual sheet of the extracted workbook into a new workbook each of the four files are 3MB. If I delete my four sheets in the extracted workbook and I am effectively left with an empty workbook the file size is still 2.7Mb. Somewhere something is hiding that is causing this workbook size to be overly excessive.

Any help on how I can reduce the file size would be greatly appreciated.

regards

 

[RoryA]

You can't delete all the sheets in a workbook, so what is left in that extracted workbook, and how exactly was it created in the first place?

 

[Skagga5]

I have a master workbook that uses code to extract four sheets and attach them to an email, there are no formulas in the sheets of the extracted workbook and to be honest is very small insignificant worksheets in the extracted workbook, the size of this workbook is 3MB, I tested the size of each sheet and when I extract them to a new workbook they all come in at 3MB individually which is also very strange.
On the extracted workbook, I inserted a blank sheet, sheet 1 and deleted the four sheets with my data, so I only have 1 empty sheet in the extracted workbook, when I save this this the file size is 2.7MB for practically empty workbook.

 

[RoryA]

How did you extract them to a new workbook - what is the code you used?

 

[Skagga5]

Set Sourcewb = ActiveWorkbook
Sheets(Array("Office_Summary", "Detail Report", "TransactionalSummary", "Transactional_Data")).Copy
Set Destwb = ActiveWorkbook

 

[RoryA]

That looks OK. Can you post the "empty" workbook somewhere (eg OneDrive, Dropbox) so I can have a look at it?

 

[Skagga5]

Here you go. I have added to files to the folder, both of them are effectively empty workbooks, one in XLSX and the other in XLSB format. I have changed my code to save the extracted file in binary format, this has reduced the size to 1.4MB but this is till rather large for what is effectively an empty workbook.

https://www.dropbox.com/sh/l6qssk1t5fwwr0q/AACLOQSQeHdKEvP4i1zibUYxa?dl=0<o></o>

 

[RoryA]

I can't download either of those, I'm afraid. Did you set permissions correctly for sharing?

 

[MARK858]

Hi Rory, both downloaded for me, I have put them on Box if it helps...

https://app.box.com/s/s1zkiomufohrw2304nmuugiv28jc8yal
https://app.box.com/s/aak0p5gyxnruvvn2trwyw3kwifqc0mql

 

[thisoldman]

I was able to download the file.

The problem is in the defined names. When I saved the xlsb file as an xlsx file, the size was 2.77MB. All of the defined names refer to files on other drives including network drives.

Deleting the named whole-column range "Currency" reduced the size to 1.51MB.

Deleting the named whole-column range "sdf" reduced the size to 15.8KB.