Linksources error

[mikefromUK]

All

The eventual purpose of the program below is the to ask the user into input two strings FindString and ReplaceString. It then opens all the excel workbooks (both current and in directory(s) below it), find the links within a workbook, find Findstring, substitute it with ReplaceString, and see if the link exists.

At the moment, I just want to display all the links in a excel workbook, for all the workbooks.

The following piece of code is giving me an type mismatch error.

Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)

As far as I can see, it is fine.

Any ideas on what is wrong?

Thanks

Michael



Option Explicit


Sub main()
' Global search and replace (including subdirectories) text so as to update the links in a series of spreadsheets
' by Michael Gibson
' Version 1.00
' 27 Aug 2002


Application.Calculation = xlCalculationManual ' this will force the sheet not to recalculate as their is no explicit calculate command in this program


Dim strFindString 'as string
Dim strReplaceString 'as string
Dim objFSO 'as object
Dim objFolder 'as object
Dim objFile 'as object
Dim objCurrentFolder 'as folder
Dim File 'as file
Dim intResponse 'as interger
Dim bolFinishProgram, bolStopProgram As Boolean
Dim intResponse2, intResponse3, intReplaceCheck, intReverse
Dim strSecondReplaceString 'as string
Dim ComboBox1 As ComboBox
Dim intUserOption As Integer
Dim strInputOptions(1 To 10) As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName("."))
Set objFolder = objFSO.getFolder(objFSO.GetAbsolutePathName(Workbooks(1).Path))
'MsgBox ("Workbooks(1).path =" & Workbooks(1).Path)
'MsgBox ("Workbooks(1).Name =" & Workbooks(1).Name)
'MsgBox ("objFSO.GetDriveName(Workbooks(1).Path) = " & objFSO.GetDriveName(Workbooks(1).Path))

'MsgBox ("Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & " = (Workbooks(1).Path = objFSO.GetDriveName(Workbooks(1).Path) & ""))

Do

strFindString = InputBox("Please input the string you are thinking of replacing?")
strReplaceString = InputBox("Please input the string you are thinking replacing '" & strFindString & "' with?")
intResponse = MsgBox("Do you wish to search for possible errors if '" & strFindString & "' is replaced with '" & strReplaceString & "'", 3)

Loop Until intResponse <> vbNo

If intResponse = vbYes Then FindPossibleErrors objFSO, objFolder, strFindString, strReplaceString


End Sub


Sub FindPossibleErrors(objFSO, objCurrentFolder, strFindString, strReplaceString)

Dim objFile
Dim objWorkSheet
Dim objLocalFolder
Dim rngCurrentCell As Range
Dim strCurrentCellFormula
Dim arrWorkbookLinks
Dim intLinksCounter

Open Workbooks(1).Path & "PossibleErrorsList.txt" For Output As #1

For Each objFile In objCurrentFolder.Files
If (objFSO.GetExtensionName(objFile.Name) = "xls") And (objFile.Name <> "findpossibleerrors.xls") Then 'check if program is Excel
Workbooks.Open FileName:=objCurrentFolder.Path & "" & objFile.Name, UpdateLinks:=False
Set arrWorkbookLinks = Workbooks.Item(objFile.Name).LinkSources(xlExcelLinks)
For intLinksCounter = 1 To UBound(arrWorkbookLinks)
MsgBox (arrWorkbookLinks(arrWorkbookLinks))
Next
Workbooks.Item(objFile.Name).Close
End If
Next

For Each objLocalFolder In objCurrentFolder.subfolders
FindPossibleErrors objFSO, objLocalFolder, strFindString, strReplaceString
Next

Close #1

End Sub