Workbooks.Add and SaveAs don't occur
 

Hi NG,

In my AddIn I monitor events at an application level (AppEvents As
Excel.Application) in order to trap all the events for certain workbooks.
This works fine.

Within the BeforeSave event, if the passed WB falls into the certain
category, I perform two operations of note before saving the workbook:

(1) - Use wb.SaveCopyAs to create a backup copy. (wb.SaveCopyAs PATH_BACKUP
& "\" & myBackUpFileName)
(2) - Create a "copy for publishing", this uses Workbooks.Add to create a
new workbook, populate it with some data, save it elsewhere.

All of this works very nicely, seamlessly and invisibly under normal
circumstances. Today however, I have a new sub in my AddIn which I don't
want to be run unless the user has saved the workbook so I check this and
then, if the user wishes, save it for them before continuing.
code snippet:
If Not ActiveWorkbook.Saved = True Then
If MsgBox("You cannot perform this if the workbook isn't saved." &
vbCrLf _
& "Do you wish to save and continue?", vbOKCancel + vbQuestion)
= vbOK Then
ActiveWorkbook.Save
Else
Exit Sub
End If
End If

The ActiveWorkbook.Save fires my BeforeSave event that I've trapped - as one
would expect - BUT... the SaveCopyAs code doesn't work. I can just F8 over
the line - there's no error raised (err.number = 0) , it's that no copy is
saved.

Likewise when I create the copy for the publishing I use "Set wbNew =
Workbooks.Add" and, instead of creating a new workbook, it just returns the
activeworkbook as wbNew - which rather messes things up.

Obviously the easiest solution is to just warn the user the activeworkbook
isn't saved and exit the procedure rather than offer to save it for them but
this is not what I want to do... :-(

Has anyone seen this sort of behaviour before? All thoughts gratefully
received

Thanks,
Gareth



Excel 2000 SP-3
Win2000