#1   Report Post  
Old July 6th 06, 03:42 PM posted to microsoft.public.excel.links
 
Posts: n/a
Default 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   Report Post  
Old July 8th 06, 12:02 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
Default 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   Report Post  
Old July 12th 06, 07:42 PM posted to microsoft.public.excel.links
 
Posts: n/a
Default 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   Report Post  
Old July 13th 06, 11:30 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Running macros without lowering security Chip Smith Excel Discussion (Misc queries) 2 May 4th 06 07:56 PM
Anyone have troubles running 2003 macros in 2000 version of Excel Jim Excel Discussion (Misc queries) 0 April 19th 06 02:15 PM
Running macros when Excel starts up Solly Excel Discussion (Misc queries) 1 July 13th 05 11:38 AM
Problem with running Macros Mark Alex Excel Discussion (Misc queries) 1 May 17th 05 02:28 PM
HELP - Running Macros in VBA Louise New Users to Excel 7 April 6th 05 03:21 PM


All times are GMT +1. The time now is 11:01 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017