Solved: Application.GetSaveAsFilename question

[k13r4n]

Morning all

I was just wondering how i go about making the GetSaveAsFilename method to ask if you want to overwrite a file that already exists becuase all it does is return a string containing a filename.

So im asuming that i need to have some code that runs after the user clicks save, that checks if the file already exists, and then, if it does, brings up a msgbox to ask if they want to overright . If they click no, then it brings the 'save as' window back up.

Does this sound about right? If so how do i go about checking if a file exsists?

Or am i way off the mark here?

cheers :beerchug:


Kieran

[lucas]

http://www.vbaexpress.com/forum/showthread.php?t=22581

[k13r4n]

thanks for your reply lucas, i read throught the thread but i cant see an answer to my question.

Can you point me to the bit you were trying to show me?


cheers


kieran

[Bob Phillips]

Dim ans As String
Dim Filename As Variant

Do

Filename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xl")
If Filename <> False Then

If Dir(Filename) <> "" Then

ans = MsgBox("Overwrite " & Filename & "?", vbYesNo, "Save file")
If ans = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename
Application.DisplayAlerts = True
Exit Do
End If
Else

ActiveWorkbook.SaveAs Filename
End If
End If
Loop Until Filename = False

[Artik]

My procedure is not likely to be optimal, but it works.:confused2 Sub AAA()
Dim newFname As Variant
Dim OK As Boolean

Do
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If newFname = False Then
Exit Sub
ElseIf Dir(newFname) = "" Then
ThisWorkbook.SaveAs newFname
OK = True
Else
If MsgBox("File already exists!" & vbCr & _
"Overwrite?", vbExclamation + vbYesNo, "Save As") = vbYes Then
On Error Resume Next
Kill newFname
If Err = 0 Then
ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If
OK = True
On Error GoTo 0
End If
End If
Loop Until OK
End Sub
Artik

PS
xld was faster.

..::Edit
This piece of xld's code is better: Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename
Application.DisplayAlerts = True
:bow:

[k13r4n]

actully i just worked out my own way using Dir() haha, and then just refreshed the thread and saw you guys had all replyed :cool:

Also i forgot to mention im not saving an excel file (my bad, sorry)
im just getting the filename/save location for the textfile that is created based on information on a spreadsheet.

anyway here is my solution (probably horribly structured)

Function BrowseForFolder() As Variant

Dim Filter
Dim FilterIndex
Dim Title
Dim OverRight

Filter = "Script Files (*.scr),*.scr,"
FilterIndex = 1
Title = "Save script File"

On Error Resume Next

GetFileName:

BrowseForFolder = Application.GetSaveAsFilename("", Filter, FilterIndex, Title)

If BrowseForFolder = False Then
GoTo IfFalse
End If

If Dir(BrowseForFolder) = "" Then
GoTo IfFalse
End If

If Dir(BrowseForFolder) <> "" Then
OverRight = MsgBox("File aready exists, Overwrite it?", vbYesNo + vbExclamation, "Overwrite File?")
End If

If OverRight = vbYes Then
GoTo IfFalse
Else
GoTo GetFileName
End If

IfFalse:

End Function
Like i said probably really bad...... but it works

thankyou for your replys


cheers


Kieran