C
cass calculator
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
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