VBA code for import of multiple csv files

[Stroppiger]

Hi everyone,

I've got some problems with the vba code, that I found online. I want to import multiple csv files without header into the worksheet "csv_data". The following code imports just one file, even though I select a few. Please tell me where I made a mistake.

Sub CSV_Import()
Dim dateien, i, lastrow
lastrow = 1
dateien = Application.GetOpenFilename _
("csv-Dateien (*.csv), *.csv", MultiSelect:=True)
If IsArray(dateien) Then
For i = 1 To UBound(dateien)
Workbooks.Open dateien(i), local:=True
With ThisWorkbook.Sheets("csv_data")
ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.count - 1).Offset(1, 0).Copy _
Destination:=.Range("A" & lastrow)
lastrow = .UsedRange.Rows.count + 1
End With
ActiveWorkbook.Close True
Next i
End If
End Sub

Thanks in advance

 

[Marc L]

(removed)

 

[Domenic]

It seems to import multiple files. Why do you say that it doesn't?

 

[Stroppiger]

It seems to import multiple files. Why do you say that it doesn't?

Because it always pastes the data of just one csv file into the worksheet "csv data" and not all of them combined.

 

[Domenic]

When I ran your code, it seemed to work fine. In any case, I have re-written your code. Hopefully it works for your. You'll notice that for efficiency I set ScreenUpdating to False before importing the data, and then back to True once completed. Here's the code...

Option Explicit

Sub CSV_Import()

    Dim dateien As Variant
    Dim sourceWorkbook As Workbook
    Dim sourceRange As Range
    Dim destinationWorksheet As Worksheet
    Dim nextRow As Long
    Dim i As Long
   
    dateien = Application.GetOpenFilename("csv-Dateien (*.csv), *.csv", MultiSelect:=True)
   
    If Not IsArray(dateien) Then Exit Sub
   
    Application.ScreenUpdating = False
   
    Set destinationWorksheet = ThisWorkbook.Sheets("csv_data")
   
    nextRow = 1
    For i = LBound(dateien) To UBound(dateien)
        Set sourceWorkbook = Workbooks.Open(dateien(i), local:=True)
        With sourceWorkbook.ActiveSheet
            Set sourceRange = .UsedRange.Resize(.UsedRange.Rows.Count - 1).Offset(1, 0)
        End With
        sourceRange.Copy destinationWorksheet.Cells(nextRow, "A")
        nextRow = nextRow + sourceRange.Rows.Count
        sourceWorkbook.Close False
    Next i
   
    Application.ScreenUpdating = True
   
    MsgBox "Completed . . .", vbInformation 'optional
   
End Sub

Does this help?

 

[Stroppiger]

When I ran your code, it seemed to work fine. In any case, I have re-written your code. Hopefully it works for your. You'll notice that for efficiency I set ScreenUpdating to False before importing the data, and then back to True once completed. Here's the code...

Option Explicit

Sub CSV_Import()

    Dim dateien As Variant
    Dim sourceWorkbook As Workbook
    Dim sourceRange As Range
    Dim destinationWorksheet As Worksheet
    Dim nextRow As Long
    Dim i As Long
  
    dateien = Application.GetOpenFilename("csv-Dateien (*.csv), *.csv", MultiSelect:=True)
  
    If Not IsArray(dateien) Then Exit Sub
  
    Application.ScreenUpdating = False
  
    Set destinationWorksheet = ThisWorkbook.Sheets("csv_data")
  
    nextRow = 1
    For i = LBound(dateien) To UBound(dateien)
        Set sourceWorkbook = Workbooks.Open(dateien(i), local:=True)
        With sourceWorkbook.ActiveSheet
            Set sourceRange = .UsedRange.Resize(.UsedRange.Rows.Count - 1).Offset(1, 0)
        End With
        sourceRange.Copy destinationWorksheet.Cells(nextRow, "A")
        nextRow = nextRow + sourceRange.Rows.Count
        sourceWorkbook.Close False
    Next i
  
    Application.ScreenUpdating = True
  
    MsgBox "Completed . . .", vbInformation 'optional
  
End Sub

Does this help?

Hi Domenic,

thank you so much for your help! The code is working perfectly.

 

[Domenic]

That's great, I'm glad I could help, and thank for your feedback.

Cheers!