PDA

View Full Version : [SOLVED] This one should get u thinking



gibbo1715
01-24-2005, 01:19 AM
Can anyone help with the below code i Got from Jake ( DrJ)

It reads a number of word docs and looks for a key word which is then displayed in an excel spreadsheet, works fine at home but at work i get the following errors.

First error i get is WordBasic.DisableAutoMacros True but i can get around that one by commenting out the relevent code the second is on the AppWrd.Selection.Find.Execute shown in the code below, both versions are excel 2000 only difference is one is searching over a network and the other isn't.


Option Compare Text
Option Explicit

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function

Sub ListText()
Dim AppWrd As New Word.Application
Dim Doc As Word.Document
Dim Search As String
Dim Prompt As String
Dim Title As String
Dim PageX() As Long
Dim LineX() As Long
Dim FPath() As String
Dim FName() As String
Dim Row As Long
Dim Counter As Long
Dim Pos As Double
Dim Path As String
Dim FileName As String
Dim MyResponse As VbMsgBoxResult
Dim StartLine As Long
Dim StartPage As Long
Dim WS As Worksheet
'WordBasic.DisableAutoMacros True
'*** Get folder from user ***
Prompt = "Select the folder with the files that you want to search through."
Title = "Folder Selection"
MsgBox Prompt, vbInformation, Title
Path = BrowseFolder("Select A Folder")
If Path = "" Then
Prompt = "You didn't select a folder. The procedure has been canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
GoTo Canceled:
End If
'*** This code works with XP only and is also used to pick a folder ***
'Application.FileDialog(msoFileDialogFolderPicker).Show
'Path = CurDir
Prompt = "What do you want to search for?"
Title = "Search Criteria"
Search = InputBox(Prompt, Title)
If Search = "" Then
GoTo Canceled
End If
'*** Confirm the procedure before continuing ***
Prompt = "Are you sure that you want to search all the files in the folder:" & _
vbCrLf & Path & " for " & """" & Search & """" & "?"
Title = "Confirm Procedure"
MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
If MyResponse = vbNo Then
GoTo Canceled:
End If
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'*** Loop through all Word documents and search each of them for the specified criteria***
FileName = Dir(Path & "\*.doc", vbNormal)
Do Until FileName = ""
On Error Resume Next
Set Doc = AppWrd.Documents.Open(Path & "\" & FileName, ReadOnly:=True, _
PasswordDocument:="DRJWasHere")
If Err <> 0 Then
GoTo NextLoop:
End If
On Error GoTo 0
With Doc
AppWrd.Selection.Find.ClearFormatting
With AppWrd.Selection.Find
.Text = Search
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
AppWrd.Selection.Find.Execute
If AppWrd.Selection.Range.Text <> Search Then
GoTo NextLoop:
End If
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage)
Counter = Counter + 1
ReDim Preserve LineX(1 To Counter)
ReDim Preserve PageX(1 To Counter)
ReDim Preserve FPath(1 To Counter)
ReDim Preserve FName(1 To Counter)
LineX(Counter) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
PageX(Counter) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
FPath(Counter) = Doc.Path
FName(Counter) = Doc.Name
StartLine = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
StartPage = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
AppWrd.Selection.Find.Execute
Do While Pos <> AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage) Or _
StartLine <> AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber) Or _
StartPage <> AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
Counter = Counter + 1
ReDim Preserve LineX(1 To Counter)
ReDim Preserve PageX(1 To Counter)
ReDim Preserve FPath(1 To Counter)
ReDim Preserve FName(1 To Counter)
If LineX(Counter - 1) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber) And _
PageX(Counter - 1) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber) And _
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage) Then
Exit Do
Else
LineX(Counter) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
PageX(Counter) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
FPath(Counter) = Doc.Path
FName(Counter) = Doc.Name
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage)
AppWrd.Selection.Find.Execute
End If
Loop
NextLoop:
End With
Doc.Close False
Set Doc = Nothing
On Error GoTo 0
FileName = Dir()
Loop
If Counter = 0 Then
MsgBox Search & " was not found.", vbInformation, "Zero Results For Search"
GoTo Canceled:
End If
Set WS = ThisWorkbook.Sheets("Sheet1")
With WS
.Range("A1").Value = "Occurrences of the word " & """" & Search & """"
.Range("A1:C1").Merge
.Range("A2").Value = "Document Path"
.Range("B2").Value = "Page Number"
.Range("C2").Value = "Line Number"
.Range("A1:C2").Font.Bold = True
.Range("A1:C2").HorizontalAlignment = xlCenter
For Row = UBound(LineX) To 1 Step -1
If PageX(Row) = 0 Or LineX(Row) = 0 Then
Else
.Range("B" & Row + 2).Value = PageX(Row)
.Range("C" & Row + 2).Value = LineX(Row)
.Hyperlinks.Add Anchor:=.Range("A" & Row + 2), _
Address:=FPath(Row) & "\" & FName(Row), TextToDisplay:=FName(Row)
End If
Next Row
.Range("A:C").EntireColumn.AutoFit
End With
Canceled:
AppWrd.Quit
Set Doc = Nothing
Set AppWrd = Nothing
'WordBasic.DisableAutoMacros False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Jacob Hilderbrand
01-24-2005, 02:58 AM
Can you test the code over your network on only files that have the word you are searching for? Let me know if that works or not.

gibbo1715
01-24-2005, 04:28 AM
Jake


It does work if all files contain the word being searched for, i think the problem maybe some of the documents are on word forms, if i put a few files in one folder seems to work ok

Jacob Hilderbrand
01-24-2005, 04:52 AM
See if this works for you.


Option Compare Text
Option Explicit

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function

Sub ListText()
Dim AppWrd As New Word.Application
Dim Doc As Word.Document
Dim Search As String
Dim Prompt As String
Dim Title As String
Dim PageX() As Long
Dim LineX() As Long
Dim FPath() As String
Dim FName() As String
Dim Row As Long
Dim Counter As Long
Dim Pos As Double
Dim Path As String
Dim FileName As String
Dim MyResponse As VbMsgBoxResult
Dim StartLine As Long
Dim StartPage As Long
Dim WS As Worksheet
'WordBasic.DisableAutoMacros True
'*** Get folder from user ***
Prompt = "Select the folder with the files that you want to search through."
Title = "Folder Selection"
MsgBox Prompt, vbInformation, Title
Path = BrowseFolder("Select A Folder")
If Path = "" Then
Prompt = "You didn't select a folder. The procedure has been canceled."
Title = "Procedure Canceled"
MsgBox Prompt, vbCritical, Title
GoTo Canceled:
End If
'*** This code works with XP only and is also used to pick a folder ***
'Application.FileDialog(msoFileDialogFolderPicker).Show
'Path = CurDir
Prompt = "What do you want to search for?"
Title = "Search Criteria"
Search = InputBox(Prompt, Title)
If Search = "" Then
GoTo Canceled
End If
'*** Confirm the procedure before continuing ***
Prompt = "Are you sure that you want to search all the files in the folder:" & _
vbCrLf & Path & " for " & """" & Search & """" & "?"
Title = "Confirm Procedure"
MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
If MyResponse = vbNo Then
GoTo Canceled:
End If
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'*** Loop through all Word documents and search each of them for the specified criteria***
FileName = Dir(Path & "\*.doc", vbNormal)
Do Until FileName = ""
On Error Resume Next
Set Doc = AppWrd.Documents.Open(Path & "\" & FileName, ReadOnly:=True, _
PasswordDocument:="DRJWasHere")
If Err <> 0 Then
GoTo NextLoop:
End If
On Error GoTo 0
With Doc
AppWrd.Selection.Find.ClearFormatting
With AppWrd.Selection.Find
.Text = Search
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
On Error Resume Next
AppWrd.Selection.Find.Execute
If Err <> 0 Then
GoTo NextLoop:
End If
On Error GoTo 0
If AppWrd.Selection.Range.Text <> Search Then
GoTo NextLoop:
End If
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage)
Counter = Counter + 1
ReDim Preserve LineX(1 To Counter)
ReDim Preserve PageX(1 To Counter)
ReDim Preserve FPath(1 To Counter)
ReDim Preserve FName(1 To Counter)
LineX(Counter) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
PageX(Counter) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
FPath(Counter) = Doc.Path
FName(Counter) = Doc.Name
StartLine = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
StartPage = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
AppWrd.Selection.Find.Execute
Do While Pos <> AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage) Or _
StartLine <> AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber) Or _
StartPage <> AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
Counter = Counter + 1
ReDim Preserve LineX(1 To Counter)
ReDim Preserve PageX(1 To Counter)
ReDim Preserve FPath(1 To Counter)
ReDim Preserve FName(1 To Counter)
If LineX(Counter - 1) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber) And _
PageX(Counter - 1) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber) And _
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage) Then
Exit Do
Else
LineX(Counter) = AppWrd.Selection.Range.Information(wdFirstCharacterLineNumber)
PageX(Counter) = AppWrd.Selection.Range.Information(wdActiveEndPageNumber)
FPath(Counter) = Doc.Path
FName(Counter) = Doc.Name
Pos = AppWrd.Selection.Range.Information(wdHorizontalPositionRelativeToPage)
AppWrd.Selection.Find.Execute
End If
Loop
NextLoop:
End With
Doc.Close False
Set Doc = Nothing
On Error GoTo 0
FileName = Dir()
Loop
If Counter = 0 Then
MsgBox Search & " was not found.", vbInformation, "Zero Results For Search"
GoTo Canceled:
End If
Set WS = ThisWorkbook.Sheets("Sheet1")
With WS
.Range("A1").Value = "Occurrences of the word " & """" & Search & """"
.Range("A1:C1").Merge
.Range("A2").Value = "Document Path"
.Range("B2").Value = "Page Number"
.Range("C2").Value = "Line Number"
.Range("A1:C2").Font.Bold = True
.Range("A1:C2").HorizontalAlignment = xlCenter
For Row = UBound(LineX) To 1 Step -1
If PageX(Row) = 0 Or LineX(Row) = 0 Then
Else
.Range("B" & Row + 2).Value = PageX(Row)
.Range("C" & Row + 2).Value = LineX(Row)
.Hyperlinks.Add Anchor:=.Range("A" & Row + 2), _
Address:=FPath(Row) & "\" & FName(Row), TextToDisplay:=FName(Row)
End If
Next Row
.Range("A:C").EntireColumn.AutoFit
End With
Canceled:
AppWrd.Quit
Set Doc = Nothing
Set AppWrd = Nothing
'WordBasic.DisableAutoMacros False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

gibbo1715
01-24-2005, 05:21 AM
Thanks for your help on this Jake

I now get an error message "variable not defined" for MyError, if i define it as a string it comes back saying word not found even if it is in several documents?

gibbo1715
01-24-2005, 05:23 AM
Ok should have read the code in more detail, changed the MyError to Err and seems to work fine

Jacob Hilderbrand
01-24-2005, 05:43 AM
http://vbaexpress.com/forum/images/smilies/doh.gifTypo on my part. I was thinking about writing it a different way, and forgot to change the variable back to the Err object.

So does it work properly for you now?

gibbo1715
01-24-2005, 07:52 AM
Once again Jake i owe you my thanks

Working perfectly

gibbo1715
01-24-2005, 10:21 AM
Just one more thing, is it possible to put a progress bar on the screen whilst this is running (would need to be above a user form im using to run the code and not in the excel status bar?

Jacob Hilderbrand
01-24-2005, 03:31 PM
See if this KB Entry can help you.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=169