Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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.links
|
|||
|
|||
Running Macros
The body of your macro looks fine to me, as a macro to update the links
in the active workbook (in fact I think I recognise some of the code<g). But it shouldn't be Workbook_Open. No need to involve Access. You can run the macro from Excel. Tools / Macro / Run But first, put it in a normal module in a workbook of your choosing. Change it from Private Sub Workbook_Open to Sub UpdateActiveWorkbookLinks() And then add a procedure that will cycle through the workbooks you need to update. It might look something like this: Sub ProcessXLSFilesInDirectory() Dim aFiles() As String, iFile As Integer Dim stFile As String, vFile As Variant Dim stDirectory As String ' first build an array of the files and then process them ' this is because you may upset the Dir function if you save a file stDirectory = "D:\TEMP\" ' name of directory to look in ' use Dir function to find XLS files in Directory stFile = Dir(stDirectory & "*.XLS") If stFile = "" Then Exit Sub ' no files to process Do While stFile < "" ' add to array of files iFile = iFile + 1 ' add one element to the array ReDim Preserve aFiles(1 To iFile) aFiles(iFile) = stFile stFile = Dir() ' gets next file Loop ' now process the files For Each vFile In aFiles Workbooks.Open stDirectory & vFile UpdateActiveWorkbookLinks Workbooks(vFile).Close saveChanges:=False Next vFile End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Running Macros
Bill -- I'm getting an error at the Password Line -- any thoughts? Here's
what I have: (2) Modules ... ******When I run the code, I get Error Message "Compile Error: Syntax Error"****** Module #1 Sub UpdateActiveWorkbookLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect Password:="mypassword" (******for some reason, this line appears in RED******) Next vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To (******for some reason, this line appears in RED******) UBound(vLinkSources) (******for some reason, this line appears in RED******) ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks Next End If For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name).Protect Password:="mypassword" (******for some reason, this line appears in RED******) Next End Sub Module #2 Sub ProcessXLSFilesInDirectory() Dim aFiles() As String, iFile As Integer Dim stFile As String, vFile As Variant Dim stDirectory As String ' first build an array of the files and then process them ' this is because you may upset the Dir function if you save a file stDirectory = "E:\AL1403 05-06\" ' name of directory to look in ' use Dir function to find XLS files in Directory stFile = Dir(stDirectory & "*.XLS") If stFile = "" Then Exit Sub ' no files to process Do While stFile < "" ' add to array of files iFile = iFile + 1 ' add one element to the array ReDim Preserve aFiles(1 To iFile) aFiles(iFile) = stFile stFile = Dir() ' gets next file Loop ' now process the files For Each vFile In aFiles Workbooks.Open stDirectory & vFile UpdateActiveWorkbookLinks Workbooks(vFile).Close saveChanges:=False Next vFile End Sub Thanks in advance for all your assistance. "Bill Manville" wrote in message ... The body of your macro looks fine to me, as a macro to update the links in the active workbook (in fact I think I recognise some of the code<g). But it shouldn't be Workbook_Open. No need to involve Access. You can run the macro from Excel. Tools / Macro / Run But first, put it in a normal module in a workbook of your choosing. Change it from Private Sub Workbook_Open to Sub UpdateActiveWorkbookLinks() And then add a procedure that will cycle through the workbooks you need to update. It might look something like this: Sub ProcessXLSFilesInDirectory() Dim aFiles() As String, iFile As Integer Dim stFile As String, vFile As Variant Dim stDirectory As String ' first build an array of the files and then process them ' this is because you may upset the Dir function if you save a file stDirectory = "D:\TEMP\" ' name of directory to look in ' use Dir function to find XLS files in Directory stFile = Dir(stDirectory & "*.XLS") If stFile = "" Then Exit Sub ' no files to process Do While stFile < "" ' add to array of files iFile = iFile + 1 ' add one element to the array ReDim Preserve aFiles(1 To iFile) aFiles(iFile) = stFile stFile = Dir() ' gets next file Loop ' now process the files For Each vFile In aFiles Workbooks.Open stDirectory & vFile UpdateActiveWorkbookLinks Workbooks(vFile).Close saveChanges:=False Next vFile End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Running Macros
You've been the victim of line breaks inserted in the original longer
lines. Each of the items in red should have the next line appended to it ( or end it with space underscore so that the next line is treated as a continuation Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
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) | |||
Running macros when Excel starts up | Excel Discussion (Misc queries) | |||
Problem with running Macros | Excel Discussion (Misc queries) | |||
HELP - Running Macros in VBA | New Users to Excel |