Re: overwrite throwing error
Try
Code:
If sfilename <> String.Empty Then
oBook.SaveAs sfilename
End If
Re: overwrite throwing error
well had to turn it into
Code:
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)
Re: overwrite throwing error
Try one of these:
Code:
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
Code:
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
Re: overwrite throwing error
interesting thank you anhn. I will try these on monday