How to save a copy of an inactive worksheet - Excel 2003 vba

Question

We have a piece of software that allows the user to run a report. The template for this report is an Excel workbook that exists on a network drive. The steps are:

The user clicks "Generate Report" The software opens the template, populates the data, and closes the template, saving it in a specific directory. This is done "silently", that is, the sheet is never visible to the user.

My goal is to have a VBA module execute while the sheet is open, which will save an additional copy of the workbook in a separate directory. This is successful up to one line of code that doesn't seem to want to run:

ThisWorkbook.SaveAs (fileName)

(Where "fileName" is a string containing the correct directory. For some reason this does not save a copy of the workbook as I would expect. If I were to open the copy made by the program and run that code it works fine, but during the initial copy when the sheet is not active the SaveAs just doesn't do anything. This code is in a module that is called by the BeforeSave event.

Any thoughts as to why this isn't being run?

Answer 1

Do you know if the BeforeSave event is being triggered? I would write a temp file to the c drive or something to be sure the event is firing.

open "c:\eventcheck.txt" for append as #1
print #1, "event fired"
close #1
ThisWorkbook.SaveAs (fileName)