CODEWITHSUNDEEP
excelvbacsvimport
Chris H.
Chris H.

Reputation: 325

Import multiple .csv files into single spreadsheet

I'm working on a simple macro to take all the csv files from a folder and copy the data into a single worksheet. All the csv files are formatted the same with headers and used data in columns a:f. The macro will open each file in turn, but nothing is getting copied. I also tried bypassing the copy/paste and still get nothing. Any ideas??

Option Explicit

Sub ImportData()

Dim lastrow As Long
Dim clastrow As Long
Dim filePath As String
Dim fileName As String
Dim count As Long
Dim importRange As Range
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim cws As Excel.Worksheet

count = 0

Set cws = ThisWorkbook.Sheets("Raw_Data")

filePath = "C:\Users\christopher.huiett\Desktop\csv_folder\"
fileName = Dir(filePath & "*.csv")

Do While fileName <> ""
    count = count + 1

    Set wb = Excel.Workbooks.Open(filePath & fileName)
    Set ws = wb.Worksheets(1)

    lastrow = ws.Cells(Rows.count, "a").End(xlUp).Row
    clastrow = cws.Cells(Rows.count, "a").End(xlUp).Row + 1

    Set importRange = ws.Range("a2" & lastrow)           'skips header row

'    cws.Cells(clastrow, 1).End(xlUp).Offset(1, 0).Resize(importRange.Rows.count, importRange.Columns.count) = importRange.Value

    importRange.Copy

    cws.Cells(clastrow, "a").PasteSpecial xlPasteValues

    wb.Application.CutCopyMode = False
    wb.Close

    fileName = Dir

Loop
End Sub

Upvotes: 0

Views: 1238

Answers (2)

Damian
Damian

Reputation: 5174

Your error comes here:

Set importRange = ws.Range("a2" & lastrow)

If lastrow = 25 then that means:

Set importRange = ws.Range("a2" & 25)

which is: ws.Range("A225")

So you need to change it for this:

Set imporRange = ws.Range("A2:F" & lastrow)

Upvotes: 0

FunThomas
FunThomas

Reputation: 29592

I think you problem is the line Set importRange = ws.Range("a2" & lastrow). Consider that lastrow = 1000: You would set the importRange to A21000 - a single cell that is for sure empty.

While the use of UsedRange is often not the method of choice when you want to figure out how many rows/columns are in use: When opening a workbook, it is reliable. You want to skip the first row, so you can use simply use UsedRange.Offset(1, 0). This will copy a blank line at the end, but that does no harm.

clastrow = cws.Cells(ws.Rows.count, "a").End(xlUp).Row + 1
Set importRange = ws.UsedRange.Offset(1, 0)

Upvotes: 1

Related Questions