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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
It seems to import multiple files. Why do you say that it doesn't?
 
Upvote 0
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?
 
Upvote 0
Solution
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.
 
Upvote 0
That's great, I'm glad I could help, and thank for your feedback.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top