Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Headers VBA

0

Hello! I took this program from this video and I have a question.

Fast Search Multiple Excel Workbooks using a File Picker Window - YouTube

How can I modify it so that it also returns me the headers from each excel? I mean if I search a name, it goes through multiple files, and returns me the headers and data from the first file, then the headers and data from the next file and so on.

Answer
Discuss

Answers

0
Selected Answer

Squishy

Don's tutorial was meant for files with similar formats (same column headers dta in same columns. In the file attached, I've modified the code so it:

  1. adds a blank row per worksheet searched
  2. puts the worksheet name and the file name/path in that row (so you know where the data is from)
  3. put the headers from that sheet in the next row then the all the found data (as the tutorial code did).

The code extract below shows the code portions I've changed. In essence, I:

  1. changed the dataRowStart constant from 2 to 1 (so it gets the first row of each sheet)
  2. made the maximum array size 2 rows larger to allow for the new filename and header rows
  3. re-used the counter k to loop through the dataRowStart row (i.e. the headers)
  4. put any found data matches in two rows lower (than the tutorial code).

See changes in bold below:

Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook)
' TeachExcel.com
....
'Data table information
dataColumnStart = 1
dataColumnEnd = 15
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 1
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
....

        'Increase size of array that will hold the data to display to its max possible size for the current worksheet.
        ReDim datatoShowArray(1 To UBound(dataArray, 1) + 2, 1 To UBound(dataArray, 2) )

        'Row increment for the final data array (datatoShowArray).
        j = 1

        'Loop through the headers for each file
        For k = 1 To UBound(dataArray, 2)

            'Add blank row...
            datatoShowArray(1, k) = ""
            ' then path/ file /sheet name...
            datatoShowArray(1, 1) = "From file: " & wbSlave.Path & "\" & wbSlave.Name & " - " & ws.Name
            'then header values to the array to put into the Dashboard.
            datatoShowArray(2, k) = dataArray(1, k)

        Next k

        'Loop through the rows in the data range.

.
......                
                'MATCH FOUND! Now do something!

                'Loop through the columns in the data range so can get their values.
                For k = 1 To UBound(dataArray, 2)

                    'Add values to the array that will be used to put data into the Dashboard.
                    datatoShowArray(j + 2, k) = dataArray(i, k)

                Next k
....
Hope this works for you.
Discuss

Discussion

Squishy. Did you have problems using my solution? 
John_Ru (rep: 3657) Nov 30, '21 at 4:20 am
It helps me a lot!! Thank you so much! Do you also know how can I replace this part
dataColumnStart = 1
dataColumnEnd = 15
with something that goes through all columns in an excel? some of mine have 40 columns, some 43 etc. And I would like something that automatically goes through all.
Squishy (rep: 18) Dec 2, '21 at 2:41 am
I would also attach at my question a file with my code. I've done a few modifications, and it's almost finished. If you can help me optimize it, it would be perfect. I added a FOR that goes through columns, you'll see there. Basically, now it returns the headers and all I need, but at some names it doesn't. I think I have some error, so maybe you'll be able to see what I can't :D
Squishy (rep: 18) Dec 2, '21 at 2:44 am
Squishy. I'll look later today but notice that you selected my Answer then changed that when you had further questions (in the Discussion points above). Please note that extending a question (which was answered fully) is generally frowned upon in the Forum - remember this is a Q&A forum, not a free code optimizing service. 
John_Ru (rep: 3657) Dec 2, '21 at 3:14 am
Yes, I thought if I select it, you won't be able to help me further. Thank you so much for your help, and I'll wait for your response later. Don't worry, I'll select your answer, because you did helped me a lot! I look forward for your opinion!
Squishy (rep: 18) Dec 2, '21 at 3:45 am
Squishy

You should be able to get help on a new question but it's a bit annoying for contributors when they give a full answer and the user realises their original question wasn't enough.

Tried your file and couldn't get it to work. I looked at your code briefly and see you deleted all the guidance comments (not a good idea if you want you or others to understand it in the future!) and changed some of the counter variables/loops- you failed to notice that (my) k was used in two separate loops (to add blank/file name rows then seaparately to loop through Field headers) but you tried to combine them. 

My Answer code works (as the original but returning file info and headers) with more than 15 columns if you simply increase the variable 
dataColumnEnd = 43
(and returns the extra columns when they appear in a sheet) but your confused code looks like you're trying to search several files for the value regardless of what column it's i (rather than the column names as the Field Name). If so, that's definitely a separate question. 
John_Ru (rep: 3657) Dec 2, '21 at 5:19 am
If however your code works for you but fails for "some names", it might be that the data cells have extra spaces or different capitalision c.f. the search value. You can get around that my making both values lower case and removing the leading/trailing spaces by changing these two lines (in your code):
searchValue = Trim(Lcase(Range("F6").Value))
....
If Trim(Lcase(dataArray(k, m))) = searchValue Then
John_Ru (rep: 3657) Dec 2, '21 at 5:19 am
I understand. It did helped too. Thanks for your help and sorry for the trouble. I'll close the topic. Thank you and have a wonderful day!
Squishy (rep: 18) Dec 2, '21 at 6:12 am
Thanks Squishy. Please be sure to come back and pose other questions
John_Ru (rep: 3657) Dec 2, '21 at 6:14 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login