VBA: Open each CSV listed in Array, add column containing value from array.

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
We have a directory containing various files we wish to add data to the last column in (Col BH). The list of files is in this table/array.

For ONLY for the files listed in this array/table, we want to open each file and add a value on each row with data. The value we want to add is the "Unit Name" for each matching Unit #.

For example, the first file to open would be "C:\Users\Workstation\Downloads\110.csv". The Unit Name for 110 is "UnitName One" so on BH2:BH(Lastrow) would have a value of "UnitName One". Then the file would save and close. The macro would then repeat for each file in this list until row 13 where it's blank.

Note about these files: All the files we want to open are CSV's. Each CSV has the same # of Columns and same header info, but different amount of Rows. We always want to add the value to Column. Also, this is not a defined name range, a table, or anything named but it could be if it would help.


Sheet1
AB
1Unit # & FilenameUnit Name (What full path to file would be)
2110UnitName One C:\Users\Workstation\Downloads\110.csv
3123456Unitname Two C:\Users\Workstation\Downloads\123456.csv
4123458Unitname Three C:\Users\Workstation\Downloads\123458.csv
5200101Unitname Four C:\Users\Workstation\Downloads\200101.csv
6232010Unitname Five C:\Users\Workstation\Downloads\232010.csv
71010101Unitname Six C:\Users\Workstation\Downloads\1010101.csv
8224023Unitname Seven C:\Users\Workstation\Downloads\224023.csv
9110110Unitname Eight C:\Users\Workstation\Downloads\110110.csv
10144423Unitname Nine C:\Users\Workstation\Downloads\144423.csv
1154444Unitname Ten C:\Users\Workstation\Downloads\54444.csv
122312Unitname Eleven C:\Users\Workstation\Downloads\2312.csv
13
14
15
16
Type Directory HereC:\Users\Workstation\Downloads\

<thead>
</thead><tbody>
</tbody>

Can someone help me create a script that does this?

I found a few links that might give you some ideas:
VBA Open Files from a list
https://www.mrexcel.com/forum/excel...cel-list-using-visual-basic-applications.html
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
This assumes that the csv files all have only one sheet. It further assumes that the code will be in the workbook containing the sheet with the list of files and that the data will be in columns as illustrated in Post # 1.


Code:
Sub addData()
Dim wb As Workbook, sh As Worksheet, c As Range, fName As String, fPath As String
fPath = "C:\Users\Workstation\Downloads\"
Set sh = ThisWorkbook.Sheets("Sheet1") 'Verify sheet name
    For Each c In sh.Range("A2", sh.Cells(Rows.Count, 1).End(xlUp))
        fName = c.Value
        On Error GoTo Skip:
        Set wb = Workbooks.Open(fPath & fName & ".csv")
            With wb.Sheets(1)
                .Range("BH2", .Cells(Rows.Count, "BH").End(xlUp)) = c.Offset(, 1).Value
            End With
        wb.Close True
Skip:
        If Err.Number > 0 Then
            MsgBox Err.Number & ":  " & Err.Description & vbLf & "File " & fName & " not found.", , "NO MATCH"
            Err.Clear
        End If
    Next
End Sub
 
Last edited:
Upvote 0
Works great! Thank you!!

This assumes that the csv files all have only one sheet. It further assumes that the code will be in the workbook containing the sheet with the list of files and that the data will be in columns as illustrated in Post # 1.


Code:
Sub addData()
Dim wb As Workbook, sh As Worksheet, c As Range, fName As String, fPath As String
fPath = "C:\Users\Workstation\Downloads\"
Set sh = ThisWorkbook.Sheets("Sheet1") 'Verify sheet name
    For Each c In sh.Range("A2", sh.Cells(Rows.Count, 1).End(xlUp))
        fName = c.Value
        On Error GoTo Skip:
        Set wb = Workbooks.Open(fPath & fName & ".csv")
            With wb.Sheets(1)
                .Range("BH2", .Cells(Rows.Count, "BH").End(xlUp)) = c.Offset(, 1).Value
            End With
        wb.Close True
Skip:
        If Err.Number > 0 Then
            MsgBox Err.Number & ":  " & Err.Description & vbLf & "File " & fName & " not found.", , "NO MATCH"
            Err.Clear
        End If
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top