A
Anthony
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
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