Check if file exists if so save as new version where filename is a reference from within sheet

0
votes

Im trying to;

1) Check if a file exists 2) If it does the save new version or if it doesn't exist then create file

The code i have is as follows:

FPath = "\\DLINK-22FD41\Metro Properties\Craig\Vivien Kondor"
FName = Range("e6").Text
FName2 = Range("e6").Text & "_v"

myfile = FPath & "\" & FName
TestStr = ""
On Error Resume Next
TestStr = Dir(myfile, vbNormal)
On Error GoTo 0
If TestStr = "" Then
    'MsgBox "File doesn't exist"
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    activewindow.close true
Else
    'MsgBox "File exists"
    VersionExt = InputBox("Enter Invoice Version")
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName2 & VersionExt, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close True
End If

The error checking works if i type the full filepath in but the filename (FName) is generated earlier in the macro

PLEASE HELP

1
excelvba

1 Answers

0
votes

Add a reference to Microsoft Scripting Runtime and you can use the FileSystemObject to check if the file exists.

Dim fso As FileSystemObject
set fso = New FileSystemObject
If fso.FileExists(myfile) Then 
     'do something
End If