getsaveasfilename loop... <> false and not already exists

[goaljohnbill]

I would like to know if there is a way to turn this:

Sub saveas()
Workbooks.Open Filename:= _
"C:\Toxo QNS temp.xls"
Do
fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _
fileFilter:="Excel Files
(*.xls), *.xls")
Loop Until fname <> False
If Dir(fname) <> "" Then
MsgBox "You can't overwrite an existing file try again"
Windows("Toxo QNS temp.xls").Close
Call saveas

into a working version of this:

Workbooks.Open Filename:= _
"C:\Toxo QNS temp.xls"
Do
fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _
fileFilter:="Excel Files
(*.xls), *.xls")
Loop Until (fname <> False) And (Dir(fname) Is "")

The second code gives a type mismatch at the "(Dir(fname) Is "")"

The goal being to stop accidental overwrites and not have to restart
the macro to do it, so that i can drop the altered loop until snippet
into all of the places it would be good to have. If there isnt a handy
way to do it, I am fine with that i would just like to know so i stop
trying to figure it out. Thank you in advance for any responses

john

 

[Joel]

Try this code

Sub saveas()

Workbooks.Open Filename:= _
"C:\Toxo QNS temp.xls"
Do
fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _
fileFilter:="Excel Files(*.xls), *.xls")

If fname <> False Then
If Dir(fname) <> "" Then
MsgBox "You can't overwrite an existing file try again"
fname = False
End If
End If
Loop Until fname <> False

Windows("Toxo QNS temp.xls").Close
Call saveas

End Sub

 

[goaljohnbill]

Worked great! thanks. It never occured to me to put ifs inside the do
loop. Everytime I learn 1 thing i end up wondering how to do 3 more.
I did take out the msgbox, temp close and call since that was the
stuff i wanted to do away with to begin with. The if part will go very
easily into other Do getsaveasfilename loops to give the same
functionality.

Thanks again
john