Solved

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

Posted on 2013-11-07
7
1,218 Views
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.
0
Comment
Question by:zimmer9
7 Comments
 
LVL 12

Expert Comment

by:tel2
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).
0
 
LVL 22

Expert Comment

by:rspahitz
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?
0
 
LVL 10

Assisted Solution

by:mark_harris231
mark_harris231 earned 125 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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zimmer9
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
    rs.MoveFirst
        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
        Next
        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)
    rs.Close
    recordtotal = DCount("ProdID", "tblRemedInternal")
Loop

xlWB.Close (True)
xl.Quit
Set xl = Nothing

End Sub
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 125 total points
ID: 39633994
Hmmm...
"    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.
0
 
LVL 12

Assisted Solution

by:tel2
tel2 earned 125 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. myspreadsheet.xlsx.zip), 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.
0
 
LVL 10

Accepted Solution

by:
broro183 earned 125 total points
ID: 39638427
Hi everyone,

1)
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.

hth
Rob
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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