Excel creates massive files on calling vba workbook.save method

[Tom Med]

I have what seems an innocuous bit of VBA code.

Private Sub App_WorkbookBeforeSave(ByVal WB As Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)

.....
Dim wbName As String
wbName = WB.name
Set WB = Nothing
getLog().wDebug "re requesting WB with no modules(" & wbName & ")"
Set WB = Application.Workbooks(wbName)
getLog().wDebug "Saving Changes(" & WB.name & ")"
WB.Save
Cancel = True
getLog().wDebug "ENDING overloaded before save event for (" &
WB.name & ")"
ErrorHandler:

End Sub

This catches the event of saving a worksheet.

But on the '.save' method excel goes into a flat spin and creates a
massive temporary file that continues to grow until excel dies
horribly.
In my log file I see this

DEBUG-26/09/2007 14:33:36-thmedlan-re requesting WB with no
modules(CRexPortfolioBuilder.xls)
DEBUG-26/09/2007 14:33:36-thmedlan-Saving
Changes(CRexPortfolioBuilder.xls)

Which seems to suggest the '.save' method is to blame. I have taken
the stack trace of what excel was doing at the time. Does anyone have
any ideas as to what I am doing wrong? and why workbook.save behaves
so badly?

Thanks
Tom


ntoskrnl.exe+0x584d
ntoskrnl.exe!KeQueryRuntimeThread+0x5e8
hal.dll+0x2ef2
hal.dll!HalRequestSoftwareInterrupt+0x3c
ntoskrnl.exe!Kei386EoiHelper+0x237d
SYMEVENT.SYS!EventObjectCreate+0xa20
SYMEVENT.SYS!SYMEvent_AllocVMData+0x5de9
ntoskrnl.exe!Kei386EoiHelper+0x237d
ntoskrnl.exe!ZwSetSystemInformation+0x23
ntdll.dll!KiFastSystemCallRet
ole32.dll!CoWaitForMultipleHandles+0x15be1
ole32.dll!WriteClassStm+0x2eb3
ole32.dll!WriteClassStm+0x2fd5
ole32.dll!StgCreateDocfile+0x691
ole32.dll!StgOpenStorageOnHandle+0xcfb
ole32.dll!CoFreeAllLibraries+0x185b
ole32.dll!WriteClassStm+0x5096
ole32.dll!WriteClassStm+0x5380
ole32.dll!WriteClassStm+0x3b06
ole32.dll!WriteClassStm+0x39d6
ole32.dll!WriteClassStm+0x365f
ole32.dll!WriteClassStm+0x3852
ole32.dll!WriteClassStm+0x374e
VBE6.DLL!rtcShell+0x25cc8
VBE6.DLL!rtcShell+0x24f3c
VBE6.DLL!rtcShell+0x254cc
VBE6.DLL!rtcShell+0x27ff7
VBE6.DLL!rtcShell+0x27f9b

 

[Bill Renaud]

Tom Med wrote:
<<This catches the event of saving a worksheet.>>

You mean "This catches the event of saving a WORKBOOK."?

Do you really need to capture WB.Name in a local variable, then Set WB =
Application.Workbooks(wbName) 2 lines down? WB was passed in ByVal in the
first place.

Also, take out the lines:

WB.Save
Cancel = True

and let Excel go ahead and save the workbook. I think your WB.Save
statement is creating an endless recursive call to this same event handler.

I haven't worked with event handlers at the Application level, so can't
really suggest anything more specific.