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]

Several data are missing to complete the VBA code:
- name sheet master book
- column with master book account
- column with Nov-2018 from the master book
- sheet name book Datasheet1118
- column with account of the book Datasheet1118
- column from which the value of the book Datasheet1118 will be obtained

Sub Get_Values()
'
    Application.ScreenUpdating = False
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("master")
    '
    ruta = "C:\trabajo\"
    meses = Array("1118", "1218")
    cols = Array("C", "D")
    For m = LBound(meses) To UBound(meses)
        arch = Dir(ruta & "*" & meses(m) & ".xls*")
        If arch <> "" Then
            Set l2 = Workbooks.Open(ruta & arch)
            Set h2 = l2.Sheets(1)
            For i = 2 To h1.Range("A" & Rows.Count).End(xlUp).Row  'column "A" with account number
                Set b = h2.Columns("A").Find(h1.Cells(i, "A"), lookat:=xlWhole)
                If Not b Is Nothing Then
                    h1.Cells(i, cols(m)).Value = h2.Cells(b.Row, "B").Value
                End If
            Next
            l2.Close False
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

I put the macro, you can put the missing data, if you need help I will gladly do it.

Regards Dante Amor

 

[osscie3]

Is there a way to loop through an entire folder of spreadsheets? Reason I ask is I have 68 different spreadsheets in one folder that I need to extract data from. I read this one article about using a query to pull data from a folder but wasn't sure if it'd work for my situation or not. Thoughts?

 

[osscie3]

I've edited your code and it seems that arch is coming up as no value. I keep getting the "Success" message box at the end which leads me to believe that arch = nothing so it just goes straight to the message box. Here's my code.

To answer your questions above-

- name sheet master book - Shares 2019 Study
- column with master book account - A
- column with Nov-2018 from the master book - C
- sheet name book Datasheet1118 - Sheet1
- column with account of the book Datasheet1118 - A
- column from which the value of the book Datasheet1118 will be obtained - B and E

Sub Get_Values()


Application.ScreenUpdating = False


Set l1 = ThisWorkbook
Set h1 = l1.Sheets("Shares 2019 Study")


dbPath = "C:\Users\me\Desktop\2019 Decay Rate Study\DB's\Shares"
dbSheets = Array("SHARES0713")
dbCols = Array("B", "E")


For m = LBound(dbSheets) To UBound(dbSheets)
    arch = Dir(dbPath & "*" & dbSheets(m) & ".csv*")
    If arch <> "" Then
        Set l2 = Workbooks.Open(dbPath & arch)
        Set h2 = l2.Sheets(1)
        For i = 2 To h1.Range("A" & Rows.Count).End(xlUp).Row
            Set b = h2.Columns("A").Find(h1.Cells(i, "A"), lookat:=xlWhole)
            If Not b Is Nothing Then
                h1.Cells(i, dbCols(m)).Value = h2.Cells(b.Row, "B").Value
            End If
        Next
        l2.Close False
    End If
Next
Application.ScreenUpdating = True
MsgBox "Success"


End Sub

 

[DanteAmor]

Of course there is a way to read all the files in a folder, but you have to explain with examples what data you want to extract from each book. You must put all the complete information (sheets, columns).
In your example, you only mention reading the book Datasheet1118 for November and the book Datasheet1218 for December.

 

[DanteAmor]

Could you upload 3 files to the cloud, the master book and 2 csv files. The data of the csv files must be compiled in the "Shares 2019 Study" sheet. And you explain to me what data comes from which csv file.

 

[osscie3]

Each workbook is essentially a monthly file/spreadsheet that only has one sheet labeled Sheet1. I need to read 68 workbooks all that have a consistent naming convention. So Shares0713, Shares0813, Shares0913, Shares1013, etc until Shares1218.

I need to retrieve the values in column B and E of each of those workbooks for the associated value in the master spreadsheet under column A.

I can't upload anything due to confidentiality unfortunately. Feel free to ask additional questions. Glad to answer.

 

[DanteAmor]

I understand the confidentiality, it is not necessary that the real data come, it can put fictitious data but that help to understand the process.

I will try but I'm blind.

Structure of the sheet "Shares 2019 Study"

	A	B	C	D	E	F
1	Account
2	1234

<tbody>
</tbody>

Structure of the "sheet1" file "Shares0713.csv"

	A	B	C	D	E	F
1	Account
2	1231
3	1232
4	1233
5	1234	701			707
6	1235

<tbody>
</tbody>

I read file Shares0713, I find account 1234, the value in column B is "701", the value in column "E" is "707", I take the value "701" and where do I put it?, I take the value "707" and where do I put it?

I read file Shares0813, I find account 1234, the value in column B is "801", the value in column "E" is "807", I take the value "801" and where do I put it?, I take the value "807" and where do I put it?

I read file Shares0913, I find account 1234, the value in column B is "901", the value in column "E" is "907", I take the value "901" and where do I put it?
...

Structure of the sheet "Shares 2019 Study"
Result:

	A	B	C	D	E	F
1	Account	Jul-2013 (col B)	Jul-2013 (col E)	Aug-2013 (col B)	Aug-2013 (col e)
2	1234	701 (Is this value going here?)	707 (Is this value going here?)	801?	807?

<tbody>
</tbody>

If you get 2 results, we need 2 sites to put them.

 

[osscie3]

Close. I'll rearrange for you.

Structure of the sheet "Shares 2019 Study"

	A	B	C	D	E	F
1	Account	07/13	08/13	09/13	10/13	Date Closed
2	1234
3	1235
4	1236

<tbody>
</tbody>

Structure of the "sheet1" file "Shares0713.csv"

	A	B	C	D	E
1	Account	Balance	Date Opened	Branch	Date Closed
2	1234	305.65	01/01/2019	1	NULL
3	1235	306.78	01/01/2018	2	NULL
4	1236	604.35	01/01/2011	3	01/01/2019
5	1237	664.14	01/01/2009	6	NULL
6	1238	12.52	01/01/2017	7	NULL

<tbody>
</tbody>

Read the file Shares0713, find account 1234, the value in column B is 305.65, the value in column E is NULL. Take value 305.65 and put it in column B and take value NULL and put it in column F.

Read the file Shares0713, find account 1235, the value in column B is 306.78, the value in column E is NULL. Take value 306.78 and put it in column B and take value NULL and put it in column F.
Etc.

I know my date closed may be overwritten at some point.

I read file Shares0813, I find account 1234, the value in column B is 400, the value in column E is 01/01/2019, I take value 400 and put it in column C, I take value 01/01/2019 and put it in column F. Etc for each account

Result:

	A	B	C	D	E	F
1	Account	07/13	08/13	09/13	10/13	Date Closed
2	1234	305.65	400			01/01/2019
3	1235	306.78				Null
4	1236	604.35				01/01/2019

<tbody>
</tbody>

Make sense?

 

[DanteAmor]

That makes a lot of sense!


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.