Free Microsoft Excel 2013 Quick Reference

Why does file size grow ten fold?

I have created a database using Excel 2003 and VBA.

A worksheet ‘form’ enables the user to view records. Once a record number
is entered into a cell on the form the VLOOKUP function finds the relevant
data to populate the form from another worksheet that contains the database.
One of the fields (cell) on the ‘database’ worksheet contains the file
name of a picture file.

On the ‘form’ worksheet is an ‘Image’ control to show the picture named in
the database.

Once a record number is entered onto the ‘form’ the following code places
the name of the picture file into the picture property of the Image control
and the picture is displayed on the ‘form’ worksheet.

Before the code is run the size of the workbook is approximately 2.5Mb.
After the code is run the size of the workbook increases to approximately
25Mb. The picture file is a .jpg and is less than 1Mb in size.

Why does the file size increase after running code?

How can I prevent the file from increasing so dramatically in size?

Any assistance will be most welcome.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim intCellValue As Integer
Dim intTopValue As Integer
Dim strImgName As String
Dim imgPath
Dim strName As String
Dim strPath As String
Dim picPicture As IPictureDisp

If ActiveCell = Cells(5, 3) Then
On Error GoTo ErrorHandler
myActiveCell = ActiveCell.Address
intCellValue = Cells(5, 3).Value 'Record Number
intTopValue = Cells(1, 15).Value 'Last Record Number
varWhat = VarType(intCellValue)
If intCellValue > 0 Then
If intFindRecordNo <= intTopValue Then
'code to pass name of picture file to image
'get file name
Application.ScreenUpdating = False
Sheets("CPDB").Visible = xlSheetVisible
Sheets("CPDB").Select 'database worksheet
ActiveSheet.Unprotect
ActiveSheet.Cells(intCellValue, 24).Select
strImgName = ActiveCell.Value
ActiveSheet.Protect
Sheets("CPDB").Visible = xlSheetHidden
'check if file name there
If Len(Trim(strImgName)) > 0 Then
Sheets("View Project").Select
strPath = ActiveWorkbook.Path & "" & strImgName
imgPath = strPath
Set picPicture = stdole.StdFunctions.LoadPicture( _
imgPath)
Sheets("View Project").Select 'Form worksheet
With ActiveSheet.Image1
.Picture = picPicture
End With
ActiveSheet.Image1.Visible = True
Else
Sheets("View Project").Select
ActiveSheet.Image1.Visible = False
End If
Application.ScreenUpdating = True
End If
Else
ActiveSheet.Image1.Visible = False
End If
End If
Exit Sub
ErrorHandler:
Sheets("View Project").Select
ActiveSheet.Image1.Visible = False
MyMsgbox = MsgBox("The picture file entered for this project " + _
vbCrLf + "does not exist or has been named incorrectly.", , "Project
Image")
End Sub

--
Derek Dowle


Post your answer or comment

comments powered by Disqus
Hi there,

I have written a macro that copies cells in a column of spreadsheet (A) into a column in spreadsheet (B) and then saves off a copy of spreadsheet (A) and moves onto the next column in spreadsheet (B) and repeats the process.

Everythign works fine except for one thing - Each iteration of the loop increases in file size by about 50 kb. If I have 56 iterations, the last spreadsheets that are created have huge file sizes even though they are no different in appearance from the first spreadsheet.

The weird thing is this: If i open the workbook and save it again, the file size reduces to the original size - 400kb. Does anybody know where this phantom file size increase is coming from?

(I have set cutcopymode to false, btw)

Additionally, I clear out all the changing cells in spreadsheet (a) before filling them in with cells from the next column in (B).

Does anybody know why the file size would increase? Is there some history thats being captured by any chance?

I have a macro that cuts columns and inserts them into a new position in a sheet, and does so on two different sheets. The file before is 562KB. Once I run the macro and save, the file size jumps to 749KB. If I then close the file, reopen, and save, the file size goes back to 562KB. I'm looking for ideas as to why this is happening. Thanks!

After inserting comments the file size has grown from 1.5 meg to over 45 meg.
There are approximately 7 worksheets.
Each Worksheet data range is about from 25 columns wide and 650 rows long.
Not all cells have a comment, but most of that range does.
That means that each worksheet contains less than 16,250 comments with data.
The entire workbook contains less than 113,750 comments with data.
Is there any reason why the file size of this document grows substantially?

I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at http://www.vbaexpress.com/kb/getarticle.php?kb_id=83


	VB:
	
 
 
Sub ExcelDiet() 
     
    Dim j               As Long 
    Dim k               As Long 
    Dim LastRow         As Long 
    Dim LastCol         As Long 
    Dim ColFormula      As Range 
    Dim RowFormula      As Range 
    Dim ColValue        As Range 
    Dim RowValue        As Range 
    Dim Shp             As Shape 
    Dim ws              As Worksheet 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    On Error Resume Next 
     
    For Each ws In Worksheets 
        With ws 
             'Find the last used cell with a formula and value
             'Search by Columns and Rows
            On Error Resume Next 
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            On Error Goto 0 
             
             'Determine the last column
            If ColFormula Is Nothing Then 
                LastCol = 0 
            Else 
                LastCol = ColFormula.Column 
            End If 
            If Not ColValue Is Nothing Then 
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column) 
            End If 
             
             'Determine the last row
            If RowFormula Is Nothing Then 
                LastRow = 0 
            Else 
                LastRow = RowFormula.Row 
            End If 
            If Not RowValue Is Nothing Then 
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row) 
            End If 
             
             'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes 
                j = 0 
                k = 0 
                On Error Resume Next 
                j = Shp.TopLeftCell.Row 
                k = Shp.TopLeftCell.Column 
                On Error Goto 0 
                If j > 0 And k > 0 Then 
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height 
                        j = j + 1 
                    Loop 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    Loop 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            Next 
             
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete 
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete 
        End With 
    Next 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?

Hi guys,

I have a workbook that balloons from around 200kb to 18mb after some formatting. the original page has 3 macros run on it which just do some replaces and formatting, then filtered data is copied to 9 other tabs.

Does anyone know why the file size would increase so much?

Cheers,

Will

I have files which are output from Access to Excel using the Transfer
Spreadsheet facilty in Access. If I don't delete the previous version of the
file and just overwrite each time then the file size grows despite the data
only changing slightly. It seems to increase by about 1Mb each time.
Anybody else come across this?
--
Les S

Hi,

I've got a strange problem. After we add and edit the list and then after we
refresh our pivot table (a file on a shared drive) the file size grows
exponentially. However it doesn't seem to do it then and there. The size
seems to be greater the next day.
Its a real problem for us.

Any help or experience with this problem?

Gor

Hello all, I'm having an issue where I click on an excel file within the windows explorer and press ctrl-c to copy it and then ctrl-v to paste it in the same location. Windows automatically names the file "Copy of [Original Filename]". The problem is that this file is mysteriously about 20~30% larger in file size. The original is about 550 KB, while the copy of it is 700 KB.

Any ideas on why this might be the case?

Hello all
I have a template that I use for requisitions. Last week I updated / improved it by adding extra functionality to it using VLOOKUPs.
However I am puzzled why the file size has grown from a manageable 222kb right up to 1.53Mb..
Would the VLOOKUPs seriously take this much memory? Or must it be some other reason?
Thanks in anticipation.......

Hi,

I have an Macro enabled Excel 2007 file which is 30MB in size. It is a "Master Template", and I use macros to delete bits out of it when they are not required. So, the macro which removes most of the tabs and also hides 19000 rows out of 20000 rows on the "Reporting" sheet (to get it to its most basic form) brings the file size down to 3MB - fine. However, when I then run another macro to actually delete these hidden rows, instead of just hiding them, the file size jumps back up to 27MB! The rows have been deleted, and the end cell is where I would expect it to be, so I am stuck as to why the file size is so massive.

Any ideas, guys?

James

I am using a Shared Workbook (about 10 users).

For some reason the file size grows from 7Mb to 250Mb in a day, but the actual additional data entered during the day is quite small. I am not logging data (under advanced tab), and have auto save set to 5mins. I check at the end of the and everyone logs off OK e.g only me left in the shared listing.

I have tried to find answers to the problem and ways of reducing the file size back from 250Mb to the starting 7Mb but failed.

Can someone explain this problem? I can't believe I am the only one who has it.

Using Office 2003

Thanks Ed

My file size was about 480kb. I then added a couple of additional macros
(maybe about 5 more, relatively short), and added some conditional
formatting. Suddenly the file size jumped to an unwieldy 3.8mb!

Half a meg was kind of large, 3.8mb is almost unusable. Any suggestions?
Thanks.

When I select a columns and then copy and paste them to another sheet, the file size grows tremendouslyl.

For instance, a 174 KB 4-worksheet file will grow to 6+ MB when I copy three of the sheets (column-by-column, but only half of the columns, however) to three new sheets in the same file. When I copy the three new sheets to a new workbook, the file size is just as big as the original (6+ MB).

When I select from top left corner to lower right corner of the used cells, the file size remains relatively small and reasonable (400 KB).

Can anyone tell me how to avoid this file size explosion and still do the copy process colum-by-column, because I need to copy the columns in a different order than they are on the original worksheet?

Regards,

Jim

This seems to happen in both Office 2002 and Office 2003:
I have a workbook that is 90Kb in size.
I open the workbook, do nothing other than invoke a single specific macro, then save the file and it has increased in size to 1.5 Mb. I can think of no reason why the file size should increase, and I would rather that it did not. Any pointers?

The offending macro DeleteRow() which I invoke is intended to delete certain rows common to the selected range, and then hide all rows below a specific row (these rows were previously hidden but new rows inserted at the bottom of the worksheet become visible by default).

Sub DeleteRow()
Dim rSelection As Range
Call PrepareStart
With Application.ThisWorkbook.Worksheets("Analysis")
   If .Index <> ActiveSheet.Index Then GoTo ExitSubDeleteRow
   Set rSelection = Intersect(ActiveWindow.RangeSelection, .Range("AnalRowSelection"))
   If rSelection Is Nothing Then GoTo ExitSubDeleteRow
   rSelection.EntireRow.Delete
   Application.CalculateFull
   .Range("HiddenRows").EntireRow.Hidden = True
   .Rows(65536).EntireRow.Hidden = True
    ExitSubDeleteRow:
End With 'Application.ThisWorkbook.Worksheets("Analysis")
Call PrepareEnd
End Sub 'DeleteRow()

Private Sub PrepareStart()
With Application
   .ScreenUpdating = False
   .ThisWorkbook.Worksheets("Analysis").Unprotect
End With 'Application
End Sub 'PrepareStart()


Private Sub PrepareEnd()
With Application
   .ScreenUpdating = True
   .ThisWorkbook.Worksheets("Analysis").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With 'Application
End Sub 'PrepareStart()
The named range "HiddenRows" refers to
=OFFSET(Analysis!AnalRowTemplate,-1,0,65537-ROW(Analysis!AnalRowTemplate),1)

If it helps, I have uploaded the original workbook here:
http://www.keepandshare.com/doc/1857...59-pm-92k?da=y

All,

I have a series of macros that format an excel spreadsheet and produces a overview page of the results. Nothing too fancy at all.

However when the macro runs the file size grows from 500 kb to around 7Mb.

Has anyone else seen this and how did you overcome it

thanks

Richard

My file size was about 480kb. I then added a couple of additional macros
(maybe about 5 more, relatively short), and added some conditional
formatting. Suddenly the file size jumped to an unwieldy 3.8mb!

Half a meg was kind of large, 3.8mb is almost unusable. Any suggestions?
Thanks.

I am automating an excel spreadsheet by using a userform with several
textboxes and a flexgrid. It works fine, but every so often for
whatever reason, the file size of the excel file increases in a very
large manner. I started with a 43kb file and it was working fine. Then
I noticed my machince was so and when I checked the file size it was
like 33Mb, but if I go back to the original file and redo everything it
is fine again for a while. Does anyone know what causes this? It has
happened in both Windows XP and Windows 2000.

Roger

why excel file does not open sometimes.

I've to constantly work on an excel file, but while it worked perfect with macros last time, NOW when I try to open it by double clicking in explorer, although the excel starts but file does not open, i.e., the contents are not displayed-although in background, it has opened (& hence while closing excel, it asks whether I want to save the changes in the opened file (although the file contents did NOT open).

This happens off & on (not all the times)

Why is it happening to this file? (size is 433 kb or around this) and it has about 15 macros.

Thanks

I have a access database that allows the user to import all tables from another database(s) for viewing purposes. I used VBA code to handle the importing of the tables. I also have code that deletes the tables when the form is closed since they are no longer needed.

Here is my problem.

I noticed that the file size on my Access Database keeps getting larger (currently at 1.9 GB), and I didn't know why. Here are the steps I took to figure out what the issue was.

I opened access and created a blank database and copied from my old database the Forms, Queries, Modules, And Code into this database. I then saved it and checked the file size. It was only about 2800 kb.

At this point realizing that the file keeps getting larger and is far bigger than it should be, I opened the form in the test scenario and imported a table, closed the form (therefore deleting the table just imported), and then closed the database. I checked the file size and it went from 2800kb to about 28,000 kb.

I then repeated the last step and imported the same table again, closed all the way out and the file was the same size.

Ok, so it didn't get bigger when I reimported the table that I had previously imported so I opened the test database and imported tables from 4 different databases and it grew again. Now the filesize is up to 128,000,000 kb.

My Question, if the database deletes the table after the user is done with information, why does the file continue to get larger? It has to be storing the table information in the database reguardless of me deleting the table. Anyone have any thoughts or insight that can help me. It would be greatly appreciated.

Hi all,

After I run the following filter on some records my file size goes from 500KB to 11MB.

Does anyone have any idea why?

I'm using Excel 97 SR-2


	VB:
	
 Button14_Click() 
     
    Cells.Select 
    Selection.EntireColumn.Hidden = False 
    Range("M30").Select 
    Selection.AutoFilter Field:=13 
    Selection.AutoFilter Field:=13, Criteria1:="CR Scheduled" 
    Columns("A:M").Select 
    Range("M1").Activate 
    Sheets("Next Release").Select 
    Cells.Select 
    Selection.EntireColumn.Hidden = False 
    Selection.ClearContents 
    Sheets("All CRs").Select 
    Selection.Copy 
    Sheets("Next Release").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
    Range("A1").Select 
    Sheets("All CRs").Select 
    Range("A1").Select 
    Application.CutCopyMode = False 
    ActiveWindow.SmallScroll ToRight:=3 
    Selection.AutoFilter Field:=13 
    Selection.AutoFilter Field:=13, Criteria1:="=Dev*", Operator:=xlAnd 
    ActiveWindow.SmallScroll Down:=-12 
    Columns("A:M").Select 
    Range("M1").Activate 
    Sheets("Development").Select 
    Cells.Select 
    Selection.EntireColumn.Hidden = False 
    Selection.ClearContents 
    Sheets("All CRs").Select 
    Columns("A:M").Select 
    Selection.Copy 
    Sheets("Development").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
    Range("A1").Select 
    Sheets("All CRs").Select 
    Application.CutCopyMode = False 
    Range("M1").Select 
    Selection.AutoFilter Field:=13 
    Selection.AutoFilter Field:=13, Criteria1:="=test*", Operator:=xlAnd 
    Columns("A:M").Select 
    Range("M1").Activate 
    Sheets("User Acceptance").Select 
    Cells.Select 
    Selection.EntireColumn.Hidden = False 
    Selection.ClearContents 
    Sheets("All CRs").Select 
    Selection.Copy 
    Sheets("User Acceptance").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
    Range("A1").Select 
    Sheets("All CRs").Select 
    Range("A1").Select 
    Application.CutCopyMode = False 
    Selection.AutoFilter Field:=13 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have these templates in excel and i don't know why their sizes keep increasing and like seroiusly what are the most size consuming things in and excel spreadsheet. First is does fill color make size greater, embedded macro code, formulas etc ???
i was just wondering are there ways to compact the file size at all !!

I know this is an awfully broad question, but if anyone has an idea I'd appreciate it.

I have been working on a project using VBA for all the grunt work. I noticed yesterday when I e-mailed the file from work to my house that the file size was around 250 to 300 kb. Today after tooling around and adding a few simple lines of code here and there to manipulate the output, I noticed the file size jumped to 2.7 MB. The only real off the wall thing I did was add a right click code that I saw in this forum and thought it could be pretty neat. I took this code back out but that really didn't change the file size much. Does anyone have an idea as to why the file would balloon up so large?

Any suggestions would be appreciated.

I find that I have one xls sheet, which only has not over 200 Kb of text.
However the file size in more than 6MB
I notice that the Auto Filter goes right up to the last column, even though the contents of all columns after Col K are blank. It does not even hold SPACES.

Not sure why it has resulted into such huge file. Has anyone faced a similar issue.
Thanks,

I'm developing a program which runs entirely from forms (there are 30 or so, which transfer control back and forth).

Among other files, the program opens and reads the contents of a small spreadsheet (named User Info.xls) containing user information-- name, phone, etc-- to save the trouble of entering it every time. The file is closed immediately after opening and reading.

The problem is this: at the main screen, the user can choose to quit. He/she is prompted to save the open data file (which is then closed), then the program is supposed to quit (Application.Quit).

If I run this sequence under control of the program, a dialog box pops up and asks me if I want to save the User Info.xls file (which is supposed to be closed at this point). The file actually is open, and can be seen behind the dialog box. Code execution has terminated at this point, also, although the spreadsheet containing the code is still open.

If, however, I single-step through the closing sequence, everything quits properly: no opening of User Info.xls, no prompts

If I insert a breakpoint just before the Application.Quit command, I can verify that User Info.xls is not open at that point. If I single-step (F8) from the breakpoint, everything closes normally, with no prompts: User Info.xls does not open.

SO WHY DOES THE FREAKIN' FILE OPEN ITSELF...

Um, excuse me. Ahem. Does anyone know why the file suddenly opens itself? This problem suddenly appeared a week or so ago-- everything used to close properly under program control.

Please help me from tearing out what remains of my hair. Thank you.


No luck finding an answer? You could always try Google.