GetSaveAsFileName Question

Bob.Carter.17 · #1 (**permalink**) 05-13-09, 12:50

All,
I have a brainfreeze here on this on. I have a template file that when I click a macro button and it runs all the commands successfully, at the end I want it to run the GetSaveAsFileName command. It should open the Save As box, no specific location as the save to (user can choose) and be set to Excel files *.xls. I can get it to work in the code, but I want it to pull information from 2 cells in determining the suggested file name. The location number is stored in cell P3 and the Month/Year is stored in cell P4. I want the suggested file name to be "Trailer Accountability Log - Location (cell P3) - Period (cell P4). I have tried several methods but have not found the magic formula yet, can anyone help me with this one?

Thanks!
Bob

Colin Legg · #2 (**permalink**) 05-13-09, 16:07

Hi Bob,

Something like this:

Code:

Sub foo()

    Const sLOG As String = "Trailer Accountability Log - "
    Const sFILEFILTER As String = "Excel files (*.xl*),*.xl*"
    
    Dim sInitialFileName As String
    Dim v
    
    With Sheet1
        sInitialFileName = sLOG & .Range("P3").Value & _
                        " - " & Format$(.Range("P4").Value, "yyyy mm dd")
    End With
    
    v = Application.GetSaveAsFilename _
                    (InitialFileName:=sInitialFileName, _
                    filefilter:=sFILEFILTER)
                    
    'etc...
    
                    

End Sub

Adjust the sheet's codename and the text as required....

Hope that helps...

Bob.Carter.17 · #3 (**permalink**) 05-13-09, 20:28

Worked out great, thanks for the help!

Colin Legg · #4 (**permalink**) 05-14-09, 15:47

You're welcome!

Bob.Carter.17 · #5 (**permalink**) 05-26-09, 13:23

Well.................

I thought it worked great. It opens the save as box, and sets up the "save as" to Excel *.xls files and creates the name I want in the file name box, but once you select your file location and click save, it acts as though it is saving it, but then there is no saved copy in the location or anywhere I can find. I tried some variations but all with the same result.

Any suggestions?

Colin Legg · #6 (**permalink**) 05-26-09, 14:08

Hi Bob,

The GetSaveAsFilename method doesn't actually save the file. It simply provides a dialogue box interface so the user can interact with your application and choose where to save the file. From the helpfile:

Quote:

Originally Posted by Excel VBA Helpfile

GetSaveAsFilename Method

Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

Once the user has either specified a file path/name OR pressed the cancel button, you need to check what the method returns:

Quote:

Originally Posted by Excel VBA Helpfile

Remarks
This method returns the selected file name or the name entered by the user. The returned name may include a path specification. Returns False if the user cancels the dialog box.

This method may change the current drive or folder.

Then, if the user did not cancel, use the workbook object's SaveAs method to save the file.

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech