Saving during code execution

Readers' Initial Reactions

"Heck!  Everybody knows to save a workbook during code execution all you need is 'Workbooks(<workbook_name>).save'.  What's this moron getting to?

The real problem...besides not being clear in the title of this entry

Sometimes during code execution there are so many changes introduced to a workbook that Excel locks up because there is no more memory to hold the changes.

Brilliant solution

Compare the memory used with the memory available and when a threshhold level is reached, save the workbook.

Less brilliant solutions

OK...I would have liked to use the brilliant solution if I would have had time.  Instead I used a couple of quicker and less brilliant solutions.

Count then Save

For one tool, I was importing a set of  input files, posting them to worksheets and specific cells in worksheets.  They came in no certain order, so the code runs like this

Constant MxFilesToProcessBeforeSave as Integer = 5

Sub ImportFromInputsFolder()

    Dim FileLongName as String

    Dim FilesProcessedSinceLastSave as Integer

    Dim FileProcessed as Boolean

    Dim wkbkTarget as Workbook

    <excluded setup calls>

    FileLongName = dir(Inputs_Dir & IIf(Right(Inputs_Dir, 1) = "\", "", "\"), vbReadOnly + vbHidden + vbSystem)

    Do While FileLongName <> ""

        FileProcessed = False

        Call ProcessInputFile(FileLongName, FileProcessed  )

        '   Get next file

        If FileProcessed Then

            FilesProcessedSinceLastSave  = FilesProcessedSinceLastSave  + 1

            If FilesProcessedSinceLastSave > MxFilesToProcessBeforeSave Then

               wkbkTarget.Save

               FilesProcessedSinceLastSave  = 0

            End If

        End If

        FileLongName = dir

    Loop

End Sub

Changing Sets

Another problem arose when trying to reset the input worksheets for the new month.  One solution would have been to delete the old worksheets and re-add them.  However, the person who had originally created the workbook methodically named and ordered the worksheets.  Each sheet was named with  a number (1 through 12) indicating the organization, a period, then a letter (d,o,i,f, or c) indicating the content of the worksheet.  Therefore when executing a "For each ws in Workbook.worksheets" loop I was able to add code to see when we changed the group.

Sub ClearWksheets(wkbkTarget)

    Dim ws As Worksheet

    Dim LastWSFamily As String

    For Each ws In wkbkTarget.Worksheets

        If Left(Right(ws.Name, 2), 1) = "." Then

            If Right(ws.Name, 1) <> LastWSFamily Then

                Application.StatusBar = "Saving before resetting " & ws.Name

                wkbkTarget.Save

                LastWSFamily = Right(ws.Name, 1)

            End If

        End If

        Application.StatusBar = "Resetting " & ws.Name

        Select Case ws.Name

            Case <name of worksheet that needs no processing>

            Case <name of worksheet that needs has areas with a special name pattern that can be cleared by a subroutine searching for that name pattern>

                ClearWkshtDataAreas 

ws:=ws ' This is a Sub to clear Named areas DataArea, DataArea1, DataArea2 etc

            Case Else

                If LCase(Right(ws.Name, Len("xref"))) <> "xref" Then ' "xref" sheets have tables used in vlookups for cross-referencing different labels for the same data.

                    

ws.Rows.OutlineLevel = 1 ' Removes outlining

                    ws.Rows.Hidden = False ' These two lines ensure the "Clear" gets all data on sheet

                    ws.Columns.Hidden = False

                    ws.Cells.Clear

                    ws.Visible = xlSheetHidden ' This hides sheets.  The procedure for adding current month's data shows (unhides) the worksheets as each worksheet is populated.  This allows users to check progress without clicking on each worksheet to see if it is populated.

                End If

        End Select

    Next ws

    Application.StatusBar = ""

End Sub