Bob said:
I have a directory with Excel files.
Is in possible to use the Windows XP's search function to find only the
files which contains one or more hyperlinks, no matter the name of the
hyperlink.
I tried to use the possibility to search for 'a word or phrase in a file',
but what do I have to search for? How is a hyperlink stored in an Excel
file?
Hi
You can use a VBScript for this.
Put the VBScript below in a .vbs file. Change the path/file name in the
variable sOutFile (current value is C:\FilesWithHyperlinks.txt).
You can now drag and drop the folder onto the VBScript, or send in the
folder name on the command line.
If any Excel files with Hyperlinks are found, the file(s) will be
listed in the file defined in sOutFile, and the file will be
automatically opened in Notepad.
Note that the current version of the script will not find Excel files
in any subfolders.
'--------------------8<----------------------
Const OpenAsASCII = 0
Const OverwriteIfExist = -1
' file that will contain the result
sOutFile = "C:\FilesWithHyperlinks.txt"
Set oArgs = WScript.Arguments
sTitle = "Hyperlinks search"
sErrMsg = "Error: You need to supply a folder path as input!"
If oArgs.Count <> 1 Then
MsgBox sErrMsg, vbCritical + vbSystemModal, sTitle
Wscript.Quit
Elseif oArgs.Count = 1 And Not oFSO.FolderExists(oArgs(0)) Then
MsgBox sErrMsg, vbCritical + vbSystemModal, sTitle
Wscript.Quit
End If
' folder where the excel files resides
sFolder = oArgs(0)
Set oShell = CreateObject("WScript.Shell")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oExcelApp = CreateObject("Excel.Application")
' Enable this line if you want to see Excel.
'oExcelApp.Visible = True
Set fOutFile = oFSO.CreateTextFile _
(sOutFile, OverwriteIfExist, OpenAsASCII)
Set oFolder = oFSO.GetFolder(sFolder)
For Each oFile In oFolder.Files
If LCase(oFSO.GetExtensionName(oFile)) = "xls" Then
bHyperlinkFound = False
oExcelApp.Workbooks.Open oFile.Path
Set oExcelBook = oExcelApp.ActiveWorkbook
For Each oSheet In oExcelBook.Worksheets
For Each oLink In oSheet.Hyperlinks
' Hyperlink is found
bHyperlinkFound = True
Exit For
Next
If bHyperlinkFound = True Then
sResult = sResult & oFile.Path & vbCrLf
' Hyperlink is found, no point in looping in this file anymore
Exit For
End If
Next
oExcelApp.Workbooks.Close
End If
Next
oExcelApp.Quit
If sResult <> "" Then
fOutFile.Write sResult
fOutFile.Close
oShell.Run "Notepad.exe """ & sOutFile & """" , 1, False
Else¨
fOutFile.Close
MsgBox "No Excel files with Hyperlinks found.", _
vbInformation + vbSystemModal, sTitle
End If
'--------------------8<----------------------
WSH 5.6 documentation (local help file) can be downloaded
from here if you haven't got it already:
http://msdn.microsoft.com/downloads/list/webdev.asp