Open save as dialog and populate filename from cells 2

[thomasks]

I want to have the save as dialog open up with the filename populated with a filename built from the contents of several cells, and a default path. Should I put this in the beforesave event? or the beforeclose event? This is the code that I was trying to use, and not having the best results.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ThisFile As String
    ThisFile = Worksheets("Pricing Sheet").Range("K10").Value _
    & " " & Worksheets("Pricing Sheet").Range("K11").Value & " " _
    & Worksheets("Pricing Sheet").Range("K12").Value & " " & "estwksht" & ".xls"
Application.Dialogs(xlDialogSaveAs).Show
Application.DefaultFilePath = "T:\Quot"
fileSaveName = Application.GetSaveAsFilename(ThisFile, filefilter:="Excel Files (*.xls), *.xls")
Application.ActiveWorkbook.SaveAs Filename:=fileSaveName

End Sub

 

[robcarr]

why do you need to bring up the box to save as, you can just get it to save as

like

activeworkbook.saveas filesavename

rob.

Hope this is of use, Rob.

 

[thomasks]

I need the file dialog so the user can choose the folder to store the file in. Each quote worksheet is stored under a folder on the "T" drive by project number. I just wanted to name the file based on the project number, quote type, and version.
Such as: "106246 Base AA estwksht.xls" then let the user browse to the folder T:\Quote\106246\Estimating\d Controls\Base ver AA\ and save the file.
But not change the master worksheet that we use. It is "T:\Quot\CONTROLS\Formulas\Estworksheet\Master EST Worksheet v5.xls"
Does this explain it?

 

[Bong]

Why not use the GetSaveAsFilename method and specify the initialfilename parameter:

InitialFilename Optional Variant. Specifies the suggested file name. If this argument is omitted, Microsoft Excel uses the active workbook's name

_________________
Bob Rashkin

 

[thomasks]

That is what I am doing. Although when the user selects the save as command from the menu, and the code runs, I get a second save as dialog with the my documents selected as default, after I have run the code! How do I supress this? Here is the code I have.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
[COLOR=green]'Build string for file name using the cells from the worksheet that contain the project number, quote type, and version.[/color]

Dim ThisFile As String
    ThisFile = Worksheets("Pricing Sheet").Range("K10").Value _
    & " " & Worksheets("Pricing Sheet").Range("K11").Value & " " _
    & Worksheets("Pricing Sheet").Range("K12").Value & " " & "estwksht" & ".xls"

[COLOR=green]'Set the path to the T drive[/color]
Application.DefaultFilePath = "T:\Quot"

[COLOR=green]'Open the save as dialog with the filename built from the string[/color]
fileSaveName = Application.GetSaveAsFilename(ThisFile, filefilter:="Excel Files (*.xls), *.xls")

[COLOR=green]'If user has selected the filename and folder to store the new worksheet, then save it.[/color]
If fileSaveName <> "False" Then
    Application.ActiveWorkbook.SaveAs Filename:=fileSaveName
    Else
    Exit Sub
End If

[COLOR=green]'If user cancels, then exit the subroutine[/color]
If vbCancel = 2 Then
    Exit Sub
End If

End Sub

 

[PHV]

You may try this:
Application.EnableEvents = False
Application.ActiveWorkbook.SaveAs Filename:=fileSaveName
Application.EnableEvents = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thomasks]

The dialog seems to be working, I have it in the "beforesave" event. However after you save the file, you get a second dialog for save as. I think this is being generated by excel when you first select "Save as" from the file menu. How can I only get (1) save as dialog and supress the second one with the code I am using to load the file name, and default folder?

 

[PHV]

You may try this:
Application.EnableEvents = False
Application.ActiveWorkbook.SaveAs Filename:=fileSaveName
Application.EnableEvents = True
Cancel = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rfvedcwsxqaz]

This is really interesting, I am new to vba and I want to be able to adapt this save as dialog to a form that I have made.

I need this to function When the user clicks browse on the form.