Ballooning Excel file!

[ragtopcaddy via OfficeKB.com]

I am creating, from an Access97 function, an xls file from an xls template.
The template is 17MB. I've been working on this project for a couple of days.
The first files I created were approx 27MB in size. Now, all of a sudden, for
no apparent reason, using a similar sized dataset from Access, the xls file
has ballooned to a whopping 133MB. What could be causing this, and how might
I fix it? If I don't solve this, I will have to start all over again.

Here is my entire function from Access97 VBA:

Function XLTemplate(strPath As String, strTemplate As String, strShtName As
String, _
strNmdRange As String, strRS As String,
strSavePath As String) As Boolean

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Const conMAX_ROWS = 20000
Dim strDate As String
Dim strWkBK As String
Dim strRange As String
Dim iRow As Integer

strDate = Format(Date - Choose(WeekDay(Date, vbMonday), 3, 1, 1, 1, 1, 1, 2)
, "mm_dd_yy")
strWkBK = "Paul Rpt_" & strDate & "_Repo.xls"
Set objXL = New Excel.Application

With objXL
.ScreenUpdating = False
.Visible = True
Set objWkb = .Workbooks.Open(strPath & strTemplate)
objWkb.SaveAs strSavePath & strWkBK
objWkb.CLOSE True
Set objWkb = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, strRS,
strSavePath & strWkBK, , strNmdRange
Set objWkb = .Workbooks.Open(strSavePath & strWkBK)
Set objSht = objWkb.Worksheets(strShtName)
objSht.Activate
objSht.Range(strNmdRange).Select
.Selection.WrapText = False
.Cells.EntireColumn.AutoFit
.Range("A4").Select

Set objSht = .Worksheets("Repo")
objSht.Select
.ActiveSheet.PivotTables("Repo_Pivot").PivotCache.Refresh
Set objSht = .Worksheets("Sub super cluster by cusip")
objSht.Select
.ActiveSheet.PivotTables("Inventory_Pivot").PivotCache.Refresh
iRow = .Range("A4").End(xlDown).Row
objSht.Range("A3").Select
Set objSht = .Worksheets("Combined")
objSht.Select
objSht.Rows(iRow & ":11001").Select
.Selection.ClearContents
.Range("A1:AO" & iRow - 2).Select
.Selection.WrapText = False
.Cells.EntireColumn.AutoFit
.CutCopyMode = False
.Selection.Copy
.Sheets.Add After:=.Sheets("Assumptions")
With .ActiveSheet
With .Cells
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
objXL.CutCopyMode = False
End With
.Name = "Current"
End With
Set objSht = .Worksheets("Current")
objSht.Select
.ActiveWindow.DisplayZeros = False
.Selection.WrapText = False
.Cells.EntireColumn.AutoFit
objSht.Cells(4, 1).Select

.Selection.AutoFilter Field:=4, Criteria1:="=GOVERNMENT", Operator:=xlOr,
_
Criteria2:="=AGENCY"
.Cells.Select
.Selection.Copy
.Sheets.Add After:=.Sheets("Current")
With .ActiveSheet
With .Cells
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
objXL.CutCopyMode = False
End With
.Name = "Agency_Govts"
End With
.ActiveWindow.DisplayZeros = False
.Selection.WrapText = False
.Cells.EntireColumn.AutoFit
.Range("A4").Select
.Selection.End(xlDown).Select
.Range(.Selection, .Selection.End(xlToRight)).Select
With .Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.Selection.End(xlLeft).Select
.ActiveCell.Offset(1, 0).Activate


objSht.Select
objSht.ShowAllData
.Selection.AutoFilter Field:=4, Criteria1:="<>GOVERNMENT", Operator:
=xlAnd _
, Criteria2:="<>AGENCY"
.Cells.Select
.Selection.Copy
.Sheets.Add After:=.Sheets("Agency_Govts")
With .ActiveSheet
With .Cells
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
objXL.CutCopyMode = False
End With
.Name = "Corp_Supra_catchall"
End With
.ActiveWindow.DisplayZeros = False
.Selection.WrapText = False
.Cells.EntireColumn.AutoFit
.Range("A4").Select
.Selection.End(xlDown).Select
.Range(.Selection, .Selection.End(xlToRight)).Select
With .Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
.Selection.End(xlLeft).Select
.ActiveCell
.Range("A4").Select
objSht.ShowAllData

End With
XLTemplate = True

OuttaHere:
On Error Resume Next
objWkb.CLOSE True
objXL.ScreenUpdating = True
objXL.CutCopyMode = False
objXL.Quit
Echo True
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Function

Thanks,

Bill Reed

 

[ragtopcaddy via OfficeKB.com]

PS: It takes forever to save the file, even after I have stripped out most of
the unused sheets used to create the 3 final sheets (still 107MB, with very
little data in it).

I am creating, from an Access97 function, an xls file from an xls template.
The template is 17MB. I've been working on this project for a couple of days.
The first files I created were approx 27MB in size. Now, all of a sudden, for
no apparent reason, using a similar sized dataset from Access, the xls file
has ballooned to a whopping 133MB. What could be causing this, and how might
I fix it? If I don't solve this, I will have to start all over again.

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200711/1

 

[Alan]

Have you tried finding your actual last used cell and then using Control >
End to find what Excel thinks is the last used cell?
I'm not familiar with Access code but if you import large amounts of data
into Excel, say down to row 20,000 and then clear contents, Excel will still
think that row 20,000 contains the last used cell. You have to delete the
entire rows from your actual last used cell row down to row 20,000, save,
exit and re-open.
If you have only three sheets and the file is 107MB it sounds like Excel
thinks they are either completely or nearly full of data.
Regards,
Alan.

 

[ragtopcaddy via OfficeKB.com]

Alan,

Thank you for your suggestion. I will check this out later today.

Bill

Have you tried finding your actual last used cell and then using Control >
End to find what Excel thinks is the last used cell?
I'm not familiar with Access code but if you import large amounts of data
into Excel, say down to row 20,000 and then clear contents, Excel will still
think that row 20,000 contains the last used cell. You have to delete the
entire rows from your actual last used cell row down to row 20,000, save,
exit and re-open.
If you have only three sheets and the file is 107MB it sounds like Excel
thinks they are either completely or nearly full of data.
Regards,
Alan.

PS: It takes forever to save the file, even after I have stripped out most
of

[quoted text clipped - 13 lines]

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200711/1

 

[JP]

Check out http://www.mvps.org/dmcritchie/excel/lastcell.htm for more
help on programmatically resetting the used range of a worksheet.


HTH,
JP

Alan,

Thank you for your suggestion. I will check this out later today.

Bill

Have you tried finding your actual last used cell and then using Control >
End to find what Excel thinks is the last used cell?
I'm not familiar with Access code but if you import large amounts of data
into Excel, say down to row 20,000 and then clear contents, Excel will still
think that row 20,000 contains the last used cell. You have to delete the
entire rows from your actual last used cell row down to row 20,000, save,
exit and re-open.
If you have only three sheets and the file is 107MB it sounds like Excel
thinks they are either completely or nearly full of data.
Regards,
Alan.

PS: It takes forever to save the file, even after I have stripped out most
of

[quoted text clipped - 13 lines]

might
I fix it? If I don't solve this, I will have to start all over again.

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-new/200711/1- Hide quoted text -

- Show quoted text -