Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Opening linked files (VBA Excel)

    Is there a code that I can write that will automatically open all Excel files that are linked and then update those open files?
    Can someone answer me before 1 pm central time.... I am teaching an Excel class.
    Thank you soooooo much

  2. #2
    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: Auto Opening linked files (VBA Excel)

    Hi Tanya,
    Is there a reason you need to do this in VBA rather than simply opening all the files and then saving the workspace so that when you reopen that workspace it automatically opens all the relevant files?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Opening linked files (VBA Excel)

    The problem is that this is a very large company with people who are creating files and sharing links on several different drives and some people dont know where that link is just that it is linked and that it needs to be updated. However, any change that is made in the parent (ie: a column is added), it messes up the formula unless all are open.

  4. #4
    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: Auto Opening linked files (VBA Excel)

    OK then, something like this ought to do it:
    <pre>Sub OpenLinkedFiles()
    Dim arrlinks, i As Integer
    arrlinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(arrlinks) Then
    For i = 1 To UBound(arrlinks)
    Workbooks.Open arrlinks(i)
    Next i
    End If

    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Opening linked files (VBA Excel)

    Tanya, not too tough. I'll have mercy on a fellow teacher. Can't imagine why you want this, though.
    <pre>Sub Macro2()
    Dim varLink As Variant
    For Each varLink In ActiveWorkbook.LinkSources(xlExcelLinks)
    Workbooks.Open varLink
    Next varLink
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Opening linked files (VBA Excel)

    When I typed it in it says Sub or Function not defined. Help. Not being a VB wiz at all, it doesn't appear to work

  7. #7
    Star Lounger
    Join Date
    Oct 2001
    Location
    Ellettsville, Indiana, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Opening linked files (VBA Excel)

    I am not sure either. It really isn't what I want, its my clients that want it. Hmmmmmm

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto Opening linked files (VBA Excel)

    Check that you typed everything as is, including "punctuation", e.g. include the period in ActiveWorkbook.LinkSources (between ActiveWorkbook and LinkSources) etc.

    Andrew C

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Opening linked files (VBA Excel)

    1) Open excel workbook that has cells with links
    2) Tools | Macro | Record New Macro & press OK
    3) Press the Stop Recording square
    4) Open Word, switch back here, copy the lines "Sub..." down to "End Sub", and paste them into Word
    5) In Word, select all <Ctrl>A and copy <Ctrl>C
    6) Switch to Excel, press <Alt><F11> to get to VBA
    7) Select all of the code in the code window <Ctrl>A and paste in my code <Ctrl>V
    8) Press <Alt><F11> to switch back to Excel
    9) Use Tools | Macro | Macros and run Macro2
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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