Macro to append data

[DonFlak]

We run several daily performance reports. The files are created by day of week with a DOW identifier built in. All of the files are structured the same way, (columns, headers, etc) but with varying number of rows per day.

I need to create a macro that will open each of the daily CSV files, extra all the data under the header row (1) and append to a single worksheet. The number of rows will vary but I need it to then open the next CSV file and append that data beginning in the first empty row so I can compile a full week of data in a single worksheet.

If you need the info, the CSV files are named CSVAR1.csv, CSVAR2.csv, etc through CSVAR7.csv and the Workbook is CSVARIANCE.xls and the data sheet is named Data

Thanks for any assistance.

 

[Ruddles]

<deleted>

 

[kpark91]

What is the significance of the data sheet named "Data"?

 

[DonFlak]

It is simply the worksheet name of the location where we compile all of the daily data. Currently we manually open each of the 7 daily csv files, copy the rows of data and paste into the Data Tab of the CSVARIANCE.xls workbook. Later other macros, open that workbook, access the data from the Data Tab and use it to produce performance analysis. If it presents a problem, the name could be changed to just about anything.

Thanks

 

[kpark91]

Sorry it took so long, I had to take care of a personal business.

I'm unsure when you meant .csv files in a workbook because I don't think excel allows that.
Anyways, I interpreted the problem as copying the csv files data into the active worksheet so... here is the code:

Sub compileCSVs()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
 
    Dim openPath As String, rootCName As String, i As Double
    Dim lineNumber As Double, elementNumber As Double, arrayOfElements As Variant, line As String
 
 
    rootCName = "CSVAR"
    csvLC = 7 '# of columns (Assumption: same number of columns in all the worksheets)
    lineNumber = 0
 
    For i = 1 To 2
        openPath = ThisWorkbook.path & "\" & rootCName & i & ".csv"
        elementNumber = 0
        csvarianceLine = 1
        Open openPath For Input As #1
            Do While Not EOF(1)
                lineNumber = lineNumber + 1
                Line Input #1, line
                arrayOfElements = Split(line, ",")
                elementNumber = 0
                For Each element In arrayOfElements
                    elementNumber = elementNumber + 1
                    Cells(lineNumber, elementNumber).Value = element
                Next
            Loop
        Close #1 ' Close
    Next i
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
 
End Sub

You will notice that it also copies the headers onto the worksheet... I'm not sure how to skip a line using Input #1 method or any other methods as a matter of fact lol.

 

[DonFlak]

Thanks. That works perfectly except as you said, that pesky 2 line header that repeats.