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

If the functionality you need is in Excel, then you'd only be complicating
the matter by trying to run it via COM Automation from Access. I admit that
I have not implemented any Excel applications using VBA and the Excel Object
Model, but I'm sure you can accomplish what you want completely in Excel.

As the Excel Object Model and other details are significantly different from
Access, I'd suggest the best place to ask would be in an Excel newsgroup,
particularly one that deals with VBA code (aka, but not quite correctly,
also referred to as "macros" in the Excel environment) in Excel.

My recollection is that you can use Microsoft Forms to create the Forms that
you'd use with Excel spreadsheets.

Larry Linson
Microsoft Access MVP



wrote in message
...
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

Thank you Larry -- I'll look into that and let you know how it turns out --
thanks for your advice and suggestion.
"Larry Linson" wrote in message
...
If the functionality you need is in Excel, then you'd only be complicating
the matter by trying to run it via COM Automation from Access. I admit
that I have not implemented any Excel applications using VBA and the Excel
Object Model, but I'm sure you can accomplish what you want completely in
Excel.

As the Excel Object Model and other details are significantly different
from Access, I'd suggest the best place to ask would be in an Excel
newsgroup, particularly one that deals with VBA code (aka, but not quite
correctly, also referred to as "macros" in the Excel environment) in
Excel.

My recollection is that you can use Microsoft Forms to create the Forms
that you'd use with Excel spreadsheets.

Larry Linson
Microsoft Access MVP



wrote in message
...
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