overwrite throwing error

[pgag45]

I'm condensing some of my code here, but I'm getting an error I'm not really sure how to handle, even with the simplicity of what I'm trying to achieve.

Basically when a user selects to name a file that already exists, MS pops up a overwrite file msgbox. but if in that overwrite msgbox the user selects no or cancel, run time error is thrown. I don't really feel like I have control of what is happening as this appears to be contained all within the .saveAs command


sfilename is a string

sfilename = Application.GetSaveAsFilename("nindex_", "Excel files (*.xls), *.xls")
If sfilename = "False" Then Exit Sub
Application.ScreenUpdating = False
Dim oBook As Workbook
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = 1
oBook.SaveAs sfilename 'crashing here if the user selects no/cancel overwrite

I tried throwing some checks in before the .saveAs, but as I said, I kind of lose control to MS. Don't know if I'm explaining this well, but any help would be much appreciated! thanks!

go steelers.

[Hack]

Try

If sfilename <> String.Empty Then
    oBook.SaveAs sfilename
End If

[pgag45]

well had to turn it into

If sfilename <> "" Then
    oBook.SaveAs sfilename
End If

to even get it to complile... but that doesn't work. At that time sfilename is actually equal to what they initially chose to overwrite (the complete path).

I did get it to not crash w/ error resume next... but I like to avoid that style, and don't really even know what happens with that in there (if it actually does overwrite it or not)

[anhn]

Try one of these:

Sub CreateAndSaveNewWB()
    Dim fName As Variant
    Dim oBook As Workbook
    Dim n As Integer
 
    fName = Application.GetSaveAsFilename
    If fName = False Then Exit Sub
    
    n = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oBook = Workbooks.Add
    Application.SheetsInNewWorkbook = n
        
    oBook.Saved = False
    
    On Error Resume Next
    oBook.SaveAs Filename:=fName
    On Error GoTo 0
    
    If oBook.Saved Then
        MsgBox "New workbook has been saved as " & fName
    Else
        oBook.Close False
        MsgBox "New workbook has not been saved"
    End If
End Sub

Sub CreateAndForceSaveNewWB()
    Dim fName As Variant
    Dim oBook As Workbook
    Dim n As Integer
    
    n = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oBook = Workbooks.Add
    Application.SheetsInNewWorkbook = n
    
    '-- force to save until success
    On Error Resume Next
    Do
        fName = Application.GetSaveAsFilename
        If fName <> False Then
            oBook.SaveAs Filename:=fName
            If Err = 0 Then Exit Do
            Err.Clear
        End If
    Loop
    On Error GoTo 0
    MsgBox "New workbook has been saved as " & fName
End Sub

[pgag45]

interesting thank you anhn. I will try these on monday