×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Open save as dialog and populate filename from cells
2

Open save as dialog and populate filename from cells

Open save as dialog and populate filename from cells

(OP)
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.

CODE

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

RE: Open save as dialog and populate filename from cells

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.yoda

RE: Open save as dialog and populate filename from cells

(OP)
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?

RE: Open save as dialog and populate filename from cells

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

Quote:

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

_________________
Bob Rashkin

RE: Open save as dialog and populate filename from cells

(OP)
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.

CODE

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

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"

'Set the path to the T drive
Application.DefaultFilePath = "T:\Quot"

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

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

'If user cancels, then exit the subroutine
If vbCancel = 2 Then
    Exit Sub
End If

End Sub

RE: Open save as dialog and populate filename from cells

(OP)
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?

RE: Open save as dialog and populate filename from cells

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close