Search specific Column in various XL Files

[DMT Dave]

Hi Guy's, a bit of a long shot of a question, not started any VBA to tackle this but is it possible to achieve the following ?



A folder containing Excel files, there can be 5 or 30 Excel files

If want to find a specific string search that will always be in a specific column but my bug is which file name is it on

ie:

FileName1
FileName2
FileName3
FileName4

Etc....

We know it is column(G) for example, what file name has my search string found it in ?

Once this is achieved, i can use FollowHyperlink method to open the found file name

Currently, we will open each file to look

Hopefully explained this question correctly!!

Thanks guy's

[Welshgasman]

Just process each file in the folder.
Once opened, you will know it's name. You can search column G for your string.
If found, write the filename and row number(s) to a table.

Then process that table.
I am sure someone on here posted a DB that would process all files in a folder (even sub folders).

That wpuld be a good starting point.

[DMT Dave]

Hi WGM, thank you, found this ad trying to understand it based on sPath and sFile

https://www.mrexcel.com/board/thread...lder.1136692//

[DMT Dave]

Excel code is different to access though i believe, never tried any XL coding before

[Welshgasman]

Excel has a macro recorder though.
So you can records your steps, and then amend the vba to suit.

[CJ_London]

you can loop through the directory and use sql to inspect - something like this

Function findXLfile(fldr As String, phrase As String) As String
Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim fName As String
Dim sqlStr As String




    
    
    fName = Dir(fldr & "\*.xlsx")
    Set db = CurrentDb
    Do While fName <> ""


        sqlStr = "SELECT * FROM (SELECT count(*) as CT FROM [sheet1$G:G] AS xlData IN '" & fldr & "\" & fName & "'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes] WHERE G='" & phrase & "')  AS XL"
         'Debug.Print sqlStr
        Set rs = db.OpenRecordset(sqlStr)
        If rs!CT <> 0 Then Exit Do
        
        fName = Dir
        
    Loop
    findXLfile = fName


End Function

Edit:
Code assumes sheet1 for the sheet name, change as required.

May need additional code if a file is opened that does not have a column G (the sql opens a recordset based on the populated area of excel)

[DMT Dave]

Thank you CJ London, would i place the fldr path prior to fName in the function as you would in a private sub ?

or place fldr path in the private sub before shelling the function findXLfile ?

will take a closer look at your SQL line also

Thank you

[CJ_London]

would i place the fldr path prior to fName in the function as you would in a private sub ?

or place fldr path in the private sub before shelling the function findXLfile ?

fldr is a parameter of the function and the function returns the first file found based on the criteria. If you want, you can dim fldr and phrase as string or make them constants in which case you can remove them from the parameters.

My thinking was you would have code for a user to select a folder and state a phrase and then call the function. If the column can vary (the example is for column G) then I would add that to the function parameters so users can select a column as well (sqlStr would need to be modified)

[DMT Dave]

Ahh ok, understood, that's great advise and thank you for taking the time to explain, will follow up soon, thanks again

[CJ_London]

so user has specified the folder and the string value to be searched for

you run the function to find the file

if a file name is returned you now have what you need to open the file

How sure are you that the value searched for is unique across all the files? It may be you need to modify the function to return a list of files which contain the value (rather than the first one as currently designed - and as I am sure you know first does not mean earliest or order as presented in the navigation window) - which would affect what happens after the function has been run

[DMT Dave]

Hi CJ London, to answer the value being unique, there would ever be only 1 value per search, i a also getting Sub or Function Not defined!!!

I have your suggested function

Option Compare Database
Option Explicit


Function findXLfile(fldr As String, phrase As String) As String
Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim fName As String
Dim sqlStr As String
    
    fName = Dir(fldr & "\*.xlsx")
    Set db = CurrentDb
    Do While fName <> ""




        sqlStr = "SELECT * FROM (SELECT count(*) as CT FROM [sheet1$D:D] AS xlData IN '" & fldr & "\" & fName & "'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes] WHERE D='" & phrase & "')  AS XL"
         'Debug.Print sqlStr
        Set rs = db.OpenRecordset(sqlStr)
        If rs!ct <> 0 Then Exit Do
        
        fName = Dir
        
    Loop
    findXLfile = fName




End Function

Called from a test button

[DMT Dave]

Hi CJ London, I have some code that was searching for data on a specific Excel file, I have slightly changed the code i found from another database i have, tweaked it a little so the file can be any file in the XL folder.

I haven't tested this yet but here it is, can you see any flaws in this ?

Dim apXL As Object, xlWB As Object, xlWS As Object, rngRange As Range
Dim intLR As Integer, iRow As Integer, i As Integer, intID As Integer, intOrder As Integer
Dim sFile As String, sPath As String, strSearch As String


sPath = "T:\DMT Ltd\XL Files\"
sFile = Dir(sPath & "*.xlsx", vbNormal)




strSearch = InputBox("Paste Number Here ?")


Select Case strSearch


Case Is = False


Exit Sub


Case Else


Set apXL = CreateObject("Excel.Application")
Set xlWB = apXL.Workbooks.Open(sPath & sFile)
Set xlWS = xlWB.Worksheets("Sheet1")
apXL.Visible = False


Do While sFile <> ""


With xlWS
    intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To intLR
        If StrComp(xlWS.Range("D" & i).Value, strSearch, vbTextCompare) = 0 Then
            iRow = i
        Exit For
        End If
        Next i
End With


If iRow > 0 Then
    MsgBox (strSearch & " Is On File Name: " & sFile)
    xlWB.Close
    apXL.Quit
    Set apXL = Nothing
    
    Application.FollowHyperlink sPath & sFile
End If


If iRow = 0 Then
    MsgBox ("File Name: " & "Your Search Is NOT On Any XL Files")
xlWB.Close
apXL.Quit
Set apXL = Nothing
End If


Loop


End Select

[CJ_London]

I’m not really the one to ask as it is not my suggestion

Suggest document the code with what each bit of code is supposed to do.

it doesn’t appear to change anything so run it and see what happens.

[DMT Dave]

Thank you CJ London, just wondering why i would get sub r function not defined from your suggested Function.....

Kindest

[DMT Dave]

It is correct to have

Option Compare Database
Option Explicit

?