VBA - vlookup in multiple files

[hmaes]

Hi all

I'm looking for a way to do a vlookup in multiple files in a folder

The lookup value is in a master file in a fixed cell (C8)
The file were to look in are in a folder on my desktop called "test" The file names will change and will have a date in it (example: "KPI's 08-01-21.csv") Sheet name is equal to file name.
Files are CSV files

I would like to copy the entire row for all the files into the master file starting point is B13. next file vlookup should be in B14, .... If the value doesn't excists in the next file it shoul be skipped.

I started to look at

but can't seem to find a way to get the vlookup in.

Can someone help?

Many Thanks
Hans

 

[rollis13]

You can use this macro as a starting point to proceed with your project, have a try. Please check delimiter and master sheet name.

Option Explicit

Sub Import_Csv_Lines()

    Dim fPath  As String                          'source path
    Dim fName  As String                          'file name
    Dim tDlm   As String                          'text delimiter
    Dim lRead  As String                          'line read
    Dim lArray() As String                        'line field array
    Dim fld    As Long                            'field to compare
    Dim sRow   As Long                            'paste start row
    Dim wsh1   As Worksheet

    Set wsh1 = Sheets("Master")                   'sheet must exist or change as suited
    tDlm = ","                                    'change delimiter if required
    fPath = Environ("USERPROFILE") & "\Desktop\test\" 'trailing backslash needed
    fName = Dir(fPath & "*.csv")
    sRow = 13                                     'start from row 13
    While fName <> ""
        Open fPath & fName For Input As #1
        Do While Not EOF(1)
            Line Input #1, lRead
            lArray = Split(lRead, tDlm, -1, vbTextCompare)
            For fld = 0 To UBound(lArray)
                If lArray(fld) = wsh1.Range("C8").Value Then
                    wsh1.Cells(sRow, "B") = lRead 'paste entire line found in csv file
                    sRow = sRow + 1
                End If
            Next fld
        Loop
        Close #1
        fName = Dir
    Wend
   
End Sub

 

[hmaes]

Hi Rollis13

Many thanks for your reply! Worked like a charm.
I have an additional question. Not sure I need to create a new threath or not. If so please let me know and I will relocate the question.

I have a date that I need to copy from 1 WB to my master workbook. The format is 04/01/2021 (dd/mm/yyy)
When copy paste it with:

ThisWorkbook.Sheets("User Panel").Range("B" & Z) = ws.Range("J3")

I (Z is a counter) get a value returned like 20 or 19.
Is there a way to do the copy and keep the same date as the source?

 

[rollis13]

No, I don't think this can be done on-the-fly with my macro so I suggest you to create a new thread for this specific question unless you mean something like this in another macro:

ThisWorkbook.Sheets("User Panel").Range("B" & Z) = Format(ws.Range("J3"), "dd/mm/yyyy")