Multiple CSV Imports to single Workbook

[Steevw]

Hi & HELP !!!

My Dilemma.

I have a singular location containing multiple csv files which I want to import into a single workbook, but each csv under a separate worksheet labelled as the csv file name, and the data imported as comma separated text fields.

can anyone please help. it would be so good to have a macro to run that just pinged off and did this for me, currently it takes hours as there are in excess of 170 csv files each day.

Please help if you can.

 

[Domenic]

Do the destination worksheets already exist within the workbook? Or do they have to be created/added?

 

[Steevw]

Hi Domenic,

They do not currently exist, i am hoping that in setting the path / local file store of the CSV's that somehow the process can create the Worksheets per the csv filenames, ideally without the .csv extensions.

I don't want much huh !!!

Thank you for responding, this really is appreciated.

 

[Domenic]

Assuming that the destination workbook is the active workbook, try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] ImportCSV()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] wkbDest [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] Data [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]Set[/color] wkbDest = ActiveWorkbook

    [color=green]'Change the path to the source folder accordingly[/color]
    strPath = "C:\Users\Domenic\Desktop\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.csv")
    
    r = 1
    c = 1
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
        Cnt = Cnt + 1
        [color=darkblue]Set[/color] wksDest = wkbDest.Worksheets.Add(before:=Worksheets(Cnt))
        wksDest.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
        [color=darkblue]Open[/color] strPath & strFile [color=darkblue]For[/color] [color=darkblue]Input[/color] [color=darkblue]As[/color] #1
            [color=darkblue]Do[/color] [color=darkblue]Until[/color] EOF(1)
                Line [color=darkblue]Input[/color] #1, Data
                x = Split(Data, ",")
                [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](x) [color=darkblue]To[/color] [color=darkblue]UBound[/color](x)
                    wksDest.Cells(r, c).Value = Trim(x(i))
                    c = c + 1
                [color=darkblue]Next[/color] i
                r = r + 1
                c = 1
            [color=darkblue]Loop[/color]
        [color=darkblue]Close[/color] #1
        r = 1
        strFile = Dir
    [color=darkblue]Loop[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    [color=darkblue]If[/color] Cnt = 0 [color=darkblue]Then[/color]
        MsgBox "No CSV values were found...", vbExclamation
    [color=darkblue]Else[/color]
        MsgBox Cnt & " CSV files have been imported...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
                
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]