VBA code for import of multiple csv files

Stroppiger

New Member
Joined
Jun 29, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,722
It seems to import multiple files. Why do you say that it doesn't?
 

Stroppiger

New Member
Joined
Jun 29, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,722

ADVERTISEMENT

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

VBA 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?
 
Solution

Stroppiger

New Member
Joined
Jun 29, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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...

VBA 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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,722
That's great, I'm glad I could help, and thank for your feedback.

Cheers!
 

Forum statistics

Threads
1,141,410
Messages
5,706,292
Members
421,439
Latest member
JordsdoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top