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:=Act iveWorkbook.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

--
Message posted from http://www.ExcelForum.com

Hi

What do you want to happen if the file exists ?

Best wishes Harald

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

NameAk = Sheets(1).Name & ".xls"
NewName =
Application.GetSaveAsFilename(InitialFileName:=Act iveWorkbook.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. Problem
occurs when I push "save" and there is another file with the same name
in this directory. The second box occures and I got the message:"Do you
want to replace the file?" If I answer yes then it is ok but if I answer
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 I
should modify my code?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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 " skrev i melding
...
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 existing
file please give a different name for the new file" and hen come back
to Save as box.


---
Message posted from http://www.ExcelForum.com/

It works

Thank you!!

--
Message posted from http://www.ExcelForum.com

Glad to hear that. Thanks for the feedback.

Best wishes Harald

"Der " skrev i melding
...
It works

Thank you!!!