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.

 

[Joe4]

Need to type name of sheet and open file/s who have that sheet.

From where? A specific directory? Is that indicated somewhere?

I don't think Excel can know the names of the sheets in files without opening each file.
So I think you would have to open every file and then check to see if that sheet name exists in that file.

 

[Dan_W]

It is possible to access the sheet names of a closed workbook, but you need to use VBA (ADODB) in order to do it. Would you be open to a VBA based solution?

 

[Joe4]

It is possible to access the sheet names of a closed workbook, but you need to use VBA (ADODB) in order to do it. Would you be open to a VBA based solution?

Dan, I would like to see how it could be done on closed workbooks, for my own knowledge, if you don't mind!

 

[Tom.Jones]

Thank you for trying to help me.
I like to choose folder, then search for sheet name. If is only one sheet then show the file and the sheet. If are many sheets in many file then show them (list them), and after that we choose the file we want.
Easy for me would be an userform, to browse for folders, in a text box, then to show files (with sheet we search) in listbox. Then click file name and open it.
I would like both way if possible (but with closed worbook will be a bonus).

Thank you.

 

[Dan_W]

Dan, I would like to see how it could be done on closed workbooks, for my own knowledge, if you don't mind!

@Joe4 - Certainly! ADO isn't my strong suit, but I have long used the following to extract the sheet names from workbooks:

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")
    Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties=Excel 8.0;"
    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
End Function

And you can run it as follows:

Sub Test()
    Dim Filename As String
    ' Assume a simple workbook with two worksheets - Sheet1, Sheet2
    Filename = "D:\test.xlsx"
    Debug.Print GetSheetNames(Filename)
    ' Output into the immediate window: Sheet1$,Sheet2$
End Sub

Assuming a workbook comprising, say, two worksheets - Sheet1 and Sheet2 - the output will be Sheet1$,Sheet2$. From this point it is a simple string manipulation to remove the dollar signs, convert it to an array, etc etc., as required. I should stress that I'm not the author of the routine above (I don't know who is) - I've simply had it sitting in my code snippet directory for years!

 

[Dan_W]

I just had another thought - you could easily get the sheet names from the workbook as a ZIP file - there are two ways you could do this - one is a method that JohnnyL and I worked on a while back that uses the Shell interface to cycle through the Sheet XML files (which is a bit slow). The quicker way would be to use the pure VBA ZIP functions developed by Cristian Buse (GitHub - cristianbuse/Excel-ZipTools: Interact with the component parts of an Excel file), and discussed with Jaafar here: Dynamic File - Last Saved by

 

[Joe4]

@Joe4 - Certainly! ADO isn't my strong suit, but I have long used the following to extract the sheet names from workbooks:

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")
    Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & ";Extended Properties=Excel 8.0;"
    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
End Function

And you can run it as follows:

Sub Test()
    Dim Filename As String
    ' Assume a simple workbook with two worksheets - Sheet1, Sheet2
    Filename = "D:\test.xlsx"
    Debug.Print GetSheetNames(Filename)
    ' Output into the immediate window: Sheet1$,Sheet2$
End Sub

Assuming a workbook comprising, say, two worksheets - Sheet1 and Sheet2 - the output will be Sheet1$,Sheet2$. From this point it is a simple string manipulation to remove the dollar signs, convert it to an array, etc etc., as required. I should stress that I'm not the author of the routine above (I don't know who is) - I've simply had it sitting in my code snippet directory for years!

Cool!

So if they have a whole directory of files to work though, they will just need to loop through them and feed each file name to the code, and write the results back.

 

[Dan_W]

Cool!

So if they have a whole directory of files to work though, they will just need to loop through them and feed each file name to the code, and write the results back.

Yes, I think that's right! Unless I'm missing something, it should be pretty straight forward.

I have my favourite "Browse for Folder" function here too if that helps you @Tom.Jones:

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

which you call with something like:

TargetPath = BrowseForFolder()

 

[Tom.Jones]

Thank you for your help.

I don't need to see the names of the sheets, in a file.
I would need to type the name of the sheet (in the cell or in the TextBox in the UserForm) - let's say - "books" (without the quotes) and I would like all the files that contain the "books" sheet to be listed in the sheet or in the ListBox in UserForm.
Double-click on the file name to open the file on the desired page.

Is such a thing possible?
Thank you.