Improve company productivity with a Business Account.Sign Up


How to compress the size of an Excel file that is created from an Access 2003 application?

Posted on 2013-11-07
Medium Priority
Last Modified: 2013-11-15
Is there a way to modify the following method to mimize the size of the 2003 Excel file that is created? When I run the Access 2003 mdb application, the size of the resulting Excel file has been over 60MB.
Question by:zimmer9
LVL 12

Expert Comment

ID: 39632714
Hi zimmer,

Do you need to do this automatically from Access?

If not...

Are you able to open it in a later version of Excel and save it as .xlsx?  That is a compressed XML format which will hopefully be smaller.

Or if you have the Office compatibility pack installed, you could do the same from Excel 2003 (i.e. save to .xlsx).

Alternatively, you could just ZIP it (e.g. WinZip or any of the free alternatives).
LVL 22

Expert Comment

ID: 39632739
Depending on why the file is big, it may be just a matter of running a macro to remove blank rows or columns.  Does the Excel file actually have tons of Access data in it?
LVL 10

Assisted Solution

mark_harris231 earned 500 total points
ID: 39632881
Another option to try:

- Navigate to the first empty row at the bottom of any valid data (Ctrl + Down Arrow usually works, but may get a false positive if there are cells that are intentionally blank)
- Press Ctrl+Shift+End to select all rows/columns below this last row
- On the Home tab, select the Clear > Clear All utility (in the Editing icon group)
- Next, return to Cell A1, then navigate to the first empty column to the right of any valid data (Ctrl + Right Arrow)
- Again press Ctrl+Shift+End to select all rows/column beyond this last column
- Select the Clear > Clear All function again
- Save and exit the document, then re-open.

This will clear any formatting or undesired content that might be "lurking" in one or more outlier cells and could be causing Excel to think there is more data than you're actually interested in.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 39633173
Here is my Acces VBA code:

Private Sub ExportToExcels(filename As String)
Dim str_sql As String
Dim cn As ADODB.Connection
Dim xl As Excel.Application
Dim xlWB As Excel.Workbook
Dim sht As Excel.Worksheet, rng As Excel.Range
Dim db As DAO.Database, rs As ADODB.Recordset
Dim recordtotal As Long
Dim SheetNum As Long
Dim dest As Range
Dim Counter As Long
Dim Source As Workbook
Dim col As Long
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set db = CurrentDb

recordtotal = DCount("ProdID", "tblRemedInternal")
Set xl = CreateObject("Excel.Application")

Set xlWB = xl.Workbooks.Add
xlWB.SaveAs filename
Set xlWB = xl.Workbooks.Open(filename)  

xl.Visible = True
SheetNum = 1
Do While recordtotal > 0
    rs.Open "Select top 60000 * from tblRemedInternal", cn, 2, 2
    If rs.EOF Then Exit Sub
        Set sht = Nothing
        On Error Resume Next
        Set sht = xlWB.Worksheets("Sheet" & SheetNum)
        On Error GoTo 0
        If sht Is Nothing Then
            With xlWB
               .Worksheets.Add After:=.Worksheets(.Worksheets.count)
               Set sht = .Worksheets(.Worksheets.count)
               sht.Name = "Sheet" & SheetNum
            End With
        End If
        For col = 0 To rs.Fields.count - 1
            sht.Cells(1, col + 1).Value = rs.Fields(col).Name
        sht.Range("A2").CopyFromRecordset rs
        SheetNum = SheetNum + 1
        str_sql = "delete from tblRemedInternal where ProdID in(Select top 60000 ProdID from tblRemedInternal)"
        DoCmd.RunSQL (str_sql)
    recordtotal = DCount("ProdID", "tblRemedInternal")

xlWB.Close (True)
Set xl = Nothing

End Sub
LVL 22

Assisted Solution

rspahitz earned 500 total points
ID: 39633994
"    rs.Open "Select top 60000 * from tblRemedInternal", cn, 2, 2"
"resulting Excel file has been over 60MB"

60MB (60,000,000)/60000 = 1000 bytes per Excel line.  That's reasonable for any particular record in a table.

That means that the uncompressed version of Excel if probably about right in size.  So you want to find a way to make this smaller?  There are several ways.

1) As tel2 seemed to be indicating, you could save it in Excel's new (2007) format, but maybe as an xlsb file.  I think that's the compressed one.
2) You could encode various pieces of data as you insert them into Excel.  This will require inserting data one cell at a time rather than
sht.Range("A2").CopyFromRecordset rs

Open in new window

but gives you more flexibility in doing various types of optimization (like inserting id's that reference longer common fields (like CA instead of California, or 1 instead of Hourly / 2 instead of Salaried.) You could also encode numbers like 1234 to convert them into Hexadecimal and potentially save space when saving as a string (although not always).
3) And the last way I can think of is to simply "zip" (compact) the file after you've created it.

BTW Instead of saving as an Excel spreadsheet, have you considered saving it as a CSV file?  With that, you typically get a lot less overhead that's created from an application file such as Excel.
LVL 12

Assisted Solution

tel2 earned 500 total points
ID: 39634687
Hi rspahitz,

> 1) As tel2 seemed to be indicating, you could save it in Excel's new (2007) format, but maybe as an xlsb file.  I think that's the compressed one.
I don't know anything about .xlsb, but as far as I know, all the .xlsx, .docx & .pptx are compressed (zipped) XML files.  To prove this, just add ".zip" to the end of the filename (e.g., then try to open the file, and your unzipping software should show the files it contains (most of which are .xml files).  It works for me.

> 3) And the last way I can think of is to simply "zip" (compact) the file after you've created it.
I've suggested this, too.
LVL 10

Accepted Solution

broro183 earned 500 total points
ID: 39638427
Hi everyone,

Yes, I believe the xlsb (proprietory binary) format would be the way to go. I have been able to generate excel files that are orders of magnitude smaller in the xlsb format than in either of the other excel 2007+ formats (xlsx or xlsm). Here are some comments about xlsb from my work's IT team:

Using .XLSB does make a file smaller hence quicker to save and open, which will be an advantage for very large files.  XLSB does have limitations though.  
·         It does not contain XML code which may be needed if the file is being used with an external interface.  If you don’t know what XML code is, chances are you’re not using it though.
·         An XLSB file won’t notify a user when a file contains macros either, whereas you know that an XLSX file does not contain macros, and an XLSM file may.  
·         Finally, some file reading software may not recognise an XLSB file, for example, when using a smartphone.

So, if you’ve got large files that you use on a regular basis via PC only, XLSB may be the way to go.  Excel Options are where to go to change your default setting to XLSB.

>>2) You could encode various pieces of data as you insert them into Excel.  This will require inserting data one cell at a time rather than
sht.Range("A2").CopyFromRecordset rs

Open in new window

Cell by cell insertion can be painfully slow. I'd suggest continued use of CopyFromrecordset to transfer the data as a single block & either doing any optimisation within excel or, preferably, within Access/VBA before using Copy/fromRecordset. For example (to continue rspahitz's example), having a lookup table that is used to lookup & replace "CA instead of California", or running code to Trim field elements within the recordset before transferring it to the worksheet.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question