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

[kbilly70]

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:

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

 

[Kerryx]

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

 

[DanteAmor]

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.

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
----- --

 

[kbilly70]

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

 

[kbilly70]

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.

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

 

[kbilly70]

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.

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

wb1.Sheets(1).Range("A:B").Copy sh.Range("A1")

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

kbilly70

 

[kbilly70]

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