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

Joe Hanna

Board Regular
Joined
Apr 3, 2013
Messages
69
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>
 
Last edited:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Are you getting the data and all the formatting from the report when you use code?
 

Joe Hanna

Board Regular
Joined
Apr 3, 2013
Messages
69
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
I thought it was reports you were exporting from Access to Excel?
 

Joe Hanna

Board Regular
Joined
Apr 3, 2013
Messages
69

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 3, 2013
Messages
69

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Apr 3, 2013
Messages
69
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,774
Members
414,018
Latest member
quang118

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top