Search file

[Tom.Jones]

Hi,

It's possible to search and open file/s from name of sheet?
Need to type name of sheet and open file/s who have that sheet.

Thanks.

 

[Dan_W]

Hi
Yes, it is of course possible using the code I've already posted above. For completeness, I reproduce it below. Here is a workbook implementing the code.

It's a very simple example, and could do with better error handling etc. I've added a progress indicator to show where you're up to in the scan in case there are lots of files.

Option Explicit

Private Sub btnBrowse_Click()
    tbPath.Text = Replace(BrowseForFolder, "\\", "\")
End Sub

Private Sub btnScan_Click()
    Me.ProgressLabel.Caption = ""
    If Len(Me.tbPath.Text) And Len(Me.TBSheetName.Text) Then
        Dim FSO As Object, FSOFolder As Object, FSOItem As Object, SheetNames As String, Counter As Long
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set FSOFolder = FSO.Getfolder(tbPath.Text)
        For Each FSOItem In FSOFolder.Files
            DoEvents
            Counter = Counter + 1
            Me.ProgressLabel.Caption = "Processing " & Counter & " of " & FSOFolder.Files.Count & " files"
            If FSOItem.name Like "*.xls*" Then
                SheetNames = GetSheetNames(FSOItem.Path)
                If InStr(SheetNames, TBSheetName.Text) Then
                    Me.ListBox1.AddItem FSOItem.Path
                End If
            End If
        Next
    End If
    ProgressLabel.Caption = Me.ListBox1.ListCount & " files found"
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Application.Workbooks.Open ListBox1.Text
End Sub

Function BrowseForFolder(Optional ByVal Prompt As String = "Please select folder") As String
    BrowseForFolder = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 1, "").self.Path & "\"
End Function

Function GetSheetNames(ByVal FilePath As String) As String
    Dim Conn As Object, RecSet As Object
    Set Conn = CreateObject("ADODB.Connection")
    Set RecSet = CreateObject("ADODB.Recordset")
    On Error GoTo ErrHandler
    Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties=Excel 12.0 Macro;"
    Set RecSet = Conn.OpenSchema(20)        ' 20 = adSchemaTables
    While Not RecSet.EOF
        GetSheetNames = GetSheetNames & IIf(Trim(GetSheetNames) = "", "", ",") & RecSet("TABLE_NAME")
        RecSet.MoveNext
    Wend
    RecSet.Close
    Conn.Close
ErrHandler:
End Function

I've updated the GetSheetNames function to allow for XLSM files too.

Hope that helps.

 

[johnnyL]

@Dan_W shoot me an email when you get a chance.

 

[Tom.Jones]

Dan_W,

Excellent. Thank you so much.

If is not too much please, how can I modify code, to open excel file, to that specific sheet.
Any way, Excellent job.

 

[Dan_W]

Dan_W,

Excellent. Thank you so much.

If is not too much please, how can I modify code, to open excel file, to that specific sheet.
Any way, Excellent job.

Hi. Totally doable, but will have to take a look when I finish work today.

 

[Dan_W]

@Dan_W shoot me an email when you get a chance.

Emailed you!

 

[Joe4]

@Dan_W shoot me an email when you get a chance.

Emailed you!

Guys, please heed rule 4 here!

Message Board Rules

Along with the Terms of Use you agreed to follow when registering, please follow these rules.

www.mrexcel.com

Please be sure to post all solutions to the this thread!

 

[johnnyL]

@Joe4, I am unsure of how just wanting to catch up with someone, Shoot the $hit, so to speak, violates any site rules. Could you please explain?

 

[Joe4]

@Joe4, I am unsure of how just wanting to catch up with someone, Shoot the $hit, so to speak, violates any site rules. Could you please explain?

If it has nothing to do with the thread/question asked, then it really shouldn't be in this thread.

 

[johnnyL]

My apologies to MrExcel for wasting the server space. I guess I am guilty of that again by responding to you. Doh!

 

[Joe4]

My apologies to MrExcel for wasting the server space. I guess I am guilty of that again by responding to you. Doh!

The issue isn't eating up server space, more of "thread hijacking" (netiquette thing).
But why not just email him directly if all you want to do it catch up?