Results 1 to 6 of 6

Thread: VBA Loop to open other Workbooks based on cell information

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Loop to open other Workbooks based on cell information

    Good Afternoon,

    I have been searching through the forums, not found what I am looking for. If somebody knows of if this has been answered elsewhere, happy to go to that thread for the info.
    My conundrum!
    I have a worksheet that has a list of workbook names and locations. I need a piece of VBA code that will open the workbook based on the name in cell, I will get it to do something whilst it is open, then close the workbook, and cycle onto the next cell down to do the same again, until it finds a blank cell and stops.

    Thanks a million for the help

    Ash

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop to open other Workbooks based on cell information

    Does the column contain the full filepath & workbook name?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jan 2009
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop to open other Workbooks based on cell information

    Quote Originally Posted by Fluff View Post
    Does the column contain the full filepath & workbook name?
    It does indeed

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop to open other Workbooks based on cell information

    In that case, how about
    Code:
    Sub SinTest()
       Dim Wbk As Workbook
       Dim Cl As Range
       For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
          Set Wbk = Workbooks.Open(Cl.Value)
          ' do something here
          Wbk.Close False
       Next Cl
    End Sub
    Change the False to True if you want to save the other workbook before closing it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jan 2009
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop to open other Workbooks based on cell information

    Fluff
    Thanks for the help, doing exactly what I need.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Loop to open other Workbooks based on cell information

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •