GetSaveAsFilename - Save as box

[Der]

Hi
I have a following problem. I have a document with macro from which
open and convert other csv files. At the end of conversion I want t
get Save as box. To do this I wrote the following code:

NameAk = Sheets(1).Name & ".xls"
NewName
Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Path
"\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")

If NewName <> False Then
ActiveWorkbook.SaveAs Filename:=NewName
FileFormat:=xlWorkbookNormal
End If

Everything is all right if I push "save" or "cancel" button. Proble
occurs when I push "save" and there is another file with the same nam
in this directory. The second box occures and I got the message:"Do yo
want to replace the file?" If I answer yes then it is ok but if I answe
cancel or no then the macro crashes and the the message I get is
"method SaveAs or object _Woorkbook failed". Can you advise me how
should modify my code

 

[Harald Staff]

Hi

What do you want to happen if the file exists ?

Best wishes Harald

 

[Der]

Hi
when th second box occures I want:

1. OK- overwrite the existing file
2. Cancel- exit and do not save
3. No- get the message box "If you do not want to overwrite existin
file please give a different name for the new file" and hen come bac
to Save as box

 

[Harald Staff]

Gotcha. The trick is to ask before the lady asks herself:

Sub test()
Dim NameAk As String
Dim NewName As Variant

NameAk = Sheets(1).Name & ".xls"
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")

If NewName <> False Then
If Dir(NewName) <> "" Then
Select Case MsgBox("File Exists. Overwrite ?", vbYesNoCancel +
vbQuestion)
Case vbYes
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Case vbNo
Do
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & _
NameAk, FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName = False Then Exit Sub
Loop Until Dir(NewName) = ""
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Case Else
Exit Sub
End Select
Else
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
End If
End If
End Sub

HTH. Best wishes Harald

 

[Der]

It works

Thank you!!

 

[Harald Staff]

Glad to hear that. Thanks for the feedback.

Best wishes Harald