Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macros
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macros
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" wrote in message ... 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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running 4 macros at once | Excel Discussion (Misc queries) | |||
running macros | Excel Discussion (Misc queries) | |||
Macros not running | Excel Programming | |||
Running Macros | Excel Programming | |||
Running macros | Excel Programming |