run macro for all files in directory - error in my code
 

I am trying to run a macro for all files in a directory. Ronald was
able to provide me with the following code which replaces the
directory of a link souce with another directory. The code is as
follows:

Sub ChangeLinks()
Set wbk = Workbooks("GrossAdds.xls")
strOldPath = "W:\Finance\Model"
strNewPath = "W:\xSIRIUS\Completed Versions\Model Working Q107
Reforecast FINAL"
aLinks = wbk.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks) Step 1
strOldLink = aLinks(i)
strNewLink = VBA.Replace(strOldLink, strOldPath, strNewPath)
If strOldLink < strNewLink Then
wbk.ChangeLink strOldLink, strNewLink, xlExcelLinks
End If
Next i
End If
End Sub

That code works just fine on an individual file. I tried to get it to
run for all files in a given directory. Tom Ogilivy provided the code
for that a couple years back and is as follows:

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
set wkbk = Workbooks.Open(.Foundfiles(i))
' work with the wkbk reference
' macro1
wkbk.Close SaveChanges:=False
Else
MsgBox "There were no files found."
End If
End With
End Sub

In efforts to incorporate the first macro with the second, I am
getting an error relating to the second for loop that says "for
control variable already in use" The code I am using is below. Can
anyone help me fix this error please?

Sub WorkWithFiles()
'Dim As Long
Dim wkbk As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "W:\xSIRIUS\Completed Versions\Model Working Q107
Reforecast FINAL"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))
' replace source directory in opened workbook
strOldPath = "W:\Finance\Model"
strNewPath = "W:\xSIRIUS\Completed Versions\Model Working Q107
Reforecast FINAL"
aLinks = wbk.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks) Step 1
strOldLink = aLinks(i)
strNewLink = VBA.Replace(strOldLink, strOldPath,
strNewPath)
If strOldLink < strNewLink Then
wbk.ChangeLink strOldLink, strNewLink, xlExcelLinks
End If
Next i
End If
wkbk.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

run macro for all files in directory - error in my code
 

Change the second For i .. Next i to another letter.

For I ...
For H...

Next H
Next I

run macro for all files in directory - error in my code
 

On May 11, 2:15 pm, David Sisson wrote:
Change the second For i .. Next i to another letter.

For I ...
For H...

Next H
Next I

Thanks. Now I'm getting "object required" error on the following
line:

aLinks = wbk.LinkSources(xlExcelLinks)

run macro for all files in directory - error in my code
 

On May 11, 12:58 pm, cass calculator wrote:

Looks like you dropped this line off when merging the two files.

Sub ChangeLinks()
Set wbk = Workbooks("GrossAdds.xls") <<<---

run macro for all files in directory - error in my code
 

On May 11, 4:27 pm, David Sisson wrote:
On May 11, 12:58 pm, cass calculator wrote:

Looks like you dropped this line off when merging the two files.

Sub ChangeLinks()
Set wbk = Workbooks("GrossAdds.xls") <<<---

yeah I did that because the first macro only words for a specific file
and that line desginates the file. The second macro should set the
workbook to whatever file is open in the for loop

run macro for all files in directory - error in my code
 

In that case, change

aLinks = wbk.LinkSources(xlExcelLinks)
to
aLinks = wkbk.LinkSources(xlExcelLinks)

run macro for all files in directory - error in my code
 

Perfect. My last question is this: Is there a way to modify the code
so that it chooses "dont update" on the update links prompt for each
file it opens in the loop?

I know how to do it with a specific file:

Workbooks.Open Filename:="W:\Workbook.xls", UpdateLinks:=0

But I'm not sure what the syntax is to apply that argument to this
line of the code which opens the i workbook in the for loop:

Set wkbk = Workbooks.Open(.FoundFiles(i))

run macro for all files in directory - error in my code
 

I like to use the argument names:

Set wkbk = Workbooks.Open(filename:=.FoundFiles(i), UpdateLinks:=0)

cass calculator wrote:

Perfect. My last question is this: Is there a way to modify the code
so that it chooses "dont update" on the update links prompt for each
file it opens in the loop?

I know how to do it with a specific file:

Workbooks.Open Filename:="W:\Workbook.xls", UpdateLinks:=0

But I'm not sure what the syntax is to apply that argument to this
line of the code which opens the i workbook in the for loop:

Set wkbk = Workbooks.Open(.FoundFiles(i))

--

Dave Peterson

run macro for all files in directory - error in my code
 

Thank you for all your help everyone!