Excel File Bloat when copying via VBA

[idbr]

Hi,

I have a simple data copy operation (running via Access VBA):

appXL.ActiveWorkbook.Sheets(strSourceTab).UsedRange.Select
appXL.Selection.Copy
appXL.Workbooks(strWorkingFileName).Activate
appXL.Workbooks(strWorkingFileName).Worksheets(1).Activate
appXL.Workbooks(strWorkingFileName).Worksheets(1).Range("A1").Select
appXL.Selection.PasteSpecial Paste:=xlPasteValues

The used range in the source book is correctly selecting only the data. No formatting is applied to the data in the working book.

The resulting file when saved is more than twice as large as the source data, despite having exactly the same data! (5266KB vs 12356KB)

Any ideas what might be causing this?

Thanks, Iain

 

[SkipVought]

Hi,

Try this...

appXL.ActiveWorkbook.Sheets(strSourceTab).UsedRange.Copy
appXL.Workbooks(strWorkingFileName).Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

It can be due to coding workbook data. I have an access application that exports queries to excel workbook (DoCmd.TransferSpreadsheet). The output workbook reduces its size twice when it is opened and saved again.

combo