VB Code question...
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VB Code question...

  1. #1
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I currently have a script set to open 2 specific workbooks and then prompt to choose the required 3rd.

    As it is, I can only select 1 when the 'open' window comes up. Is there any way I can make this so I am able to select more than one file at once?

    The code I currently have is as follows:
    Private Sub Workbook_Open()
    On Error GoTo myErr
    ChDir "(directory)"
    Workbooks.Open "(file.xls)"
    Workbooks.Open "(file.xls)"
    Which = Application.GetOpenFilename() 'prompts user to open 3rd

    Workbooks.Open Which
    End
    myErr:
    End Sub
    _________________
    "What am I, a magnet for these idiots?"
    --Pearl Forrester, MST3K


    [ This Message was edited by: Von Pookie on 2002-02-20 14:01 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    You just need to set the Multiselect argument to True e.g.

    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For l = 1 To UBound(Which)
    MsgBox Which(l)
    Next

    HTH,
    D

  3. #3
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This does let me select more than one file, however, it will not open them. The only thing that happens when I click 'open' is that I get a message box that tells me the files I selected.

    I tried deleting the "MsgBox Which(l)" line, however then it doesn't do anything...
    Kristy

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 07:40, Von Pookie wrote:
    This does let me select more than one file, however, it will not open them. The only thing that happens when I click 'open' is that I get a message box that tells me the files I selected.

    I tried deleting the "MsgBox Which(l)" line, however then it doesn't do anything...
    You may want to put:

    Workbooks.Open Which (l)

    where that "Msgbox Which(l)" was before.


  5. #5
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-21 09:15, Mark O'Brien wrote:
    You may want to put:

    Workbooks.Open Which (l)

    where that "Msgbox Which(l)" was before.
    Unfortunately, I can't get that to work either.

    This is what I'm working with now:
    Code:
    Private Sub Workbook_Open()
    On Error GoTo myErr
    ChDir "(dir)"
    Workbooks.Open "(file).xls"
    Workbooks.Open "(file).xls"
    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For l = 1 To UBound(Which)
    Workbooks.Open Which (l)
    Next
    Workbooks.Open Which
    End
    myErr:
    End Sub
    _________________
    "What am I, a magnet for these idiots?"
    --Pearl Forrester, MST3K


    [ This Message was edited by: Von Pookie on 2002-02-21 09:26 ]

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    Code:
    Private Sub Workbook_Open()
    On Error GoTo myErr
    ChDir "C:Documents and SettingsksharpeDesktopECR"
    Workbooks.Open "C:Documents and SettingsksharpeDesktopECRUPGUPG List Revised.xls"
    Workbooks.Open "C:Documents and SettingsksharpeDesktopECRMain Directory.xls"
    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(Which)
       Workbooks.Open Which(I)
    Next
    
    End
    myErr:
    End Sub

    [ This Message was edited by: Russell Hauf on 2002-02-21 09:27 ]

  7. #7
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    That seemed to do the trick.

    Thanks!
    Kristy

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com