Error when file to save is already opened.

[ATan]

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]

have you try this before your saveas code

[VBA]
application.displayalerts = false
[/VBA]

[ATan]

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]

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:

[vba]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[/vba]

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