Loop through multiple files to get values?

[osscie3]

Hi all,

I think this will be most efficient doing this through VBA but feel free to suggest otherwise (Access, etc).

I need to loop through 68 different excel files (all named appropriately with date at the end - ie - Datasheet1018, Datasheet1118, Datasheet1218) and grab 2 values from each Excel file based off the primary key of the master database. Essentially I need to do a vlookup on multiple Excel files.

For instance my master database looks like this:

Acct. Number	Oct-2018	Nov-2018	Dec-2018
1234	3,456

<tbody>
</tbody>

I need to get the values for November and December in their respective Excel files based on the account number 1234.

What's the best way to go about tackling this?

 

[DanteAmor]

Only one detail, according to the example, exactly what do you have in cell B1: 07/13 or jul/13 or 07/01/2013 ?, I need to know exactly what you have, is a text or a date, since the file says 0713 but in the cell you surely have something else.

Forget the date, I put it according to each file.
Work in the macro

 

[DanteAmor]

Go the macro.

Before you should create a sheet called "Temp", on that sheet I need to sort the files to read from the oldest date to the most recent.

Sub Get_Values()
'
    Application.ScreenUpdating = False
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("Shares 2019 Study")
    Set h2 = l1.Sheets("Temp")
    '
    h2.Range("A1:C1").Value = Array("ARCH", "PER", "NUM")
    h2.Rows("2:" & Rows.Count).ClearContents
    ruta = "C:\Users\me\Desktop\2019 Decay Rate Study\DB's\Shares"
    'ruta = "C:\trabajo\shares\"
    If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
    '
    'Read all files from folder
    j = 2
    arch = Dir(ruta & "*.csv")
    Do While arch <> ""
        h2.Cells(j, "A").Value = arch
        h2.Cells(j, "B").Value = "'" & Left(Right(arch, 8), 4)
        h2.Cells(j, "C").Value = Val(Left(Right(arch, 8), 4))
        j = j + 1
        arch = Dir()
    Loop
    '
    'Sort files by date
    u2 = h2.Range("A" & Rows.Count).End(xlUp).Row
    With h2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=h2.Range("C2:C" & u2), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange h2.Range("A1:C" & u2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    '
    'Get values
    lcol = u2 + 1
    h1.Cells(1, lcol).Value = "Date Closed"
    u1 = h1.Range("A" & Rows.Count).End(xlUp).Row
    k = 2
    For i = 2 To u2
        Set l3 = Workbooks.Open(ruta & h2.Cells(i, "A").Value)
        Set h3 = l3.Sheets(1)
        h1.Cells(1, k).Value = "'" & h2.Cells(i, "B").Value
        For j = 2 To u1
            cuenta = h1.Cells(j, "A").Value
            Set b = h3.Columns("A").Find(cuenta, lookat:=xlWhole)
            If Not b Is Nothing Then
                h1.Cells(j, k).Value = h3.Cells(b.Row, "B").Value
                h1.Cells(j, lcol).Value = h3.Cells(b.Row, "E").Value
            End If
        Next
        k = k + 1
        l3.Close False
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Try and tell me.

 

[osscie3]

Forget the date, I put it according to each file.
Work in the macro

Oh it's just a simple column heading that I type in for reference. Macro shouldn't have to key in on any of that.

 

[DanteAmor]

Do not worry the macro will put it, just try the new macro.

 

[osscie3]

This worked beautifully. Thank you so much. There were some columns that didn't pull data over but after inspecting those files, it looks like they weren't formatted correctly so I'm formatting them now as we speak. Will run again once I get all the files correctly formatted.

 

[DanteAmor]

Im glad to help you