Hello,
I know that similar questions were answered in the past but none of them are exactly what I'm looking for.
I'm trying to merge multiple CSV files into one master sheet and then archive the used files. Of course, I found this very useful thread where my question was answered.
All credits go to @Domenic for the Code
The code is working quite well and is doing what is suppose but ones a merge job is done the new files that should be merged the code will overlap the existing values(from the previous merge). What I would like to accomplish is every new file to be added to the end of the master sheet and not to the begging.
I know that similar questions were answered in the past but none of them are exactly what I'm looking for.
I'm trying to merge multiple CSV files into one master sheet and then archive the used files. Of course, I found this very useful thread where my question was answered.
All credits go to @Domenic for the Code
Code:
[COLOR=darkblue][FONT='inherit'][I]Option[/I][/FONT][/COLOR][COLOR=darkblue][FONT='inherit'][I]Explicit[/I][/FONT][/COLOR]
[FONT='inherit'][COLOR=darkblue]Sub[/COLOR] ImportCSV()[/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] strSourcePath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] strDestPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] strFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] strData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT='inherit'] Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR][/FONT]
[FONT='inherit'] [COLOR=green]'Change the path to the source folder accordingly[/COLOR][/FONT]
[FONT='inherit'] strSourcePath = "C:\Path\"[/FONT]
[FONT='inherit'] [COLOR=darkblue]If[/COLOR] Right(strSourcePath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strSourcePath = strSourcePath & "\"[/FONT]
[FONT='inherit'] [COLOR=green]'Change the path to the destination folder accordingly[/COLOR][/FONT]
[FONT='inherit'] strDestPath = "C:\Path\"[/FONT]
[FONT='inherit'] [COLOR=darkblue]If[/COLOR] Right(strDestPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strDestPath = strDestPath & "\"[/FONT]
[FONT='inherit'] strFile = Dir(strSourcePath & "*.csv")[/FONT]
[FONT='inherit'] [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(strFile) > 0[/FONT]
[FONT='inherit'] Cnt = Cnt + 1[/FONT]
[FONT='inherit'] r = Cells(Rows.Count, "A").End(xlUp).Row + 1[/FONT]
[FONT='inherit'] [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] [/FONT]
[FONT='inherit'] [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] EOF(1)[/FONT]
[FONT='inherit'] Line [COLOR=darkblue]Input[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData[/FONT]
[FONT='inherit'] x = Split(strData, ",")[/FONT]
[FONT='inherit'] [COLOR=darkblue]For[/COLOR] c = 0 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](x)[/FONT]
[FONT='inherit'] Cells(r, c + 1).Value = Trim(x(c))[/FONT]
[FONT='inherit'] [COLOR=darkblue]Next[/COLOR] c[/FONT]
[FONT='inherit'] r = r + 1[/FONT]
[FONT='inherit'] [COLOR=darkblue]Loop[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]Close[/COLOR] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/FONT]
[FONT='inherit'] Name strSourcePath & strFile [COLOR=darkblue]As[/COLOR] strDestPath & strFile[/FONT]
[FONT='inherit'] strFile = Dir[/FONT]
[FONT='inherit'] [COLOR=darkblue]Loop[/COLOR][/FONT]
[FONT='inherit'] Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR][/FONT]
[FONT='inherit'] [COLOR=darkblue]If[/COLOR] Cnt = 0 Then _[/FONT]
[FONT='inherit'] MsgBox "No CSV files were found...", vbExclamation[/FONT]
[COLOR=darkblue][FONT='inherit'][I]End[/I][/FONT][/COLOR][COLOR=darkblue][FONT='inherit'][I]Sub[/I][/FONT][/COLOR]
The code is working quite well and is doing what is suppose but ones a merge job is done the new files that should be merged the code will overlap the existing values(from the previous merge). What I would like to accomplish is every new file to be added to the end of the master sheet and not to the begging.