Running Macros

I have the following Macro (which I need to run on approximately 200
different workbooks) -- what is the BEST way to achieve this? Should I use
a Function (and if so, what would it look like)?

NOTE: I'm assuming that this Macro should reside in a Module -- but, should
this be run outside of EXCEL (say, from a Form in ACCESS)?

Here's my code:

Does anyone see anything that I may be missing? Here's the MOST important
thing I need this code to do (assuming there are no further modifications
needed) ...



This code (Macro) needs to perform this same task on approximately 200
different Workbooks (all residing in the same Network Directory), but ONLY
when a Command Button is pressed. I'm assuming that I'll need to create a
Form (in ACCESS perhaps) and call this Macro -- correct? How would I do
this, and how would I get this Macro to perform this Link Update on all 200
or so Workbooks?



Private Sub Workbook_Open()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect
Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To
UBound(vLinkSources)
ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Protect
Password:="mypassword"
Next
End Sub

Put this in a general module of a workbook

sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = "C:\MyDummy\"
sName = Dir(sPath & "*.xls")
do while sName <> ""
set bk = Workbook.Open(sPath & sName)
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Unprotect Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
ActiveWorkbook.UpdateLink _
vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Protect Password:="mypassword"
Next
bk.Close Savechanges:=True
sName = Dir()
Loop
End Sub

Put a command button on a worksheet in that workbook. Double click on it to
get to the code

Private Sub CommandButton1_click()
UpdateAllLinks
End sub

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> I have the following Macro (which I need to run on approximately 200
> different workbooks) -- what is the BEST way to achieve this? Should I use
> a Function (and if so, what would it look like)?
>
> NOTE: I'm assuming that this Macro should reside in a Module -- but, should
> this be run outside of EXCEL (say, from a Form in ACCESS)?
>
> Here's my code:
>
> Does anyone see anything that I may be missing? Here's the MOST important
> thing I need this code to do (assuming there are no further modifications
> needed) ...
>
>
>
> This code (Macro) needs to perform this same task on approximately 200
> different Workbooks (all residing in the same Network Directory), but ONLY
> when a Command Button is pressed. I'm assuming that I'll need to create a
> Form (in ACCESS perhaps) and call this Macro -- correct? How would I do
> this, and how would I get this Macro to perform this Link Update on all 200
> or so Workbooks?
>
>
>
> Private Sub Workbook_Open()
> Dim vLinkSources
> Dim iLinkSource As Integer
> Dim AnySheet As Worksheet
> For Each AnySheet In ActiveWorkbook.Worksheets
> ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect
> Password:="mypassword"
> Next
> vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
> If Not IsEmpty(vLinkSources) Then
> For iLinkSource = LBound(vLinkSources) To
> UBound(vLinkSources)
> ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks
> Next
> End If
> For Each AnySheet In ActiveWorkbook.Worksheets
> ActiveWorkbook.Worksheets(AnySheet.Name).Protect
> Password:="mypassword"
> Next
> End Sub
>
>
>

Tom -- Thank you for the assistance ...
When I ran the code, I got a "Run-time error '424': Object Required at the
following line:

Set bk = Workbook.Open(sPath & sName)

Any thoughts as to what's causing this?

Here's the code:

Sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = "C:\Temp\"
sName = Dir(sPath & "*.xls")
Do While sName <> ""
Set bk = Workbook.Open(sPath & sName)
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Unprotect Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
ActiveWorkbook.UpdateLink _
vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Protect Password:="mypassword"
Next
bk.Close Savechanges:=True
sName = Dir()
Loop
End Sub


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Put this in a general module of a workbook
>
> sub UpdateAllLinks()
> Dim vLinkSources
> Dim iLinkSource As Integer
> Dim AnySheet As Worksheet
> sPath = "C:\MyDummy\"
> sName = Dir(sPath & "*.xls")
> do while sName <> ""
> set bk = Workbook.Open(sPath & sName)
> For Each AnySheet In ActiveWorkbook.Worksheets
> ActiveWorkbook.Worksheets(AnySheet.Name) _
> .Unprotect Password:="mypassword"
> Next
> vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
> If Not IsEmpty(vLinkSources) Then
> For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
> ActiveWorkbook.UpdateLink _
> vLinkSources(iLinkSource), xlExcelLinks
> Next
> End If
> For Each AnySheet In ActiveWorkbook.Worksheets
> ActiveWorkbook.Worksheets(AnySheet.Name) _
> .Protect Password:="mypassword"
> Next
> bk.Close Savechanges:=True
> sName = Dir()
> Loop
> End Sub
>
> Put a command button on a worksheet in that workbook. Double click on it
> to
> get to the code
>
> Private Sub CommandButton1_click()
> UpdateAllLinks
> End sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
>> I have the following Macro (which I need to run on approximately 200
>> different workbooks) -- what is the BEST way to achieve this? Should I
>> use
>> a Function (and if so, what would it look like)?
>>
>> NOTE: I'm assuming that this Macro should reside in a Module -- but,
>> should
>> this be run outside of EXCEL (say, from a Form in ACCESS)?
>>
>> Here's my code:
>>
>> Does anyone see anything that I may be missing? Here's the MOST
>> important
>> thing I need this code to do (assuming there are no further modifications
>> needed) ...
>>
>>
>>
>> This code (Macro) needs to perform this same task on approximately 200
>> different Workbooks (all residing in the same Network Directory), but
>> ONLY
>> when a Command Button is pressed. I'm assuming that I'll need to create
>> a
>> Form (in ACCESS perhaps) and call this Macro -- correct? How would I do
>> this, and how would I get this Macro to perform this Link Update on all
>> 200
>> or so Workbooks?
>>
>>
>>
>> Private Sub Workbook_Open()
>> Dim vLinkSources
>> Dim iLinkSource As Integer
>> Dim AnySheet As Worksheet
>> For Each AnySheet In ActiveWorkbook.Worksheets
>> ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect
>> Password:="mypassword"
>> Next
>> vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
>> If Not IsEmpty(vLinkSources) Then
>> For iLinkSource = LBound(vLinkSources) To
>> UBound(vLinkSources)
>> ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks
>> Next
>> End If
>> For Each AnySheet In ActiveWorkbook.Worksheets
>> ActiveWorkbook.Worksheets(AnySheet.Name).Protect
>> Password:="mypassword"
>> Next
>> End Sub
>>
>>
>>