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,170 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 11

Expert Comment

by:tel2
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:zimmer9
Comment Utility
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
Comment Utility
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 11

Assisted Solution

by:tel2
tel2 earned 125 total points
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now