Templates & File Size

  • Hi All,

    I have a problem.

    This ones fairly complex, for me anyway ;).

    I have a template that is opened (as a copy) and then worked on. A lot of information is entered and calculated as well as imported by way of FORM addins etc. The end result is that the file is SavedAs xyz.xls or whatever name. The file ends up being in the order of 3 to 4 MB in size by the time the project is finished.

    The problem is that many people are using the template and saving finished copies which equates to mega memory use.

    ie 30 users x 3 projects a day x 3MB = 270MB memory a day x 365 days = 98550MB a year. OUCH !!

    Everyone loves the project because it streamlines their work and reduces what used to take 5 days to do down to 2. But the all powerfull IT department have slammed it because of memory use.

    But back to the problem, how can I reduce the file size?? I've stripped my code back, I've deleted all unecessary formating, removed unwanted links........etc. and still have a fairly big file.

    I was wondering if anyone has ever thought about a way to save just the information that was entered when the workbook template was opened to the point of closing. And then to have it merge or combine with a template when you want to edit etc and again save the information. Essentially so that the template acts like an operating system and you only save the entred data???

    This way it would reduce the size of the files dramatically because I'm not saving the whole template again.

    Any thoughts, suggestions or help ??

    Thanks in advance.


  • Just my 2 cents without seeing your particular file:
    1) Is the file 3-4 mb mainly because of physical size (number of sheets/rows/columns) or number of lookup formulas?
    2) If its lookup formulas can you substitute for VBA code instead? You might also be able to skip a few tables by using code & arrays instead.
    3) I've also found that =sumifs instead of =vlookups helps with file size.
    4) # of rows use less space than # of columns,.Can you move tables below each other instead of beside each other?
    Hope these suggestions help, tho' you've probably looked at this already.

  • You say that you are importing data. Is each user essentially saving the same imported data in each file? Can you blow away the imported data before saving?

  • Egad,

    Thanks for tips and ideas. I've done most of this already, my application is a little complex in that each column of data is treated as a seperate set of data.

    It's a quote package and each column represent a piece of equipment. One Sheet is for calculations and selections, one for costing, one for sell pricing, one for factory orders etc. and each column represents a piece of equipment.

    It can be populated upto the last column or 251 pieces of equipment so memory use is mainly a function of the amount of data being physicaly entered.

    Having thought about this a bit I am toying with the idea of having the input recorded via VBE into a new module and then having the module saved with the filename into a specific location as a *.bas file through the VBE.

    Next time they want to view the file they can import the *.bas which will auto execute (by code) through an interface I will build. This way I am only saving the actual keystrokes and commands they have performed and not the whole template. When they open a new template they will have the option of populating it with an existing dataset.

    Of course this is a very rough idea at this time and will need a fair amount of work to perfect. That's why i was hoping someone else may have already done similar??

    Thanks again


  • I have set up similar templates to what you describe and encountered the same problem. As a solution I wrote a macro to only save the text of the workbook when the file is saved.
    I added an 'INDEX' sheet to the workbook which quickly loads the directory of text files. The INDEX includes an OPEN button so when you select a text file it is imported back into the template. The template is fully formatted with data validations, cross links to other files, graphs, tables etc. a 3mb file. The text files are only 2kb to 50 kb in length a massive saving. The text save macro is in the before save event, it dumps the text to file and cancels the workbook save.

    Hope you find this usefull.

  • Hi Dave,

    Thanks for the link, I've done all that many times over.

    I'm pretty sure that even your File Size Reducer Addin would have a problem finding me some savings ;)

    Thanks anyway.

    :o Insomniac..............you are getting sleepy........very very sleepy.........listen to the dull tones of your computer fans buzzing.................listen to the rythmic clicking of your hard drive...............your eyelids are now like lead........sleeeeeep........sleeeeeeeep..............zzzzzzzzzzzzzzzzzzzz.

    Now you will send me all the code you used to do the text capture "Pleasssssse"

    OK WAKE UP!!!!!!!!

    Sorry just messin around

    Tell me how did you capture the text entries and save them to a text file?

    Do you have an example of the code you used to do this? It would save me a swag of time if you were a very very very nice person and would share

    Grovel grovel smile :D


  • Hi AJW

    ...In that case I believe your file size in correctly reflecting the amount of data. Formulae can increase file size, but they can also be converted to values only. What I often do is have a Workbook Open Event that pulls in all external values via formulae, then converts them all to values only. :cool:

    If you have Pivot Tables ensure they are only referencing the needed data Dynamic Named Ranges are ideal for this and formulae references.

    RE: But the all powerfull IT department have slammed it because of memory use.

    I think you will find they will 'nit-pick' anything that they are not in full control of. If all they can do is whine about memory usage on a network they either have nothing better to do and/or your company is about to go into liquidation. :(

  • "Formulae can increase file size, but they can also be converted to values only. What I often do is have a Workbook Open Event that pulls in all external values via formulae, then converts them all to values only. :cool: "

    Thanks for the above suggestions Dave, unfortunatly I need the data and formula to remain live and ready to update should changes need to be made. We are constantly doing revision after revision of quote using the last revision. I trie converting or copying everything and pasting text only once before just as an exercise but didn't gain much from it. (That's when I realised that I needed to maintain the links.)

    I really like the idea of somehow recording the data entry & events then saving them only. I've been reading up on Workbook merge, Change History etc. It appears that this only applies to shared
    workbooks, I need to figure a way to firstly turn on the change history and secondly activate the merge_workbooks for an unshared one ?

    All too hard at he momment with a million other things to do. I'll need to give my approach some serious thought.


  • Hi AJW
    yes Iam awake again, here is some very simple code as example for you

    Option Explicit
    Option Base 1
    Public TextRange As String
    Public TextData(4)
    Public Rw As Integer, Cl As Byte
    Public Path As String
    Public FName As String
    Public Fnum As Integer
    Public Count As Integer
    'TEXT DUMP Excel Workbook ; Insomniac 2003

    Private Sub ALLOFF()
    Application.IgnoreRemoteRequests = True 'disable system
    Application.Calculation = xlCalculationManual 'disable calculation
    Application.EnableEvents = False 'disable events
    Application.ScreenUpdating = False 'prevent screen flicker
    End Sub

    Private Sub ALLON()
    Application.IgnoreRemoteRequests = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub

    Public Sub TextDump()

    Count = Application.WorksheetFunction.CountA(Sheets("DATA").Range("A:A"))
    TextRange = "A1:D" & Count

    'when writing text files you need to replace any quotation marks
    'that the user may have entered : I use apostrophy instead
    Sheets("DATA").Range(TextRange).Replace What:=Chr$(34), Replacement:=("'"), _
    SearchOrder:=xlByColumns, MatchCase:=True

    'create a filename
    FName = Application.UserName & Format(Date, "dd-mm-yy") & ".txt"

    Path = ThisWorkbook.Path & FName
    Fnum = FreeFile ' Get unused file number
    'On Error GoTo Done
    Open Path For Output As #Fnum
    For Rw = 1 To Count
    For Cl = 1 To 4
    TextData(Cl) = Sheets("DATA").Cells(Rw, Cl)
    MsgBox (TextData(1))
    Write #Fnum, TextData(1), TextData(2), TextData(3), TextData(4)

    Done: Err.Clear: Close #Fnum: On Error GoTo 0
    End Sub
    Public Sub TextLoad()
    Rw = 1
    FName = Application.UserName & Format(Date, "dd-mm-yy") & ".txt"
    Path = ThisWorkbook.Path & FName
    Fnum = FreeFile ' Get unused file number
    On Error GoTo Done
    Open Path For Input As #Fnum
    Do While Not EOF(1) ' read in file until finished
    Input #1, TextData(1), TextData(2), TextData(3), TextData(4)
    For Cl = 1 To 4
    Cells(Rw, Cl) = TextData(Cl)
    Rw = Rw + 1

    Done: Err.Clear: Close #Fnum: On Error GoTo 0
    End Sub

    Create a workbook with a sheet named "DATA"

    insert dummy data in columns A:D

    CodeNo. Date operation complete
    1 01-Jan see y
    2 02-Jan hear n
    3 03-Jan smell n
    4 04-Jan touch y

    insert a module and copy the code to it

    ***SAVE the wokbook***

    Run 'TextDump' to save the text
    Run 'TextLoad' to load the text

    As you can see we are only saving text in columns A to D the rest of the worksheet may contain formulas, hyperlinks etc and will remain intact. Other sheets may be graphs or tables that reference the text columns A:D on sheets 'DATA'

    this is a much simpler way for multiple users to acces all the formatting and links of a custom workbook without having write access to the workbook

    For user friendly no-fuss insert this code in the 'BeforeSave' workbook event code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call TextDump
    Cancel = True
    End Sub

    This will Dump the text file and prevent the XLWorkbook from saving

    As I mentioned in my previous post I usually add an INDEX sheet which loads all the text files for the USER to choose from (more code needed for this).

    One last point, sharing a workbook makes it extremely large and very user UNfreindly, if you simply dump each users input as text you can check with worksheet change selection event for file datetime and load in new data if it has been saved. I have Common Wokbooks with up to 20 Users at a time and they all see the same data instantly.

    Hope you can follow this, and yes I tested the code!!!!!!

  • Insomniac,

    Thanks heaps for the code and tips. It may take a little time for me to get at it due to current workload but I'm keen to give it a run. Hope you don't mind if I pick your brains on this now and again.

    Thanks again.

    AJW :yes:

  • High again AJW

    Iam not sure if the code I supplied will work as I see that this site has added 'Smiley faces'to some of it!

    Keep in mind that I set up my workbooks with this method in mind, I usually set aside columns A-Z for user entries and as the TextDump Range.(rest of columns may be formulas, links etc), and hide unused columns.
    You can still apply formats, validation or conditional formatting to the user area without ill effects.

    If you require to save thousands of rows of data it is far quicker to read and write the file as binary in a single instance ( you need to dimension an array to known number of rows).


    Dim TextArray(2000, 26)

    Sub TextSave()
    TextArray(2000, 26) = Range("A1:Z2000")
    Open "c:\textdump.txt" For Binary As #1
    Put #1, , TextArray 'writes to file
    Close #1
    End Sub

    Sub LoadText()
    Open "c:\textdump.txt" For Binary As #1
    Get #1, , TextArray 'reads complete file
    Range("A1:Z2000") = TextArray(2000, 26)
    Close #1
    End Sub

    One more note, the values of the cell are what is written depending on the cell formatting.
    text as text
    numbers as numbers
    dates as serial number :ie 01/01/03 as #37622#
    boolean as #True# or #false#

  • I stumbled upon this while browsing the forum and it is something that I have long been asked to address. I was thinking about saving the workbooks as pdf files but then, of course, the problem is how to revise (also a constant problem) without re-entering all the data at each revision. I had put it on the back burner but am really pleased to see the posting. Now if I could only understand Insomniacs code!!!



  • OK, I've been researchingthis topic further based on a hint given in Insomniacs post about Binnary File Format.

    Apparently from what I've picked up using VBA you can save the file as a Binary File?? to conserve file space and them somehow open it again??

    Does anyone have any insight into this ??



  • Hi AJW again,

    Here is a small demonstration workbook, had to find time to throw it together.

    Download and save in a new folder

    Read the NOTE sheet

    Use any of it that you find of value!

    (There is code in the Workbook and sheet DATA as well as module1)

    Regards Insomniac

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!