Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Question Unanswered: GetSaveAsFileName Question

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...
    Last edited by Colin Legg; 05-13-09 at 17:10.

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Worked out great, thanks for the help!

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    You're welcome!

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    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?

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •