PDA

View Full Version : Error when file to save is already opened.



ATan
02-24-2009, 07:35 PM
I have this codes which help me to save 2 files, one as per the original filename and another with a date stamp. The codes will run fine as long as any of the file to be save is not opened. How can I change the codes to avoid this error. Preferably a message box to tell that the file is already opened and the macro will end.


Sub SaveFile()

Dim f As Variant
Dim strFileName As String
Dim strFileDirectory As String
Dim fname As String

fname = "ACR-WP8402"
strFileName = fname & " " & Format(Now(), "yyyy-mm-dd") & ".xls"
strFileDirectory = "I:\Final Cost Forecast-Infra\WP8402\"

'Show the SaveAs box
f = Application.GetSaveAsFilename(InitialFileName:=strFileDirectory & strFileName, _
FileFilter:="Microsoft Excel Workbook (*.xls),*.xls")

If f <> False Then
ActiveWorkbook.SaveAs strFileDirectory & strFileName
End If

ActiveWorkbook.SaveAs Filename:= _
"I:\Final Cost Forecast-Infra\WP8402\ACR-WP8402.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End Sub

chungtinhlak
02-24-2009, 08:08 PM
have you try this before your saveas code


application.displayalerts = false

ATan
02-24-2009, 08:12 PM
chungtinlak,

Tried that but the error message still appear and I am asked to end or debug the macro which is what I am trying to avoid.

GTO
02-24-2009, 08:30 PM
Greetings ATan,


...The codes will run fine as long as any of the file to be save is not opened...

I hope I'm not missing anything and not tested, but in a throwaway copy of your wb, try:

Sub SaveFile()
Dim f As Variant
Dim strFileName As String
Dim strFileDirectory As String
Dim fname As String
Dim wb As Workbook

fname = "ACR-WP8402"
strFileName = fname & " " & Format(Now(), "yyyy-mm-dd") & ".xls"

For Each wb In Workbooks
If wb.Name = strFileName Then
MsgBox "Workbook already open; cannot SaveAs.", vbCritical, ""
Exit Sub
End If
Next

strFileDirectory = "I:\Final Cost Forecast-Infra\WP8402\"

'Show the SaveAs box
f = Application.GetSaveAsFilename(InitialFileName:=strFileDirectory & strFileName, _
FileFilter:="Microsoft Excel Workbook (*.xls),*.xls")

If f <> False Then
ActiveWorkbook.SaveAs strFileDirectory & strFileName
End If

ActiveWorkbook.SaveAs Filename:= _
"I:\Final Cost Forecast-Infra\WP8402\ACR-WP8402.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

BTW, this seems familiar and I'm not sure if this question was posed before: Have you tried or looked at SaveCopyAs? I may be mis-reading, but if not, it would seem easier to just do a Save and a SaveCopyAs so that the original wb is still the one open. Just a thought of course.

Anyways, hope this helps,

Mark