Combine multiple CSV files automatically instead of using Application.GetOpenFilename

[davidp13]

Hi. I have a piece of code that helps me select multiple CSV files and then import them into one sheet where I will then run additional scripts to build output data. I'm looking to automate the process more and trying to circumvent the selection of the CSV through the manual process and rather have the script automatically pick up all the files and then import + manipulate as needed.

Below is the code. Can anyone help me change this so that when I execute it, it will choose all the CSV in a folder automatically? Thanks

Sub Import()    Dim fn, ws As Worksheet, e, flg As Boolean, LastR As Range, wsName As String
   
    fn = Application.GetOpenFilename("Excel(*.csv*),*.csv*", MultiSelect:=True)
    
    If Not IsArray(fn) Then Exit Sub
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Application.ScreenUpdating = False
    ws.Cells.Clear
    For Each e In fn
        With Workbooks.Open(e)
            With .Sheets(1)
                wsName = .Name
                If Not flg Then
                    .Rows(1).Copy ws.Cells(1)
                    ws.Columns(1).Insert
                    ws.Cells(19).Value = "Sheet name"
                    flg = True
                End If
                Set LastR = ws.Cells(Rows.Count, 2).End(xlUp)(2)
                With .Range("a1").CurrentRegion
                    With .Resize(.Rows.Count - 1).Offset(1)
                        .Copy LastR
                        LastR(, 18).Resize(.Rows.Count).Value = _
                            CreateObject("Scripting.FileSystemObject").GetBasename(e)
                    End With
                End With
            End With
            .Close False
        End With
    Next
    Sheets("Sheet1").Range("A:A").EntireColumn.Delete
    ws.Range("a1").CurrentRegion.Columns.AutoFit
    Application.ScreenUpdating = True
    Set ws = Nothing
End Sub

 

[Lovin]

Yeah, think since it comes from the Biometric system that interferes. Thank you for the assistance. Appreciate it.

 

[Lovin]

Hi Joe4, I had to share. I finally solved my issue of the .csv not pulling through to my merged document. It was due to me forgetting about my Personal.XLSB. My files would merge, then the extension pulled through to a different sheet. I'm quite happy I managed to solve that and thought to let you know. Thank you for the help .