PDA

View Full Version : Solved: Application.GetSaveAsFilename question



k13r4n
01-07-2009, 03:43 PM
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
01-07-2009, 03:46 PM
http://www.vbaexpress.com/forum/showthread.php?t=22581

k13r4n
01-07-2009, 04:31 PM
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
01-07-2009, 04:44 PM
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
01-07-2009, 04:46 PM
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
01-07-2009, 05:12 PM
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