Error When using ActiveWorkBook.SaveAs and EnableEvents in same macro
 

I am trying to save an Excel workbook in Excel 2003 SP2 via VBA using
the ActiveWorkbook.SaveAs command, however I get the follwoing error:

Microsoft Office Excel cannot access the file 'C:\Documents and
Settings\user\Desktop'. There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a
currently open workbook.

When I step through the code, the error occurs on the
"ActiveWorkbook.SaveAs" line. After a lot of testing and trial and
error, I have found that the error occurs when
Application.EnableEvents is somewhere in the code. Even if I put
Application.EnableEvents right at the end of the macro, the error will
still occur at the SaveAs line even though EnableEvents havent been
switched on or off?

Below is my code residing in the ThisWorkBook module in VBA. Even if I
remove the first "Application.EnableEvents = False" the error will
still occur at "ActiveWorkbook.SaveAs" because the
"Application.EnableEvents = True" is still in the macro at the end? If
i remove all EnableEvents lines, then the macro works fine?

This problem has only occurred on 2 of our cients.

I need to turn EnableEvents off so I can save the file via VBA without
running the Workbook_BeforeSave again.

Any ideas on what could be causing this problem?

Regards
Anthony

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

[Run some other code before a file gets saved..................]

Dim fname As Variant
fname = Application.GetSaveAsFilename(FileFilter:="Excel
(*.xls),*.xls", Title:="Save File")

Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveWorkbook.SaveAs fname

Application.DisplayAlerts = True
Application.EnableEvents = True

[Run some other code after a file gets saved....................]

End Sub

Error When using ActiveWorkBook.SaveAs and EnableEvents in same ma
 

Because you say that the problem occurs with only 2 clients, one assumes that
it could be their xl setup. Also you don't say if you are setting a path for
the save as.

Check if there is a difference in the Default File Location between those
that don't work and those that do work. You may have to reset it on those
that don't work.

To check Default File Location:- XL 2002 Tools-Options-General.

You could also try opening Excel fresh and copy the following macro and run
it and see what it returns.

Sub Test_Path()
MsgBox "The current file path is " & _
Application.DefaultFilePath
End Sub

I am only clutching at straws for this so it might or might not help.

Regards,

OssieMac


"Anthony" wrote:

I am trying to save an Excel workbook in Excel 2003 SP2 via VBA using
the ActiveWorkbook.SaveAs command, however I get the follwoing error:

Microsoft Office Excel cannot access the file 'C:\Documents and
Settings\user\Desktop'. There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a
currently open workbook.

When I step through the code, the error occurs on the
"ActiveWorkbook.SaveAs" line. After a lot of testing and trial and
error, I have found that the error occurs when
Application.EnableEvents is somewhere in the code. Even if I put
Application.EnableEvents right at the end of the macro, the error will
still occur at the SaveAs line even though EnableEvents havent been
switched on or off?

Below is my code residing in the ThisWorkBook module in VBA. Even if I
remove the first "Application.EnableEvents = False" the error will
still occur at "ActiveWorkbook.SaveAs" because the
"Application.EnableEvents = True" is still in the macro at the end? If
i remove all EnableEvents lines, then the macro works fine?

This problem has only occurred on 2 of our cients.

I need to turn EnableEvents off so I can save the file via VBA without
running the Workbook_BeforeSave again.

Any ideas on what could be causing this problem?

Regards
Anthony

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

[Run some other code before a file gets saved..................]

Dim fname As Variant
fname = Application.GetSaveAsFilename(FileFilter:="Excel
(*.xls),*.xls", Title:="Save File")

Application.DisplayAlerts = False
Application.EnableEvents = False

ActiveWorkbook.SaveAs fname

Application.DisplayAlerts = True
Application.EnableEvents = True

[Run some other code after a file gets saved....................]

End Sub