Results 1 to 5 of 5

Thread: GetOpenFilename

  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetOpenFilename

    I have used this in the past for selecting single files, but now I would like to create a macro that allows selection of multiple file. The purpose is to create a summary workbook that derives it's data from sequentially opened workbooks that the user specifies in one GetOpenFilename dialog operation. I can't seem to get the options right after the method call and am uncertain how to code the handling of the resultant array. Below is the result of my floundering around. Any help would be appreciated.


    Sub GatherTimeSheet()
    ' Inhibit screen activity
    Application.ScreenUpdating = False
    FilesToOpen = Application.GetOpenFilename("Excel Files (*.xls)", "*.xls", , , "True")
    For Each n In FilesToOpen
    If FilesToOpen(n) <> False Then
    Workbooks.Open Filename:="FilesToOpen(n)"
    ' Insert code to perform copy action here
    End If
    Next
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename

    I can help with arrays, but not with the GetOpenFilename.

    On my Word97SR2 system, the method pops up in the Help files, but NOT when I use the period after Application. Odd.

    What version are you using?

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename

    Chris,

    Thanks for the input
    My version is Microsoft Excel 2000 (9.0.3821 SR-1)

    Does the code within the For Each loop look sound relative to the treatment of the FilesToOpen array?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename

    have you tried doing the same process with the Common Dialog control?
    i find the object.ShowOpen gives a lot better control over what the user can do

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename

    <pre>For Each n In FilesToOpen
    If FilesToOpen(n) <> False Then
    Workbooks.Open Filename:="FilesToOpen(n)"
    ' Insert code to perform copy action here
    End If
    Next
    </pre>


    Yes, although I gagged at your typo "Each loop look sound" - had trouble reading it out aloud! hah hah.


    geoff W is going to suggest that you make use of the pre and /pre tags. We'lll talk.


    After you have done your "copy action", shouldn't you be closing the file?


    I have a Files processor (which I've decided to re-write) that will run any process you can devise on any set of files on your system. You fire up the GUI, nominate file characteristics, and turn it loose. It looks for a macro "Process" which corresponds to the inner part of your lopp, and runs that macro on every instance of a file it can open. If it can't find a Process, it creates one for you.

    Available from my d/l page.

Posting Permissions

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