Excel - save only the changes that are made ? 1

[pabby]

HI,

A colleague of mine who is developing a spreadsheet for our business to incorporate a lot of calculations has mentioned that the spreadsheet is 200mb without any data in it.(due to all the formulae) When it saves - does it save all of the excel sheets etc or can it be set to save only the data that has been changed/inserted.

Once data is added to this spreadsheet it will grow dramatically and will take a minute or more to save data - is there any way to speed this up or is there an alternative product to excel. Ultimately we want to have the save time as fast as possible.

Any ideas ?

thanks,

Paul

 

[grande]

If you want to stick with simple MS products, I'd take the MS Access route. Rather than spreadsheets, go for a database.

-------------------------
Just call me Captain Awesome.

 

[KenWright]

Tell him to run a backup daily, or to prepare for heartache when it dies, because that is a huge size for a spreadsheet. I've yet to see one that requires anything like that amount of filesize, and if you are right in that it is going to grow dramatically, I'd suggest he possibly rethink his approach.

Just curious, but what version of Excel is he running (I hope it's Excel 2003)?

He should also take a look at Charles William's site on Excels internal memory limits - Also lots of info re calculation on there:-

http://www.decisionmodels.com/memlimitsc.htm

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

Or if he must use Excel: try building a macro that applies the formulae and then copies the record to values - formulae take up loads of memory, filesize and calculation overhead.
I have a standard whereby I have formulae two rows above my 'fieldnames' and a macro that allows me to select a range of the fieldnames. It steps through each cell in the selection applying the formula to rows above it to every row in that column, calculating just that column then converting to values.

Thanks,

Gavin

 

[grande]

Gavin - That's some good thinking! Have a star!

-------------------------
Just call me Captain Awesome.

 

[Gavona]

Thanks for the star, Captain Awesome. I do agree though that you and Ken are probably right about the ideal solution.
To properly deserve that star perhaps my code would be helpful to Pabby or anyone else. my standardised approach includes the use of rangenames. The database is called "Alldata". I use each column heading to name the range below it. These three subroutines help with that (and with an expanding database). (I prefer not to use dynamic rangenames so I can select the range using the NameBox)

Sub Alldata_Create()
Selection.CurrentRegion.Name = "Alldata"
End Sub

Sub Alldata_Expand()
Range("Alldata").CurrentRegion.Name = "Alldata"
End Sub

Sub Alldata_FieldNames_Create()
Range("Alldata").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
End Sub

Having used the last of the above routines I can call this routine from code:

Private Sub CopyFormula(MyName)
'This sub is called by another macro containing lines like _
     CopyFormula ("Level")
'MyName is the name of the range to which data is to be copied _
 it does not include the heading.
Application.StatusBar = "Applying Formulae to " + MyName
With Range(MyName)
    .Cells(-2, 1).Copy
    .Cells.PasteSpecial (xlPasteAll)
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With
Application.StatusBar = " Finished" + "Applying Formulae to " + MyName
End Sub

But as a general purpose routine I like to be able to select a range of headings and refresh the values in thos columns by re-applying the formulae:

Sub FormulaCopy()
'The formulae are 2 rows above the heading i.e.
'   Formula
'   [blank]
'   Heading
'   first cell of range named ....
' Macro created 17/05/2005 by gk
'
Dim Myprompt As String, Response As String, Style As Integer

Myprompt = "For each cell in current selection the macro copies formulae in the second row above the selected cell to all cells below the selected cell. (actually there must be a named range equal to the text in the selected cell and it is this range that is copied to.  Calculation may be set to manual as the routine calculates the pasted cells (only) and then pastes them to values"
Style = vbOKCancel + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each c In Selection
Application.StatusBar = "Applying Formulae to " + c.Value
With Range(Replace(c.Value, " ", "_"))
    .Cells(-2, 1).Copy
    .Cells.PasteSpecial (xlPasteAll)
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With
Next c

Myprompt = "Macro Finished - calculation set to automatic"
Style = vbOK + vbCritical + vbDefaultButton2   ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub
Application.StatusBar = "Setting calculation to automatic"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub

Note that Replace(c.Value, " ", "_") is used rather than c.value because I often have spaces in my column headings and Excel replaces these with underscores when it creates the named ranges. You would need to use a similar approach with certain other characters.

Thanks,

Gavin