Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Can you please clarify what you are trying to do.

You have a list of numbers and you want to search all files in a folder to see which number is located in which file?

Will the required number always be in the same cell in all the files, or could it be anywhere?

Can each number in the list be in only 1 of the files, or could it be in none or more than 1?

How do you want the result to appear, in a message box, displayed on a sheet, if the latter what sheet and range?

What is the name of the folder on your desktop and ,ideally, the full path to that folder.?

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Is there just one sheet in each of the files? If more than 1 do all sheets need to be searched or will the number be somewhere on a particular sheet, if so what is the sheet name?

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Working on a solution for you, will post as soon as done, but I have other commitments too. Maybe tomorrow when done.

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

I have some code ready, but it would help if you could answer the following

On the sheet that has the list of numbers, is there a header in cell A1 and the number list starts in A2, or is there just a list starting in A1?

With the files that are to be searched what is the maximum likely number of columns and rows of data?

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Try this. You could add a button to your sheet that has the Number List and assign the "FileSearch" macro to that button.

All these macros should be placed in the same standard module.

The workbook that has the Number List can be either put in the same folder as the files that have to be searched, or anywhere else. If in the same folder then it will be excluded from the files that get searched.

The code will search the entire used range of the first sheet without needing to open each file.

Note there are 2 places in the code where you will need to change the code to reflect the actual name of the sheet that contains the number list.

Option Explicit
Dim x
 
Sub FileSearch()
    Dim Files() As String, sPath As String, sFiles As String, iv As Long, v As Integer
    Dim y(), z, i As Long, ii As Long, iii As Long, sSht As String, sAdd As String
     
    sPath = "C:\Users\manager\Desktop\New folder(2)\"
    sFiles = Dir(sPath & "*.xl*")
    z = Application.Transpose(Sheets("Sheet1").Columns(1).SpecialCells(2)) '// Change sheet name to suit.
    For ii = LBound(z) To UBound(z)
        z(ii) = CStr(z(ii))
    Next
    ReDim Preserve y(1 To UBound(z) - 1, 1 To 1)
    
    Do While sFiles <> ""
        i = i + 1
        ReDim Preserve Files(1 To i)
        Files(i) = sFiles
        sFiles = Dir()
    Loop
    
    If Not IsEmpty(Files(1)) Then
        Application.ScreenUpdating = 0
        For ii = LBound(Files) To UBound(Files)
            If Files(ii) <> ThisWorkbook.Name Then
                GetData sPath & Files(ii), GetFirstSheetName(sPath & Files(ii)), "A1:AM5000"
                For iii = 0 To UBound(x, 1)
                    For iv = 0 To UBound(x, 2)
                        If Not IsNull(x(iii, iv)) Then
                            If Not IsError(Application.Match(CStr(x(iii, iv)), z, 0)) Then
                                i = Application.Match(CStr(x(iii, iv)), z, 0) - 1
                                If Not IsEmpty(y(i, UBound(y, 2))) Then ReDim Preserve y(1 To UBound(y, 1), 1 To UBound(y, 2) + 1)
                                For v = 1 To UBound(y, 2)
                                    If IsEmpty(y(i, v)) Then
                                        y(i, v) = Files(ii)
                                        Exit For
                                    End If
                                Next
                            End If
                        End If
                    Next
                Next
            End If
        Next
    End If
    
    With Sheets("Sheet1").[b2] '// Change sheet name to suit
        .Resize(5000, 10).Clear
        .Resize(UBound(y, 1), UBound(y, 2)) = y
        .Parent.Columns(2).Resize(, UBound(y, 2)).AutoFit
    End With


End Sub
 
Public Sub GetData(sFile As Variant, sSht As String, sRng As String)
    Dim oCon As Object, oRS As Object, sCon As String, sSQL As String
     
    If Val(Application.Version) < 12 Then
        sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sFile & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No"";"
    Else
        sCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & sFile & ";" & _
        "Extended Properties=""Excel 12.0;HDR=No"";"
    End If
    sSQL = "SELECT * FROM [" & sSht$ & "$" & sRng$ & "];"
    Set oCon = CreateObject("ADODB.Connection")
    Set oRS = CreateObject("ADODB.Recordset")
     
    oCon.Open sCon
    oRS.Open sSQL, oCon, 0, 1, 1
    x = oRS.GetRows
     
    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
     
End Sub


Function GetFirstSheetName(sFile As String) As String
    Dim oShts As Object
    Set oShts = GetObject(sFile).Worksheets
    
    GetFirstSheetName = oShts.Item(1).Name
    
End Function

The code will search the first worksheet of all the files in the folder (except the number list file if it is in the same folder) and record the file(s), if any, that contain each number in columns B onwards for each number in the list.

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

Try changing that line to

If i > 0 Then

If you still get the error then it could be that the path to the folder that contains the files is not the path you gave me earlier.

Check the path to the folder and modify this line if necessary

sPath = "C:\Users\manager\Desktop\New folder(2)\"

Note the "" at the end, that must be included.

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

If the file with the number list is kept in the same folder as the files to be searched then you can use this (the path does not need to be hard coded, so is dynamic and code will not need changing if the folder is moved or renamed).

sPath = ThisWorkbook.Path & Application.PathSeparator

If you use this method ensure that the number list file is saved to the correct folder and opened from there before testing the code.

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

The code ran perfectly with some test files I created.

Can you attach your number list file and a couple of files that need to be searched. Much better to test on real situations rather than on what I think the files will be like!
[sw]*[/sw]

Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.

I also ran it on the files you sent and all was OK.

Yes that could well be the reason for the repeated file names. I did not take multiple entries of the same number in any single sheet into account as I thought it would not happen. Do you want me to amend the code to check for multiple instances of the number in a sheet and then stop the file name being repeated?