VBA Code Help - Check file exists returns incorrect result.

[dinochristou]

Good Morning All,

I have some code (below) that is returning the incorrect result when it comes to check if the file already exists.

I know its there but the check returns the result msgbox "This file does not exist."

Can anyone shed some light as to why it thinks it does not exist?
Just to mention I also tried a length result check on the same file but still does not think it exists.

Sub SaveAs_PasteVal_Del_Email()

Dim strFileName As String
Dim iDate As String
Dim strSaveAs As String

strFileLoc = "\\BOFFIL01.Group.Net\lv26129\Test\"
'Where file will be saved

iDate = "Half Hour SL - " & Right(Date, 2) & Mid(Date, 4, 2) & Left(Date, 2)
'What the saved file will be called - issueDate

strSaveAs = strFileLoc & iDate
'full location and name of file

If Dir(strSaveAs) <> "" Then
MsgBox "This file already exists"
'& vbNewLine & "Would you like to overwrite the file?", vbYesNo, "File Exists"
Else
MsgBox "This file does not exist."
End If


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strSaveAs, Password:="", WriteResPassword:="Password"
'Saves current active workbook in the specified location with specified name with read/write password

Call Paste_Values
'Runs PasteValues Macro

Sheets(Array("Pivots 1", "Pivots 2")).Select
ActiveWindow.SelectedSheets.Delete
'Above worksheets will be deleted

Call Hide_Sheets
'Runs Hide_Sheets macro

Call Mail_Link
'Runs Mail_Link macro

ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.Quit

End Sub

 

[dinochristou]

Well I now have it working to point that I am happy with (in terms of function), but I am sure there is a much clearner approach as I class my VBA knowledge as very much beginner.

Here is my code, please feel free to comment:

Sub SaveAs_PasteVal_Del_Email()
Dim strFileName As String
Dim iDate As String
Dim strSaveAs As String
Dim myDate As Date
Dim myFDate As String
Dim strRSaveAs As String

strFileLoc = "\\BOFFIL01.Group.Net\lv26129\Test\"
'Where file will be saved

iDate = "Half Hour SL - " & Right(Date, 2) & Mid(Date, 4, 2) & Left(Date, 2)
'What the saved file will be called - issueDate

strSaveAs = strFileLoc & iDate
'full location and name of file

If Dir(strSaveAs & ".xls") <> "" Then
MsgBox1 = MsgBox("The file name already exists" & vbNewLine & "Do you want to overwrite it?", vbWarning + vbYesNo, "File Already Exists")
Else
GoTo 2
End If

If MsgBox1 = vbYes Then GoTo 1
If MsgBox1 = vbNo Then GoTo 3

1
Application.DisplayAlerts = False
Kill (strSaveAs & ".xls")

2
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strSaveAs, Password:="", WriteResPassword:="Venus"
'Saves current active workbook in the specified location with specified name with read/write password

99
Call Paste_Values
'Runs PasteValues Macro

Sheets(Array("Pivots 1", "Pivots 2")).Select
ActiveWindow.SelectedSheets.Delete
'Above worksheets will be deleted

Call Hide_Sheets
'Runs Hide_Sheets macro

Call Mail_Link
'Runs Mail_Link macro

ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
GoTo 0
3
myDate = Application.InputBox("Enter a relevant date for the file." & vbNewLine & vbNewLine & "NOTE: File will be saved as" & vbNewLine & "'Half Hour SL - yymmdd - revised.xls" & vbNewLine & "Any file with same name will be replaced", "File Date", FormatDateTime(Date, vbShortDate))
myDate = Format(myDate, "dd/mm/yyyy")
If myDate = False Then GoTo 0

myFDate = "Half Hour SL - " & Right(myDate, 2) & Mid(myDate, 4, 2) & Left(myDate, 2) & " - revised"
'Name and format of revised file name

strRSaveAs = strFileLoc & myFDate
'Revised full location and name of file

Application.DisplayAlerts = False
If Dir(strRSaveAs & ".xls") <> "" Then Kill (strRSaveAs & ".xls")
ActiveWorkbook.SaveAs strRSaveAs, Password:="", WriteResPassword:="Venus"
'Saves current active workbook in the specified location with specified name with read/write password

GoTo 99

0
End Sub