Vlookup across multiple workbooks

1

Hello!


I have 17 workbooks, say, 1.xlsx to 17.xlsx., each coming from a different source (with regular update) containing around 100 serial numbers with corresponding values. While populating a new workbook, each S. N. needs opening a file, checking through it and copying its value.


Could there be an easier way?


Thank you!

Answer
Discuss

Discussion

Do the values change often? If not, you could import all values into a master file and then just search that file using vlookup. 
don (rep: 1412) Jan 20, '18 at 8:29 am
The S. N. is updated time to time that disturbs if a master file is created.
Chhabi Acharya (rep: 78) Jan 20, '18 at 11:19 am
Add to Discussion

Answers

1
Selected Answer

Here is one I just threw together.

Disclaimer: test everything on test files first! I combined some macros to save time so it doesn't look pretty but it should work.

Sub VlookupMultipleWorkbooks()

Dim xDirect, xFname
Dim Dest As Range
Dim Tbl_Array As Range

Application.ScreenUpdating = False


On Error Resume Next

'Change this to put the data in a different place in the workbook.
Set Dest = ActiveCell.Offset(0, 1)

Look_Value = ActiveCell.Value


With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a folder"
.Show
    If .SelectedItems.Count <> 0 Then
        xDirect = .SelectedItems(1) & "\"
        xFname = Dir(xDirect, 7)

        Do While xFname <> ""

            If (xFname Like "*test*") = True And Not (xFname Like ThisWorkbook.Name) Then

                Set wb = Workbooks.Open(xDirect & xFname)

                For Each wSheet In wb.Worksheets

                    With wSheet

                        vFound = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 2, False)

                        If Not vFound = vbNullString Then

                            Dest.Value = vFound

                            wb.Close SaveChanges:=False

                            Application.ScreenUpdating = True

                            Exit Sub

                        End If

                    End With

                Next wSheet

                wb.Close SaveChanges:=False

                xFname = Dir

            Else

                xFname = Dir

            End If

        Loop

    End If
End With

Application.ScreenUpdating = True

End Sub

The cell that is selected in the current workbook is what will be used as the lookup value.

"*test*" this part says which files to go through in the directory that you will select. In this case, each file with the word test in it will be searched.

Range("A1:B10") is the lookup range, just like for a regular vlookup. Change this range reference to whatever you need it to be. The way this macro works, is that the lookup range, this range here, must be in the same location for every workbook that you are searching.

The next value after the range, 2, is the column lookup value for the vlookup function. It works like it does for the regular vlookup function in Excel - 2 means to get values from the second column of the lookup range.

I think that that's all you have to change. When the macro runs, you select the directory that contains the files to search through.

Hardcoded Directory Path

Sub VlookupMultipleWorkbooks_HardCoded_Directory()

Dim fPath, xFname
Dim Dest As Range
Dim Tbl_Array As Range

Application.ScreenUpdating = False


On Error Resume Next

'Change this to put the data in a different place in the workbook.
Set Dest = ActiveCell.Offset(0, 1)

Look_Value = ActiveCell.Value


fPath = "C:\Your Directory Path\"
xFname = Dir(fPath)

Do While xFname <> ""

    If (xFname Like "*test*") = True And Not (xFname Like ThisWorkbook.Name) Then

        Set wb = Workbooks.Open(fPath & xFname)

        For Each wSheet In wb.Worksheets

            With wSheet

                vFound = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 2, False)

                If Not vFound = vbNullString Then

                    Dest.Value = vFound

                    wb.Close SaveChanges:=False

                    Application.ScreenUpdating = True

                    Exit Sub

                End If

            End With

        Next wSheet

        wb.Close SaveChanges:=False

        xFname = Dir

    Else

        xFname = Dir

    End If

Loop



Application.ScreenUpdating = True

End Sub
Discuss

Discussion

Hello!
  I found the codes great but the result is only one value for one S. N. Could it be modified so as to insert values from multiple cells in the same row? Moreover, setting a direct path to a folder (instead of choosing it each time) would shorten the process. My efforts with little knowledge just worsened the condition.
I really appreciate your hard labor and help to me.
Chhabi Acharya (rep: 78) Jan 22, '18 at 12:31 pm
Basically, anything can be done lol. But, you need to give a more specific example of what values you mean, do you want to pull in multiple values for a single SN and then multiple SN's at the same time or what exactly? The above macro currently works like a single vlookup formula, basically.
don (rep: 1412) Jan 22, '18 at 12:57 pm
Sorry to trouble with my annoying queries. I wonder if we could call the values of more than one cell for one S. N., e.g. bringing values in B1, C1, D1, etc. for the S.N. in A1.
I am unaware of the power of the codes you can weave together. So my query may appear clumsy. I think you've got the gist (as stated in your reply),  so whatever help you provide to save time and minimize effort is heartily weicome.
Chhabi Acharya (rep: 78) Jan 23, '18 at 9:01 am
Where you see this line:
Set Dest = ActiveCell.Offset(0, 1)

Add new lines like this under it:
Set Dest2 = ActiveCell.Offset(0, 2)
Notice a 2 was added after Dest and the 1 was changed to a 2.

Where you see this line:
vFound = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 2, False)

Add new lines like this under it:
vFound2 = WorksheetFunction.VLookup(Look_Value, Range("A1:B10"), 3, False)
A 2 was added after vFound and the 2 was changed to a 3.

Where you see this line:
Dest.Value = vFound

Add new lines like this under it:
Dest2.Value = vFound2
Dest was changed to Dest2 and vFound was changed to vFound2.

Follow this pattern and add a new line of code in all 3 places for each new value that you want to get.

Try this and tell me how it works.
don (rep: 1412) Jan 23, '18 at 2:54 pm
Following your suggestion along with a change in ("A1:B10") as ("A1:J10"), and setting a folder as a default path, the codes accomplished my wish and thank you very much for that.

If I'm not mistaken, the file with this code can call data from source files if the cell adjacent to the S. N. is not empty. I mean, if B1 is empty, the values of C1, D1, etc. could not be imported. However, blanks in any other cell did not show this behaviour. I hope you'll surely suggest me about it.
Chhabi Acharya (rep: 78) Jan 25, '18 at 10:46 am
Sorry, forgot about the default folder path. As for the other thing you mention, I don't think it matters if there is no value for B1, etc. Each vlookup works independently so that that if one can't return a value, that shouldn't affect the others. Is that what you mean?
don (rep: 1412) Jan 26, '18 at 5:41 am
   For the time being, I inserted Dest1 as well that calls S.N. (in the source file into the target file)  itself as the first value in the row, so no problem if column B is empty.
However, I was not successful in inserting default path code although it seemed to work for a short time and then I lost somewhere.

I changed the following codes


With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a folder"
.Show


into
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath ="D:\multiple files"


And it worked fine until the file was closed and reopened. To function it again, I > add .show > save > delete the added word > save.
Could you suggest me, please?
Chhabi Acharya (rep: 78) Feb 2, '18 at 5:45 am
I took another look at the code for the directory path and changed it for you. Enough code had to be changed that I just uploaded the new macro in the answer above.

Let me know if this solves the issue.
don (rep: 1412) Feb 8, '18 at 5:43 am
Thank you again for your updated answer. It is a magic!
Chhabi Acharya (rep: 78) Feb 12, '18 at 11:52 am
Glad it worked :)
don (rep: 1412) Feb 13, '18 at 8:18 am
Add to Discussion

Answer the Question

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