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

 

[RichardMGreen]

You would need to check what the user has pressed. You can check for vbYes and vbNo (literally) but your would need to put the results of the messagebox into a variable (let's call it response).

The you can say:-
if response=vbyes then
save the file anyway
else
exit sub
endif

Untested, but it should point you in the right direction.

 

[dinochristou]

Thanks for the advise.

Still struggling with this bit, anyone got any code to throw at me to try please?

Again this is the code I have so far (part of the longer code at beginning of the post)

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

End If

What I need to do is when I click yes is to send the kill command to delete the file that already exists which would be

Kill (strSaveAs & ".xls")

And then to carry on with the rest of the Sub.

When I click no I want to simply

Exit Sub

I just cant seem to get it all working together.
PLEASE HELP

 

[dinochristou]

Just a comment so that this post pops to the top of the forum.

Please HeLp

 

[RichardMGreen]

If you're only overwriting the current file, the you should be able to turn alerts off (application.displayalerts=false), save the file and then turn alerts back on (application.displayalerts=true). You don't need to delete the file to overwrite it.

 

[dinochristou]

Hi Richard,

Yeah I tried that also but seems to fall over when the file exists.
Not sure if its because I'm doing a save as and applying a readonly password.

I spent hours messing about with the code yesterday and gave up. But its a fresh new day so maybe I will get some new ideas.

 

[RichardMGreen]

A SaveAs should just tell you that the file already exists and ask if you want to over-write it.
Can you post your entire macro and highlight where it falls over if the file exists?

 

[dinochristou]

Hi Richard,

I think I may have got it working now, using what I call "Dirty VBA", but the code is....before the fix is....Its not falling over on the other macros it calls, just the code highlighted in red and only when there is another file with the same name there:

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

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strSaveAs, Password:="", WriteResPassword:="Venus"
'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

However, I did get the message box using this method...

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 & ".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 0

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

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

0
End Sub

 

[RichardMGreen]

On your first macro, you're not specifying a file extension for the save which could be causing problems.

The second macro looks fine though.

 

[dinochristou]

On your first macro, you're not specifying a file extension for the save which could be causing problems.

Sorry I think I hade copy and paste issues of my own fault there, its the same as the 2nd one but without the msgBox section etc.

I'm not tweaking it more so that an input box appears allowing you to specify a date if required and tagging the word 'revised' in the file name.

Hopefully I will get there by lunch and will re-post the code incase anyone else would like to use it or if anyone has better methods of suggestion.

Thanks for your help Richard.
Much appreciated.

Cheers
Dino

 

[RichardMGreen]

No problem and I hope you get it sorted.