VBA to read 1 line at a time from 2 CSVs and write them sequentially to the active workbook

[BakerSteve]

Hi, this should be easy (?) but I have struggled mightily and I have not found other's solutions to this question! I consider myself intermediate in native Excel skills and somewhat of a beginner using VBA.
My quandary: I have two (input) CSV files which are of the same length and width. The only difference is that one contains REVENUE data and the other contains TAX data. They have identical column headers in row 1 and identical row labels in column A.
I want to open both CSV files and read the first line from each, and write them sequentially to the active (already open) worksheet. Then, loop to the 2nd line in each input CSV, read those, and write them to the active worksheet, repeating this process until EOF.
This should be simple, right?

 

[Anthony47]

You could easily do that by recording a macro while, starting from your master workbook
-open the first csv (*)
-select from A1 down then to the right, Copy
-move to the master workbook
-select the last row (*) then Paste
-return to csv1 and close
-open the second csv (*)
-repeat the process of select, copy, paste; close
Stop now recording

If you publish the code of your macro we will help in automatizing the steps with the asterix (*)

For the first suggestion on recording macro: Quick start: Create a macro - Microsoft Support

You say you are a beginner with vba, I think that recording a macro (and then modifying its code) is one of the most effective way to learn quickly

 

[BakerSteve]

You could easily do that by recording a macro while, starting from your master workbook
-open the first csv (*)
-select from A1 down then to the right, Copy
-move to the master workbook
-select the last row (*) then Paste
-return to csv1 and close
-open the second csv (*)
-repeat the process of select, copy, paste; close
Stop now recording

If you publish the code of your macro we will help in automatizing the steps with the asterix (*)

For the first suggestion on recording macro: Quick start: Create a macro - Microsoft Support

You say you are a beginner with vba, I think that recording a macro (and then modifying its code) is one of the most effective way to learn quickly

Thank you, Anthony47! I will try your suggestion - that is truly a good way to learn.
The only difference in what you suggest is that I want alternate lines (which I can try and mimic through recording) - that is:
OUTPUT
header row csv1
header row csv2
row 2 from csv1
row 2 from csv 2
row 3 from csv1
row 3 from csv2
and so on
I think a do loop would be the ultimate approach.
Thanks!

 

[Anthony47]

Do record the macro, and import the first 3-4 lines (then close the CSVs and terminate recording); the resulting code will be the base for a loop to import all the data

 

[BakerSteve]

Do record the macro, and import the first 3-4 lines (then close the CSVs and terminate recording); the resulting code will be the base for a loop to import all the data

Thank you again, Anthony47! I got 'er done - not altogether pretty, but it works. Thanks for your Socratic coaching!

Option Explicit

Sub MonthlyTaxReporting()
'
' MonthlyTaxReporting Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim dateien As Variant
Dim srccsv1, srccsv2 As Workbook
Dim destws As Worksheet
Dim n, r, s As Long

dateien = Application.GetOpenFilename("csv-Dateien (*.csv), *.csv", MultiSelect:=True)

If Not IsArray(dateien) Then Exit Sub

Application.ScreenUpdating = False

Set destws = ThisWorkbook.Sheets("csv_data")
destws.Activate
Cells.Clear

Set srccsv1 = Workbooks.Open(dateien(2), local:=True)
Set srccsv2 = Workbooks.Open(dateien(1), local:=True)

srccsv1.Activate
n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

r = 0
s = 0
Do While r <= n

r = r + 1
s = s + 1
srccsv1.Activate
Rows(r & ":" & r).Select
Application.CutCopyMode = False
Selection.Copy
destws.Activate
Rows(s & ":" & s).Select
ActiveSheet.Paste

If s > 1 Then
s = s + 1
srccsv2.Activate
Rows(r & ":" & r).Select
Application.CutCopyMode = False
Selection.Copy
destws.Activate
Rows(s & ":" & s).Select
ActiveSheet.Paste
End If
Loop

srccsv1.Application.CutCopyMode = False
srccsv1.Close False
srccsv2.Application.CutCopyMode = False
srccsv2.Close False

destws.Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True

MsgBox "Completed . . .", vbInformation 'optional

End Sub

 

[Anthony47]

Your code works, thus it is perfect
As you gain experience with vba you will learn that you don't need to "Select" before "Copy" (selection is a slow operation), so you could use srccsv2.Rows(r & ":" & r).Copy
Then you will learn about the "Destination" parameter for Copy and will use srccsv2.Rows(r & ":" & r).Copy Destination:=destws.Cells(s ,1)
These tricks will be useful when dealing with long sets of data

Bye

 

[BakerSteve]

Super cool!
Option Explicit

Sub MonthlyTaxReporting()
'
' MonthlyTaxReporting Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim dateien As Variant
Dim srccsv1, srccsv2 As Workbook
Dim ws1, ws2, destws As Worksheet
Dim n, r, s As Long

dateien = Application.GetOpenFilename("csv-Dateien (*.csv), *.csv", MultiSelect:=True)

If Not IsArray(dateien) Then Exit Sub

Application.ScreenUpdating = False

Set destws = ThisWorkbook.Sheets("csv_data")
destws.Activate
Cells.Clear

Set srccsv1 = Workbooks.Open(dateien(2), local:=True)
Set srccsv2 = Workbooks.Open(dateien(1), local:=True)

srccsv1.Activate
Range("A1").Select
ws1 = ActiveSheet.Name

n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

srccsv2.Activate
Range("A1").Select
ws2 = ActiveSheet.Name

r = 0
s = 0
Do While r <= n

r = r + 1
s = s + 1
srccsv1.Sheets(ws1).Rows(r & ":" & r).Copy Destination:=destws.Rows(s & ":" & s)

If s > 1 Then
s = s + 1
srccsv2.Sheets(ws2).Rows(r & ":" & r).Copy Destination:=destws.Rows(s & ":" & s)
End If
Loop

srccsv1.Application.CutCopyMode = False
srccsv1.Close False
srccsv2.Application.CutCopyMode = False
srccsv2.Close False

destws.Cells.EntireColumn.AutoFit
Range("A2").Select
Application.ScreenUpdating = True

'MsgBox "Completed . . .", vbInformation 'optional

End Sub

 

[Anthony47]

Excellent!
Next step? Learn how to use TAGs to make your code easier to read on the forum (before the Moderators get angry with us)!
Bye

 

[BakerSteve]

Thank you once again, Anthony47 !! I was wondering why my posts did not look right ...

Option Explicit

Sub MonthlyTaxReporting()
'
' MonthlyTaxReporting Macro
'
' A macro to open two similarly formatted CSV files of same length and write/output them, alternately, one line at a time, to a blank worksheet
'
    Dim dateien As Variant
    Dim srccsv1, srccsv2 As Workbook
    Dim ws1, ws2, destws As Worksheet
    Dim n, r, s As Long

    dateien = Application.GetOpenFilename("csv-Dateien (*.csv), *.csv", MultiSelect:=True)

    If Not IsArray(dateien) Then Exit Sub

    Application.ScreenUpdating = False

    Set destws = ThisWorkbook.Sheets("csv_data")
    destws.Activate
    Cells.Clear                 ' clear the output worksheet

    Set srccsv1 = Workbooks.Open(dateien(2), local:=True)
    Set srccsv2 = Workbooks.Open(dateien(1), local:=True)

    srccsv1.Activate
    Range("A1").Select
    ws1 = ActiveSheet.Name      ' get the name of the first CSV worksheet, often lengthy and different each month
    
    n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1    ' get the number of rows of the input CSVs
    
    srccsv2.Activate
    Range("A1").Select
    ws2 = ActiveSheet.Name      ' get the name of the second CSV worksheet
    
    r = 0
    s = 0
    Do While r <= n
        
        r = r + 1               ' increment the row number of the input CSVs
        s = s + 1               ' increment the row number of the output worksheet
        srccsv1.Sheets(ws1).Rows(r & ":" & r).Copy Destination:=destws.Rows(s & ":" & s)

        If s > 1 Then           ' don't repeat the header (first) row from the second CSV in the output worksheet
            s = s + 1           ' increment the row number of the output worksheet
            srccsv2.Sheets(ws2).Rows(r & ":" & r).Copy Destination:=destws.Rows(s & ":" & s)
        End If
    Loop

    srccsv1.Application.CutCopyMode = False
    srccsv1.Close False
    srccsv2.Application.CutCopyMode = False
    srccsv2.Close False
    
    destws.Cells.EntireColumn.AutoFit       ' make it look good
    Range("A2").Select
    Application.ScreenUpdating = True
  
    MsgBox "Completed . . .", vbInformation 'optional

End Sub