Templates & File Size

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?

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 AJW

have you tried the steps shown here:
Excel File Size Blowout

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.

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()

ALLOFF
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)
Next
MsgBox (TextData(1))
Write #Fnum, TextData(1), TextData(2), TextData(3), TextData(4)
Next

Done: Err.Clear: Close #Fnum: On Error GoTo 0
ALLON
End Sub
Public Sub TextLoad()
Range("A:D").ClearContents
ALLOFF
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)
Next
Rw = Rw + 1
Loop

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

Create a workbook with a sheet named "DATA"

insert dummy data in columns A:D
eg

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!!!!!!

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

eg:

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.
eg:
text as text
numbers as numbers
dates as serial number :ie 01/01/03 as #37622#
boolean as #True# or #false#
etc

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!!!

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