GetSaveAsFilename alwyas overwrites existing file

[jeff]

I am exporting a text file from within my macro, but it ALWAYS overwrites an
existing file without asking me if I want to replace it?

Anybody know what I did wrong?

FName = Application.GetSaveAsFilename("Rename_me.gft", filefilter:="GIFT
files _
(*.gft), *.gft,Text files, *.txt,All Files, *.*", _
Title:="Please pick a file name for your GIFT file")
If FName = False Then
myMessage = "You didn't select a file." & Chr(10) & Chr(10) & "Export
has been ABORTED!"
GoTo MyError:
End If

THanks

 

[Tim Williams]

GetSaveAsFilename only askes the user for a filename: it's up to you to
check if it exists and if so then ask the user what they want to do.

You don't show the code which does the save: post that and I'm sure you'll
get a suggestion on how to handle it.

Tim

 

[jeff]

Oops... forgot the save part of the macro!!!

OKay, thanks Tim... so I guess my question has now changed to "How do I
check to see if the file already exists?" Here's the whole chunk of code I
already have. There are no problems with it all, as far as the actual export
goes, it's just that it overwrites the existing file if it already exists.

'---Pick a filename and location to save---
FName = Application.GetSaveAsFilename("Rename_me.gft", _
filefilter:="GIFT files (*.gft), *.gft,Text files, *.txt,All Files,
*.*", _
Title:="Please pick a file name for your GIFT file")
If FName = False Then
myMessage = "You didn't select a file." & Chr(10) & _
Chr(10) & "Export has been ABORTED!"
GoTo MyError:
End If
'***Need code HERE to check if the file (FName) already exists!!!!***
'---Export To Text File---
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
Open FName For Output Access Write As #FNum
For q = 0 To 100
If qGIFT(q) <> "" Then Print #FNum, qGIFT(q)
Next q
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

 

[Tom Ogilvy]

'---Pick a filename and location to save---
FName = Application.GetSaveAsFilename("Rename_me.gft", _
filefilter:="GIFT files (*.gft), *.gft,Text files, *.txt,All Files,
*.*", _
Title:="Please pick a file name for your GIFT file")
If FName = False Then
myMessage = "You didn't select a file." & Chr(10) & _
Chr(10) & "Export has been ABORTED!"
GoTo MyError:
End If
if dir(fName) <> "" then
ans = Msgbox("File exists, overwrite?", vbYesNo)
if ans = vbNo then exit sub
end if
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
Open FName For Output Access Write As #FNum
For q = 0 To 100
If qGIFT(q) <> "" Then Print #FNum, qGIFT(q)
Next q
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

 

[jeff]

Thanks Tom!

It's a very simple solution, isn't it!!!!

if dir(FileName) <> "" then
ans = Msgbox("File exists, overwrite?", vbYesNo)
if ans = vbNo then exit sub
end if

I also found another example it the following post (almost the same question
asked at almost the same time)!!!

Subject: SaveFileName in Excel
3/10/2007 9:05 PM PST
By: TedT
In: microsoft.public.excel.programming

Thanks again!