Find specific word in all location

[Tom.Jones]

Hi,

I searched this forum and others but I have not found what I'm looking for.
I found several VBA code looking for a specific word but I would need something different.
That was my distinct occur:
- Path to the file
- Sheet and location is found word
- Hyperlink to that location (only in cells where it is displayed address)

Occurrence of Harold	62
C:\Work\Account\database
	Group 1.xlsx
		Sheet1	C3	D4	E11	F22
		Sheet3	B11	C14
	Bank 1.xlsm
		Sheet5	A4	D6	F8	I22
		Sheet7	D23	H44
C:\Work\Account\Data
	Team 1.xlsm
		Golf	A11
		Sheet4	A3	H6	F11	G22
		Foreign 3	D44	H46
	Activity.xlsm
		Activ	C11	D15	F44	R12	S7	T11	W29
		Pasive	B12	C22
	Type.xlsm
		1	B12	C15	D11
		2	H33
		3	G12	J45
D:\Result\Capacity\IQ
	High.xlsx
		0-49	A2	C4	D6
		50-69	D11
		70-89	A33	C13	D11	F12
		90-110	C12	F33
		>110	A3	B12	C22	D17
	Medium.xlsm
		0-49	C12	F33
		50-69	A3	B12	C22	D17
		70-89	A2	C4	D6
		90-110	D11
		>110	A33	C13	D11	F12

<tbody>
</tbody>

 

[JLGWhiz]

You would need to list the seven files by their full name (includes path) in a column of a worksheet. For illustration purposes, Column A of sheet 1 will be used in the code below. You would have to modify that part of the code if you use
use something different to list the files. Sheet 1 would be located in the file which hosts the code. Assume the seven files are in cells A2:A8. The workbook name, sheet name and cell address of the found string will be posted in columns B, C and D of the host workbook.

Sub Harold()
Dim sh As Worksheet, wb As Workbook, rng As Range, fStr
Set rng = ThisWorkbook.Sheets(1).Range("A2:A8")
    For Each c In rng
        On Error Resume Next
        Set wb = Workbooks.Open(c.Value)
        On Error GoTo 0
        If Not wb Is Nothing Then
            For Each sh In wb.Sheets
                If sh.UsedRange.Rows.Count > 2 Then 'Skip sheets with no data
                Set fStr = sh.Cells.Find("Harold", LookIn:=xlValues, LookAt:=xlPart)
                    If Not fStr Is Nothing Then
                        fAdr = fStr.Address
                        Do
                            With ThisWorkbook.Sheets(1)
                                .Cells(Rows.Count, 2).End(xlUp)(2) = wb.Name
                                .Cells(Rows.Count, 3).End(xlUp)(2) = sh.Name
                                .Cells(Rows.Count, 4).End(xlUp)(2) = fStr.Address
                            End With
                            fStr.Value = "Harold"
                            Set fStr = sh.Cells.FindNext(fStr)
                        Loop While fStr.Address <> fAdr
                    End If
                End If
            Next
        End If
    Next
End Sub

 

[Tom.Jones]

Hi JLGWhiz,

Thank for replay and for VBA code.
I would need a VBA code to show my information as I said in post 1, I did not write my location and connection to the file.
When I press the button to display as it is in my post # 1.
That way the files to be in column A, the files name will be listed in col B, col C the sheets names and cells that are found in the following columns D, E, ect.
Thank you.

 

[Tom.Jones]

bump

 

[Tom.Jones]

Nobody?