Search and match data from multiple workbooks

[sheexin]

Hello!

I am really new with VBA and have been trying to solve this problem for ages, but to no avail. I use a Mac Excel 2011. Would appreciate any help or advice!

Essentially: I have a master folder of multiple excel files which have ID numbers, and various data such as Name, Description, Price and so on. The information is allocated in a table format, so column A has ID numbers, B has Names, etc. I would like to create a new excel file outside the folder which would allow me to key in ID numbers into a certain column. The VBA should match the ID with those in the master folder, and allow me to extract the corresponding and necessary information accordingly.

1. Take 1 ID at a time from the list in Book1.xls
2. Search the entire master folder, each file, column ____ until it finds a match.
3. Once it finds a match, I can select copy columns ____ from matching workbook to ____ of book1.xls.
4. Stop searching and go back to the list and get the next item to search again.

I have found someone with a similar problem before on this forum, but I can't seem to run the VBA. The thread can be found here: www(dot)vbaexpress(dot)com/forum/showthread.php?10772-Solved-Difficult-Macro-to-search-multiple-workbooks-for-a-match

and the code used:


Sub SearchandDostuff()


Application.ScreenUpdating = False
Application.DisplayAlerts = False


'dimension variables
Dim wb As Workbook, wbCheck As Workbook, strID As String
Dim wsTO As Worksheet, wsFROM As Worksheet, i As Long, pos As Long
Dim folder As String, file As String, Path As String
Dim c As Range, rngMatch As Range, a As Range, rngFrom As Range
Dim Matched As Boolean, ID As Range, counter As Integer


'folder to loop through
folder = BrowseForFolder


'set destination info
Set wsTO = Workbooks("Book1.xls").Sheets("Sheet1") '<<== name your file and sheet


'set the range list, can be re-written to Range("C2:C100") and set to fit
With wsTO
Set rngMatch = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)
End With
MsgBox rngMatch.Address
For Each ID In rngMatch


Matched = False
'Start FileSearch
With Application.FileSearch
.LookIn = folder
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = False
.Execute
If .Execute > 0 Then
'loop through all found files
For i = 1 To .FoundFiles.Count
'set incidental variables
pos = InStrRev(.FoundFiles(i), "\")
file = Right(.FoundFiles(i), Len(.FoundFiles(i)) - pos)
Path = Left(.FoundFiles(i), pos)


'check if workbook is open. if so, set variable to it, else open it
If IsWbOpen(file) Then
Set wb = Workbooks(file)
If wb.Name = ThisWorkbook.Name Then GoTo SkipME
Else
Set wb = Workbooks.Open(Path & file)
End If


Dim shchk As Boolean
shchk = False
With wb


'set worksheets to look in and if matched copy data from S,T to D,E
'find the sheet with SET in the left side of the name
For Each s In Worksheets
If UCase(Left(s.Name, 3)) = "SET" Then
Set wsFROM = Sheets(s.Name)
shchk = True
Exit For
End If
Next s


End With
If shchk = False Then GoTo SkipME
strID = ID.Text
Set rngFrom = wsFROM.Range(Cells(2, 11), Cells(Rows.Count, 11).End(xlUp))
''check for the ID column?
Set c = rngFrom.Find(strID) 'if a match is found we have the right workbook
If Not c Is Nothing Then 'if a match is found
'copy data from S and T to D and E
'set the range of column C for source list to find
ID.Offset(, 1).Resize(1, 2) = c.Offset(, 8).Resize(1, 2).Value
Matched = True


End If 'end If for the first check
If Matched = True Then Exit For 'if a match is found, exit file search loop
SkipME:


wb.Close False
Next i 'next file
End If


End With 'end with application filesearch
Next ID 'go to the next ID




Set wsTO = Nothing: Set wsFROM = Nothing: Set a = Nothing
Set c = Nothing: Set wb = Nothing


Application.ScreenUpdating = True
Application.DisplayAlerts = True




End Sub
Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function


Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'''Code from kpuls, Knowledge base submission


Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)


On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0


Set ShellApp = Nothing


Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select


Exit Function
Invalid:


End Function


Whenever I run the code, it shows run time error 429, Active X component can't create object and this is highlighted:
Set ShellApp = CreateObject("Shell.Application"). _BrowseForFolder(0, "Please choose a folder", 0, OpenAt)


I would appreciate it if anyone who creates the code could explain it to me as well, in case I need to do some tweaking! Thanks!

[excelliot]

did you found solution to it?

[SamT]

Solved: Difficult... Macro to search multiple workbooks for a match? (http://www.vbaexpress.com/forum/showthread.php?10772-Solved-Difficult-Macro-to-search-multiple-workbooks-for-a-match)

But that code can't possibly match your data structure.


Essentially: I have a master folder of multiple excel files [Q4 below] which have ID numbers, and various data such as Name, Description, Price and so on. The information is allocated in a table format, so column A has ID numbers, B has Names, etc. I would like to create a new excel file outside the folder which would allow me to key in ID numbers into a certain column. The VBA should match the ID with those in the master folder, and allow me to extract the corresponding and necessary information accordingly.

1. Take 1 ID at a time from the list in Book1.xls
2. Search the entire master folder, each file, column ____ until it finds a match.
3. Once it finds a match, I can select copy columns ____ from matching workbook to ____ of book1.xls.
4. Stop searching and go back to the list and get the next item to search again.

1)Is book1.xls the name of the Excel File you're asking for?
2) Which Column
3) Copy Which columns to where
4)What kind of "Excel Files" ? csv, txt, xls. xlsx, xlsm. xlsb ? If appropriate, what sheet names are you interested in?

Can you upload an example of the "Excel Files" we will be working on? Note that you will need to have at least (IIRC) 3 posts to use the Advanced Editor Attachments function.

[sheexin]

Right, will upload the files!

[sheexin]

With regards to the question
1)Is book1.xls the name of the Excel File you're asking for? I'm using a sample file called New which is where I would be extracting information to. Files 1, 2 and 3 are like my database which contains all the information which I want to extract.
2) Which Column ID numbers are in column A. Name in B, Description in C. This is a sample so there's not a lot of information but there may be more columns to copy from. If possible, I want to create an Input Box which allows me to select which column of information to copy from. If that's too difficult, I just need the row of information.
3) Copy Which columns to where From the database excels sheets 1,2,3 to New. All database files would be in 1 folder. New may or may not be in that folder, does it matter?
4)What kind of "Excel Files" ? csv, txt, xls. xlsx, xlsm. xlsb ? If appropriate, what sheet names are you interested in? The files would most likely be in xlsx and xlsm, but hopefully we can make VBA flexible such that it includes files with xls* (though I know Excel Mac does not support the * function). The information should be on Sheet 1, so it wouldn't matter.

[sheexin]

13761137621376313764

[SamT]

So what you need is a basic framework that you can then code yourself for all the details?

I have attached a suggested layout for the databases. Note especially the differences in cell formatting of the Header Row and the data rows. This difference is strongly suggested to help Excel and VBA determine the header row.

I would also suggest that all the code and forms be in an xlsm file, with one or more "New file" templates to choose from.

IMO, the easiest solution to code and use is a UserForm in the xlsm workbook with various controls to select the database, Items and various additional Data Fields (Columns).

In VBA, Right Click in the Project Explorer on the Project name you are working on and Select "Insert >> UserForm"

I am imagining that the UserForm would have a ListBox Control or OptionButton Controls to select a DataBase file, a MultiSelect, MutliColumn ListBox to select the Items and a list box or option buttons to select various additional Data Fields to include, and of course, CommandButtons for "Add Record(s)," "New," and "Close"

[sheexin]

Thanks SamT for the suggestion! Yes, I can format the information according to what you've attached.

However, from what I know of UserForms, they seem to be suitable for inputting data to create a database? What I would like is to extract information from a folder of excel workbooks using an ID number, instead of inputting data. I could be completely wrong, so please advise!

[SamT]

A VBA UserForm can do anything. It is only a GUI to the code.


Random Quotes:
What I would like is to extract information from a folder of excel workbooks using an ID number...
I want to create an Input Box which allows me to select which column of information to copy from...

1. Take 1 ID at a time from the list in Book1.xls
2. Search the entire master folder, each file, column _ID___ until it finds a match.
3. Once it finds a match, I can select copy columns ____ from matching workbook to ____ of book1.xls.
4. Stop searching and go back to the list and get the next item to search again.

Assuming Static Directories, (Folders) that can be done with out a UserForm. What If you just selected an ID and all the fields (columns) of data you wanted at once, then ran the code?

The output can be formatted in any way you can imagine in Excel, Word, Outlook or any other MS Office application. Depends on how complex of a code structure you can live with. At a certain complexity, A UserForm is the best solution, simply for ease of Use and maintaining the code.

In order for us to start coding, Prepare a workbook with Sheet1 named Master, (with sufficient rows to cover most of the IDs in the three Database sheets.) Sheets 2 thru 4 with five rows from each database file, (+ the top row of column names,) Named the same as the database files. Please format the DataBase files as suggested above. And Sheet 5, named Result, formatted as you want, using Field names in place of any actual data.

If the actual database sheets are stored as CSV files, the code can very quickly search all DBs for any ID without out first selecting a particular DB. I don't know the significance of separate DB's, so this is your call. Note that CSV files are very easy to search and extract data from.