Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.misc
|
|||
|
|||
Running Macros
That code is written to run from within an Excel workbook. It would run
automatically each time that workbook is opened. Since, as I recall, you got the code from another example somewhere, it may be that the Open event for the workbook is not the place you want it, but you would probably want it to be a regular Macro that you would call on demand from within Excel. Open Excel, use [Alt]+[F11] to open up the VBA editor. Choose Insert | Module and cut and paste this into the module: Sub GetLinkedData() 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 That is the same code you had, just renamed and set up to be run as a regular Macro from the Excel toolbar: Tools | Macros | Macro then highlight its name and click the [Run] button. But this is going to just deal with the one workbook - and you've got 200 or so of them to deal with! There are a couple of different ways to deal with this. Which way is best is kind of determined by where those other workbooks are. If they are all in one folder, there's one pretty efficient way to do it in code. If they may be scattered around there are other ways to deal with them. You'll need code to identify and open them just at the start of what you have now (right after the DIM statements) and close each up after the updates to it are done right before the End Sub statement. Also, the workbook with this code in it will have to be run on a system that has access to the database or other source of information that the links in the workbooks refer to for the updates to succeed. " 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 macros without lowering security | Excel Discussion (Misc queries) | |||
Anyone have troubles running 2003 macros in 2000 version of Excel | Excel Discussion (Misc queries) | |||
Problem with running Macros | Excel Discussion (Misc queries) | |||
HELP - Running Macros in VBA | New Users to Excel | |||
Macros running slow in Excel 2003 | Excel Discussion (Misc queries) |