Import CSV's to Excel file and inserting constants in rows - VBA

[Ostemaden]

Hi,

The code below imports csv files from a folder into an Excel worksheet and then moves the files to a different folder.

The csv's individually contains only two values seperated by a semicolon, Value1 and Value2 (e.g.: File1 = 773463;CT778884343, File2 = 544543;GH832539371)

The Excel sheet I'm importing them into has five columns A, B, C, D, E and cannot be changed.

What I need to do is to import Value1 from CSV into Column A and add a constant suffix: "-A", Value2 into Column D and then put a constant in Column B, so the example above will look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]773463-A[/TD]
[TD]My constant[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT778884343[/TD]
[/TR]
[TR]
[TD]544543-A[/TD]
[TD]My constant[/TD]
[TD][/TD]
[TD][/TD]
[TD]GH832539371[/TD]
[/TR]
</tbody>[/TABLE]

The code:

[FONT='inherit'][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

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

    [COLOR=darkblue]Dim[/COLOR] strSourcePath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strDestPath [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] strData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/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]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=green]'Change the path to the source folder accordingly[/COLOR]
    strSourcePath = "C:\Path"
    
    [COLOR=darkblue]If[/COLOR] Right(strSourcePath, 1) <> "" [COLOR=darkblue]Then[/COLOR] strSourcePath = strSourcePath & ""
    
    [COLOR=green]'Change the path to the destination folder accordingly[/COLOR]
    strDestPath = "C:\Path"
    
    [COLOR=darkblue]If[/COLOR] Right(strDestPath, 1) <> "" [COLOR=darkblue]Then[/COLOR] strDestPath = strDestPath & ""
    
    strFile = Dir(strSourcePath & "*.csv")
    
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(strFile) > 0
        Cnt = Cnt + 1
        r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        [COLOR=darkblue]Open[/COLOR] strSourcePath & strFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
            [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] EOF(1)
                Line [COLOR=darkblue]Input[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData
                x = Split(strData, ",")
                [COLOR=darkblue]For[/COLOR] c = 0 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](x)
                    Cells(r, c + 1).Value = Trim(x(c))
                [COLOR=darkblue]Next[/COLOR] c
                r = r + 1
            [COLOR=darkblue]Loop[/COLOR]
        [COLOR=darkblue]Close[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Name strSourcePath & strFile [COLOR=darkblue]As[/COLOR] strDestPath & strFile
        strFile = Dir
    [COLOR=darkblue]Loop[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation
    [/FONT][COLOR=darkblue][FONT='inherit']End[/FONT][/COLOR][COLOR=darkblue][FONT='inherit']Sub[/FONT][/COLOR]

Thanks in advance