VBA to copy column/data from two .csv files to one combined file.

kbilly70

New Member
Joined
Jan 10, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. First time posting and I'm a novice when it comes to VBA.

I have two .csv files that are exports from another program. I am trying to copy some of the data from those .csv files into one excel file so that it can be imported back into the program. The two .csv files have different file names, different sheet names, and some columns that have different headings.

What I am looking to do is copy multiple columns from file1 & file2 into one Excel file. As an example, columns A & B from file1 and column B from file2 into the one Excel file as columns A, B, & C. Here is an image of what it would look like:

1705067182462.png


I've found a number of VBA code in posts here but being a novice, I've not been unable to modify them for my needs.

Thank you in advance for any assistance.

kbilly70
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Ever think of just using Excels Power Query, import the 2 files and Merge them, removing any columns you dont need, easier than VBA, if you have a common key in both files e.g. Employee ID this would be a breeze
 
Upvote 0
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

According to your image data, the data is in columns A and B and starts at the row 2.
I highlighted in blue the data that you must update in the macro.
Put the following macro in a workbook, save the workbook with the macro in the same path where you have the csv files.
Rich (BB code):
Sub import_2_csv_files()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh As Worksheet
  Dim f As Range, c As Range
 
  Application.ScreenUpdating = False
 
  Set sh = ThisWorkbook.Sheets("Sheet1")                            'Destination sheet name
  Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & "File1.csv")   'File name1
  Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & "File2.csv")   'File name2
 
  sh.Rows("2:" & Rows.Count).ClearContents
 
  wb1.Sheets(1).Range("A:B").Copy sh.Range("A1")
 
  For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
    Set f = wb2.Sheets(1).Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(0, 1).Value = f.Offset(0, 1).Value
    End If
  Next

  wb1.Close False
  wb2.Close False
 
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --
 
Upvote 0
Solution
Ever think of just using Excels Power Query, import the 2 files and Merge them, removing any columns you dont need, easier than VBA, if you have a common key in both files e.g. Employee ID this would be a breeze

Kerryx, thank you for the suggestion and I did consider that, but the only common key is the name and the column headings are different. I could rename but was hoping for a solution where I didn't have to touch the files.

kbilly70
 
Upvote 0
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

According to your image data, the data is in columns A and B and starts at the row 2.
I highlighted in blue the data that you must update in the macro.
Put the following macro in a workbook, save the workbook with the macro in the same path where you have the csv files.
Rich (BB code):
Sub import_2_csv_files()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh As Worksheet
  Dim f As Range, c As Range
 
  Application.ScreenUpdating = False
 
  Set sh = ThisWorkbook.Sheets("Sheet1")                            'Destination sheet name
  Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & "File1.csv")   'File name1
  Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & "File2.csv")   'File name2
 
  sh.Rows("2:" & Rows.Count).ClearContents
 
  wb1.Sheets(1).Range("A:B").Copy sh.Range("A1")
 
  For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
    Set f = wb2.Sheets(1).Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(0, 1).Value = f.Offset(0, 1).Value
    End If
  Next

  wb1.Close False
  wb2.Close False
 
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Dante Amor,

Thank you for the kind words and welcome! The code works except it is only grabbing A & B from file1 and not grabbing B from file2.

I only changed what you advised in the code. Any suggestions?

Thank you,

kbilly70
 
Upvote 0
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

According to your image data, the data is in columns A and B and starts at the row 2.
I highlighted in blue the data that you must update in the macro.
Put the following macro in a workbook, save the workbook with the macro in the same path where you have the csv files.
Rich (BB code):
Sub import_2_csv_files()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh As Worksheet
  Dim f As Range, c As Range
 
  Application.ScreenUpdating = False
 
  Set sh = ThisWorkbook.Sheets("Sheet1")                            'Destination sheet name
  Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & "File1.csv")   'File name1
  Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & "File2.csv")   'File name2
 
  sh.Rows("2:" & Rows.Count).ClearContents
 
  wb1.Sheets(1).Range("A:B").Copy sh.Range("A1")
 
  For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
    Set f = wb2.Sheets(1).Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(0, 1).Value = f.Offset(0, 1).Value
    End If
  Next

  wb1.Close False
  wb2.Close False
 
  Application.ScreenUpdating = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Dante Amor,

Actually, I think this is solved. I just tweaked/added to this
VBA Code:
wb1.Sheets(1).Range("A:B").Copy sh.Range("A1")

Again, thank you so much for the quick response and solution!

kbilly70
 
Upvote 0
I apologize for reviving this if this is incorrect but didn't think I was supposed to create a new thread.

I have run into an instance where wb1 column B has one or more rows than wb2 column B and so the data does not line up between the columns in the combined sheet.

Is there a way to display a zero or null in column c when column b from wb1 or wb2 is blank?

I hope that was clear.

Thank you,

Brandon
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

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