VBA Code Help - Check file exists returns incorrect result.

dinochristou

New Member
Joined
Sep 7, 2011
Messages
34
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
The file doesnt seem to be on your computer. right?

Is the same path displayed when you go to the folder via explorer?
 
Upvote 0
Thanks for the quick response.

It exists as I created it earlier.
If I let the code carry on running it debugs at the 'saveas' section.

Likewise, if I delete the file manually and run the code again, it saves the file fine with no debug errors.
 
Upvote 0
There is some misunderstanding. If the file exists there then obviously you will get an error saving at the location.
 
Upvote 0
Sorry, to be clearer as I dont thin I explained properly, the section that does the checking (below), always displays MsgBox "This file does not exist." even if the file does exist.

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
 
Upvote 0
Have you stepped through the code and checked the value of strSaveAs on this line:-
If Dir(strSaveAs) <> "" Then
 
Upvote 0
Hi Richard,

I looked at the watch value and just noticed (annoyingly I didnt see this earlier), that ".xls" was not part of the result, hence the file does not exist.

Have ammended the code now and it has found it.

Thanks for you help :)

Regards
Dino
 
Upvote 0
I do have a spin off question to this code also.

I want, when the check bit comes in, to display a msgbox stating "The file already exists, do wish to overwrite this?" with a vb YesNo button.

I have go to this stage but not sure how to handle the statement as the first IF statement is the file check.

Please can you or anyone advise?

Code I have so far is:

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

All I want to do is Kill (strSaveAs & ".xls") when yes is selected
Else Exit Sub when No is selected.

Thanks in advance.

Dino
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top