Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    Hi. I have a list of numbers in column A on the active worksheet. I need a code please that will open all the files within a folder on my desktop and tell me the name of the file each number in A is located. Thanks.

    Also posted here.

    https://www.mrexcel.com/forum/excel-questions/1005281-code-open-files-tell-me-name-file-where-data-located.html
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

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

    Any help anyone please?
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    Comment


    • #3
      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.?
      We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

      Comment


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

        Originally posted by KjBox View Post
        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?

        Correct

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

        Could be anywhere in the files

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

        More than likely just the one file, but could be in more

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

        In column B would be fine next to the number in A, if its in more than one file then column C and so on..


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

        C:\Users\manager\Desktop\New folder (2)
        Thanks all relevant answers above.
        ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

        To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

        The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

        Comment


        • #5
          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?
          We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

          Comment


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

            Originally posted by KjBox View Post
            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?
            There should be only one sheet but they will all be called different names in each file, if there is more than one sheet in a file only the first will need searching if that is possible.
            ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

            To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

            The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

            Comment


            • #7
              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.
              We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

              Comment


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

                Ok, thanks.
                ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

                To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

                The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

                Comment


                • #9
                  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?
                  We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                  Comment


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

                    Originally posted by KjBox View Post
                    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?
                    Yes the data starts in a2 and each file could be about 10000 rows by 30 columns.
                    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

                    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

                    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

                    Comment


                    • #11
                      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.

                      Code:
                      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.
                      We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                      Comment


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

                        Thanks. As soon as I run it I get a runtime error '9' subscript out of range. When I debug it points to If Not IsEmpty(Files(1)) Then
                        ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                        Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

                        To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

                        The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

                        Comment


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

                          Try changing that line to
                          Code:
                          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
                          Code:
                          sPath = "C:\Users\manager\Desktop\New folder(2)\"
                          Note the "" at the end, that must be included.
                          We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                          Comment


                          • #14
                            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).
                            Code:
                            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.
                            We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                            Comment


                            • #15


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

                              Ok there was an error in the path in your code, there wasnt a space between folder and (2). The code began to run but then there was another error pointing to

                              If Not IsEmpty(y(i, UBound(y, 2))) Then
                              ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                              Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!

                              To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

                              The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

                              Comment

                              Working...
                              X