Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open a folder macro (Excel XP)

    I want to set up a macro that will do nothing more then open the OPEN FILE window at a specific folder. I can't make this happen with the RECORD MACRO tool as it wants a file name entered to complete the task.

    Can you tell me how to pull this off?

    I've tried a number of things unsuccessfully.

    Thanks,
    BH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a folder macro (Excel XP)

    You can use code like this:

    Dim strFile As String
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel workbooks", "*.xls"
    .InitialFileName = "C:Excel"
    If .Show = True Then
    strFile = .SelectedItems(1)
    Else
    Exit Sub
    End If
    End With

    The variable strFile will contain the path and filename of the workbook selected by the user.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Open a folder macro (Excel XP)

    Hi,
    What are you actually trying to achieve? You can use the <code>Application.GetOpenFilename</code> method to return a file name for further processing. If you want to open a specified directory by default, you can change the drive and path first:
    <pre> ChDrive "C:"
    ChDir "C:Test"
    varfile = Application.GetOpenFilename()
    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open a folder macro (Excel XP)

    I will use this macro on my inovice form via a command button.

    I have both a quote form and an invoice form that I have created in Excel for my business. What's nice about the system is that I can open a previously created quote and copy/paste all the purchase information into the invoice. I have a command button on the quote form that automatically handles this for me.

    When I open the blank invoice form right now I have to press the OPEN FILE button, then work my way through the directory structure to the folder where all the quotes are stored. Not a big deal, but a OPEN QUOTE command button would save a couple clicks and a few seconds with every invoice I create from the quotes.

    Every invoice requires a different quote be opened, so I'm not after a specific file with the macro.........just the Quote folder so I can then search out the quote I need and open it.

    Hans, I've input your code and it opens the FILE OPEN window just fine. However, if I select a specific file and press OPEN, the OPEN FILE window closes but the quote doesn't open. Here is what I've done.........probably screwed up what you had in mind..........sorry.........



    Sub Get_Quote_File()
    '
    ' Get_Quote_File Macro
    ' Macro recorded 7/16/2007 by B.H. Davis
    '

    '
    Dim strFile As String
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel workbooks", "*.xls"
    .InitialFileName = "D:MS Office DocumentsQuotes"
    If .Show = True Then
    strFile = .SelectedItems(1)
    Else
    Exit Sub
    End If
    End With


    End Sub


    I tried replacing the "1" in the strFile= line with the path ...........D:MS Office DocumentsQuotes.....but that doesn't work.

    Thanks,
    BH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a folder macro (Excel XP)

    The FileDialog doesn't open the file, it only provides you with the name of the file selected by the user. It's up to you as programmer to open the file. Insert the following line above End Sub:

    Workbooks.Open strFile

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open a folder macro (Excel XP)

    Hans,

    I make no pretense at being a "programmer" !!!!!! With the much appreciated help of people like you I muddle through getting Excel to do the things I need.

    Thanks for your patience and help. The new command button is doing exactly what I wanted.

    Regards,
    BH

Posting Permissions

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