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

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

For I ...
For H...

Next H
Next I

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)

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") <<<---

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

In that case, change

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

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))

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

Thank you for all your help everyone!