Auto overwrite when saving to open file with same name

[pingme89]

I have the following code to create and save a new workbook. If there is a file that is already open by the same name, I get an error.
Is there a way I can force it to close the open file and automatically overwrite the previous file with the same name?

Where DirPath is string indicating the path of the file and WBname is the name of the file.

ActiveWorkbook.SaveAs Filename:=DirPath & WBName & ".xlsx", FileFormat:=51

 

[tonyyy]

You might give this a try...

Sub test2()
    Application.DisplayAlerts = False
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(WBName & ".xlsx")
    If wb Is Nothing Then
        On Error GoTo zero
    Else
        Workbooks(WBName & ".xlsx").Close SaveChanges:=False
    End If
    ActiveWorkbook.SaveAs FileName:=DirPath & WBName & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
End Sub

The construct If wb Is Nothing... tests if the WBName workbook is open, and if so, closes it.

The Application.DisplayAlerts = False function suppresses any popup dialogs.

Of course you'll have to insert your own code that gives WBName it's value.

Cheers,

tonyyy

 

[pingme89]

DIM nwb as workbook
DirPath and WBName are strings
When I use the following line, I get a "Type Mismatch" error
Set nwb = DirPath & WBName & ".xlsx"


nwb = DirPath & WBName & ".xlsx"
If nwb Is Nothing Then
On Error GoTo zero <---- THIS LINE gets a LABEL NOT DEFINED COMPILE ERROR
Else
Workbooks(WBName & ".xlsx").Close SaveChanges:=False
End If
ActiveWorkbook.SaveAs filename:=DirPath & WBName & ".xlsx", FileFormat:=51
Application.DisplayAlerts = True




I took out the word "Set" and the error went away.

However, I get another error.

 

[GTO]

Hi there,

You will probably want to consider any downsides, but to simply close (with or without saving) a workbook with the same name, this should work.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> exammple()<br><br><SPAN style="color:#00007F">Const</SPAN> WBName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Book1.xlsx"<br><SPAN style="color:#00007F">Const</SPAN> Path <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G:\DesktopRestore\"<br><br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>  Application.Workbooks(WBName).Close SaveChanges:=<SPAN style="color:#00007F">False</SPAN><br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>  <br>  Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>  ActiveWorkbook.SaveAs Path & WBName, 51<br>  Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>  <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark