none
write a macro to prompt for a filename and location for saving as excel RRS feed

  • Question

  • Hello, 

    Is it possible to write a Macro to save the file as excel, :

    1. Run the Macro to get the prompt the for the name and location of the file.

    2. Once the file name/location is specified, export the file as excel and bypass the export wizard. (using a pre-saved map that already exists)

    I just want to prompt for location and then bypass the rest of the process, I am trying to automate the process and to minimize the human error of running the wizard over and over again by the resources who will use the file. I want the "end users" to only specify the location and be done with it and close the file. 

    I really do appreciate your help. Regards.


    • Edited by SB099 Monday, December 3, 2012 4:46 PM
    Monday, December 3, 2012 4:45 PM

Answers

  • Project doesn't have a File browse command so you have 2 choices:

    1. Have a reference to Excel and:
    Sub Test()
    Dim xlapp As New Excel.Application
    Dim Path As String
        Path = xlapp.GetSaveAsFilename("Excel Files (*.xlsx), *.xlsx")
        xlapp.Quit
    End Sub

      2.  Use a Windows API. The code for Access works well as in:
           http://access.mvps.org/access/api/api0001.htm


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by SB099 Tuesday, December 4, 2012 7:18 PM
    Monday, December 3, 2012 7:17 PM
    Moderator

All replies

  • Project doesn't have a File browse command so you have 2 choices:

    1. Have a reference to Excel and:
    Sub Test()
    Dim xlapp As New Excel.Application
    Dim Path As String
        Path = xlapp.GetSaveAsFilename("Excel Files (*.xlsx), *.xlsx")
        xlapp.Quit
    End Sub

      2.  Use a Windows API. The code for Access works well as in:
           http://access.mvps.org/access/api/api0001.htm


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by SB099 Tuesday, December 4, 2012 7:18 PM
    Monday, December 3, 2012 7:17 PM
    Moderator
  • Thanks Rod.

    This worked :)

    Oh and BTW, big fan here, your book rocks !! 

    Tuesday, December 4, 2012 7:17 PM