SaveAs no question...

[okrob]

I have a file I save weekly to several different names. I open the
workbook read only and after some modifications. I then copy the
worksheet to a new book. Tell the new workbook it's new name, then
save it to the same location every time. I want to keep the yes / no /
cancel alert for existing workbooks, but would like to get rid of the
saveas dialog completely. In otherwords, if the file doesn't already
exist, save with the filename I've given it.
Been looking though here, but most everyone wants to get rid of the
yes/no/cancel alert...

Anyway, my code example (probably taken from here in the past couple of
years):

Sub sav()
month = Evaluate("IF(MONTH(C4)>9,MONTH(C4),0&MONTH(C4))")
day = Evaluate("IF(DAY(C4)>9,DAY(C4),0&DAY(C4))")
year = Evaluate("RIGHT(YEAR(C4),2)")
With Application
FN = .GetSaveAsFilename("S:\MYPATH\" & Range("B2").Value & "\"
& Range("B2").Value & " " & month & day & year & ".xls")
If FN <> False Then
ActiveWorkbook.ActiveSheet.Buttons.Delete
ActiveWorkbook.SaveAs FN <======== I'd like this to not
show the saveas dialog, just go ahead and save the darn thing with no
questions unless there's an existing workbook.
ActiveWorkbook.Close False
End If
End With
End Sub

 

[Corey]

See Below
I have a file I save weekly to several different names. I open the
workbook read only and after some modifications. I then copy the
worksheet to a new book. Tell the new workbook it's new name, then
save it to the same location every time. I want to keep the yes / no /
cancel alert for existing workbooks, but would like to get rid of the
saveas dialog completely. In otherwords, if the file doesn't already
exist, save with the filename I've given it.
Been looking though here, but most everyone wants to get rid of the
yes/no/cancel alert...

Anyway, my code example (probably taken from here in the past couple of
years):

Sub sav()
month = Evaluate("IF(MONTH(C4)>9,MONTH(C4),0&MONTH(C4))")
day = Evaluate("IF(DAY(C4)>9,DAY(C4),0&DAY(C4))")
year = Evaluate("RIGHT(YEAR(C4),2)")
With Application
FN = .GetSaveAsFilename("S:\MYPATH\" & Range("B2").Value & "\"
& Range("B2").Value & " " & month & day & year & ".xls")
If FN <> False Then
ActiveWorkbook.ActiveSheet.Buttons.Delete
Application.Displayalerts=False
ActiveWorkbook.SaveAs FN <======== I'd like this to not
show the saveas dialog, just go ahead and save the darn thing with no
questions unless there's an existing workbook.
ActiveWorkbook.Close False
End If
End With
Application.Displayalerts=True
End Sub


Corey....

 

[Dave Peterson]

So you want to stop showing the application.getsaveasfilename?

I _think_ that this does what you want:

Option Explicit
Sub sav()
Dim myDateStr As String
Dim FN As Variant
Dim resp As Long
Dim TestStr As String

With Worksheets(1)
myDateStr = Format(.Range("c4").Value, "mmddyy")
FN = "S:\MYPATH\" & .Range("B2").Value _
& "\" & .Range("B2").Value & " " & myDateStr & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(FN)
On Error GoTo 0

If TestStr = "" Then
'doesn't exist
resp = vbYes
Else
FN = Application.GetSaveAsFilename(InitialFileName:=FN, _
filefilter:="Excel files,*.xls")
If FN = False Then
'do nothing
resp = vbNo
Else
TestStr = ""
On Error Resume Next
TestStr = Dir(FN)
On Error GoTo 0

If TestStr = "" Then
'new chosen name doesn't exist
resp = vbYes
Else
resp _
= MsgBox(Prompt:="Wanna overwrite the existing file", _
Buttons:=vbYesNo)
End If
End If
End If

If resp = vbYes Then
.Buttons.Delete
Application.DisplayAlerts = False
On Error Resume Next
.Parent.SaveAs Filename:=FN, FileFormat:=xlWorkbookNormal
If Err.Number <> 0 Then
MsgBox "An error occurred:" _
& vbLf & Err.Number _
& vbLf & Err.Description
Err.Close
Else
'.Parent.Close savechanges:=False
End If
Application.DisplayAlerts = True
Else
MsgBox "Ok, not saved!"
End If
End With
End Sub

You may not want to use variables that are also functions in VBA.

Month(), day() and year() are all VBA functions.

 

[Tom Ogilvy]

Activeworkbook.SaveAs FN doesn't show the saveas dialog.
Application.GetSaveAsFilename shows the saveas dialog.

 

[okrob]

Tom,
Man, I shoulda used some msgboxs to figure this out on my own...
Oh well, nothing like someone pointing out the obvious.
Thanks.
Rob