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
<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
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
A | B | |||
---|---|---|---|---|
1 | Unit # & Filename | Unit Name | (What full path to file would be) | |
2 | 110 | UnitName One | C:\Users\Workstation\Downloads\110.csv | |
3 | 123456 | Unitname Two | C:\Users\Workstation\Downloads\123456.csv | |
4 | 123458 | Unitname Three | C:\Users\Workstation\Downloads\123458.csv | |
5 | 200101 | Unitname Four | C:\Users\Workstation\Downloads\200101.csv | |
6 | 232010 | Unitname Five | C:\Users\Workstation\Downloads\232010.csv | |
7 | 1010101 | Unitname Six | C:\Users\Workstation\Downloads\1010101.csv | |
8 | 224023 | Unitname Seven | C:\Users\Workstation\Downloads\224023.csv | |
9 | 110110 | Unitname Eight | C:\Users\Workstation\Downloads\110110.csv | |
10 | 144423 | Unitname Nine | C:\Users\Workstation\Downloads\144423.csv | |
11 | 54444 | Unitname Ten | C:\Users\Workstation\Downloads\54444.csv | |
12 | 2312 | Unitname Eleven | C:\Users\Workstation\Downloads\2312.csv | |
13 | ||||
14 | ||||
15 | ||||
16 | ||||
Type Directory Here | C:\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