Opening and closing linked workbooks

[Gavona]

I am trying to:
1. open all linked workbooks (if not already open)
2. Copy some formulae that use the links, calculate the copied formulae then convert them to values.
3. Close the workbooks that I opened.

This is a generic, frequently used routine rather than limited to a particular project. Currently I do steps 1 and 3 manually.
The code I have so far for steps 1 and 3 is below.

Issue of the moment:
I can't perform step 3.
Workbooks(arLinks(intIndex)).Close SaveChanges:=False
Gives "Subscript out of range"

Other Unresolved Issues:
1a I have not managed to identify if the workbook is already open
1b If the workbook that is opened itself has links then I want to tell it not to update the links
1c I would be better searching the formulae I am about to copy (Selection.offset(-2,0)) for links that are referred to in them rather than open all linked workbooks. I'll look at that later.

Sub OpenAllLinks()
    Dim arLinks As Variant
    Dim intIndex As Integer
    Dim MyWb As Workbook
    Set MyWb = ActiveWorkbook
    arLinks = MyWb.LinkSources(xlExcelLinks)
   
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
           MsgBox "Opening linked file" & arLinks(intIndex)
           MyWb.OpenLinks arLinks(intIndex)
        Next intIndex
    Else
        MsgBox "The active workbook contains no external links."
    End If
End Sub


Sub CloseAllLinks()
    Dim arLinks As Variant
    Dim intIndex As Integer
    Dim MyWb As Workbook
    Set MyWb = ActiveWorkbook
    arLinks = MyWb.LinkSources(xlExcelLinks)
   
    If Not IsEmpty(arLinks) Then
        For intIndex = LBound(arLinks) To UBound(arLinks)
           MsgBox "Closing linked file" & arLinks(intIndex)
           Workbooks(arLinks(intIndex)).Close SaveChanges:=False
        Next intIndex
    Else
        MsgBox "The active workbook contains no external links."
    End If
End Sub

I am workin in xl 2003 at the moment but needs to run on xl 2000.

I would appreciate any pointers!
Regards,


Gavin

 

[Loomah]

Hi
Without looking too deeply into this - it is Saturday after all!! - I'd guess that the value of intLinks is 0 (zero) in which case there is no such thing as workbook(0).

The easiest way around this is to add "Option Base 1" to the top of your module.

By default the base element of an array is 0 which is why I have made my first assumption. Using "Option Base 1" changes this to 1 which will mean your first workbook will be Workbooks(1).

Looking forward - 1a; try testing from a different angle foor an error. If you try to activate a workbook that isn't open it will generate a trappable error. Error = book open; no error = book not open.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Gavona]

Thanks Loomah, I think my brain must have taken Saturday off. The value of arLinks(intIndex) is the drive and full path "E:\xxxxxx\xxxxxxx\CC_Hierachy_LA01.xls" of course what I needed was just the file name.
I decided to get this as I opened the links rather than devising a way of extracting it from the above string.

Option Explicit
Option Base 1
Dim LWb() As Workbook

Sub CopyFormulae()
Dim arLinks As Variant
Dim intIndex As Integer
Dim MyWb As Workbook
Dim Wbk
Set MyWb = ActiveWorkbook

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

arLinks = MyWb.LinkSources(xlExcelLinks)
ReDim LWb(UBound(arLinks))

'****************loops through and opens links***********************
If Not IsEmpty(arLinks) Then
    For intIndex = LBound(arLinks) To UBound(arLinks)
       Application.DisplayAlerts = False
       Application.StatusBar = "Opening linked file" & arLinks(intIndex)
       MyWb.OpenLinks arLinks(intIndex)
       Application.DisplayAlerts = True
       Set LWb(intIndex) = ActiveWorkbook
    Next intIndex
Else
    MsgBox "The active workbook contains no external links."
End If
MyWb.Activate

'***********************************************************************
'**    Do your thing here                                             **
'***********************************************************************
Application.StatusBar = "starting to apply formulae"
Call FormulaCopy

'**************Loops through and closes all linked workbooks**********
For Each Wbk In LWb
    On Error Resume Next
    Application.StatusBar = "Closing linked Workbook" + Wbk.Name
    Application.DisplayAlerts = False
    Wbk.Close SaveChanges:=False
    Application.DisplayAlerts = True
    On Error GoTo 0
Next Wbk

'**************Reset status bar and calculation mode *****************
Application.StatusBar = "Setting calculation to automatic"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
Application.StatusBar = False
End Sub

I' ll have to think about enhancement 1c (identifying all the links in a selection so I can open just them) later. Any ideas appreciated.

Thanks


Gavin

 

[firefytr]

Here is a function to tell if the workbook is open or not ...


Function IsWbOpen(wbName as String) as Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function


As far as not updating links, check out the Help files on the Open method, there is a syntax for UpdateLinks (boolean, True/False).

-----------
Regards,
Zack Barresse