Exported from Access to Excel file is too large. How can I fix it?

[Joe Hanna]

Hello Fellas,

I created a procedure with the help of members of the Forum to automatically export reports from Access to Excel Files.
However, I bumped into the following obstacle. When Reports are exported, the file created in MS EXCEL 2003 is about 5 times larger than when I copy and paste the same reports manually from Access to excel.</SPAN>

Do you know what causes this? Is it because Access exports blank rows after the table? </SPAN>
Is there a way to fix it?

PS: When I convert the file form Excel 2003 to Excel 2007 the file size reverts to what it should be - five times smaller.
I have Access 2003 and Excel 2007, so currently I cannot use Access 2007 and the new Excel format.
</SPAN>

Thank you!</SPAN>

 

[Norie]

Are you getting the data and all the formatting from the report when you use code?

 

[Joe Hanna]

i am getting the data from the query, but the formatting seems pretty plane. I havent applied any formatting in access.
The font is "MS Sans Serif". There is not header formatting. Most of the fields and Text and Currency.

 

[Norie]

I thought it was reports you were exporting from Access to Excel?

 

[Joe Hanna]

I thought it was reports you were exporting from Access to Excel?

Yes, I am exporting from Access to Excel.

'xls' files are too lange.

 

[Norie]

Joe

What is it exactly you are exporting?

Reports? Tables? Queries?

If it's reports what size are the files if you export the underlying query or table rather than the actual report?

 

[Joe Hanna]

Joe

What is it exactly you are exporting?

Reports? Tables? Queries?

If it's reports what size are the files if you export the underlying query or table rather than the actual report?

I apologize,

I should have been more precise.</SPAN>

I do not export Access Reports. </SPAN>

I export Access Queries that query Tables. </SPAN>

For example, there are 5 queries that are exported to Excel. The file size could be 10MB for all 5 when exported to Excel 97-2003 (.xcl) file format.
When I copy the queries manually to a Excel 2007 (.xclx) file or if I convert the already exported Excel 97-2003 file to Excel 2007(.xclx) the size becomes 2MB as it should be. </SPAN>

 

[xenou]

The Excel 2007 file format uses compression. It will always be smaller than a similar file saved in the prior Excel 2003 format.
ξ

 

[Joe Hanna]

It seems like this is the case.

I have to find a way to manually resave the files in the new format.

Thanks for the replays and the support gutys!