Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
noyb
 
Posts: n/a
Default General purpose "open links" macro

Is it possible to write a macro which will open all linked workbooks
without hard coding the workbook names so as to make such a macro more
general?
Thanks
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

When you say 'all linked workbooks', do you mean all workbooks directly
linked to the current workbook, or every workbook in every link in every
workbook that is opened by the routine, ie if book a is linked to b, and b
to c and c to d and d to e etc, then by running the routine from a, you
would open b, c, d, e?

If you just want to open all workbooks with references in 'Edit Links'
within your activeworkbook, then how about:-

Sub OpenAllLinks()
Dim CurWkbk As Workbook
Dim arLinks As Variant
Dim intIndex As Integer
Set CurWkbk = ActiveWorkbook
arLinks = CurWkbk.LinkSources(xlExcelLinks)

If Not IsEmpty(arLinks) Then
For intIndex = LBound(arLinks) To UBound(arLinks)
CurWkbk.OpenLinks arLinks(intIndex)
Next intIndex
Else
MsgBox "The active workbook contains no external links."
End If
End Sub

Slight tweak on example straight out of help.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"noyb" wrote in message
...
Is it possible to write a macro which will open all linked workbooks
without hard coding the workbook names so as to make such a macro more
general?
Thanks



  #3   Report Post  
noyb
 
Posts: n/a
Default

Works great, thanks

Ken Wright wrote:
When you say 'all linked workbooks', do you mean all workbooks directly
linked to the current workbook, or every workbook in every link in every
workbook that is opened by the routine, ie if book a is linked to b, and b
to c and c to d and d to e etc, then by running the routine from a, you
would open b, c, d, e?

If you just want to open all workbooks with references in 'Edit Links'
within your activeworkbook, then how about:-

Sub OpenAllLinks()
Dim CurWkbk As Workbook
Dim arLinks As Variant
Dim intIndex As Integer
Set CurWkbk = ActiveWorkbook
arLinks = CurWkbk.LinkSources(xlExcelLinks)

If Not IsEmpty(arLinks) Then
For intIndex = LBound(arLinks) To UBound(arLinks)
CurWkbk.OpenLinks arLinks(intIndex)
Next intIndex
Else
MsgBox "The active workbook contains no external links."
End If
End Sub

Slight tweak on example straight out of help.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 1 July 27th 05 02:02 PM
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 0 July 14th 05 05:55 PM
Button fails to call macro when open an Excel via Intranet tigertax Excel Discussion (Misc queries) 1 April 12th 05 10:21 AM
Open Word Doc From Excel Macro MATT Excel Discussion (Misc queries) 4 December 14th 04 01:09 AM
How do I get my personal macro worksheet to open whenever I open . Claudia_R Excel Discussion (Misc queries) 3 December 10th 04 12:59 AM


All times are GMT +1. The time now is 07:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"