Merge CSV files into one master sheet

Status
Not open for further replies.

ManeVane

New Member
Joined
Aug 12, 2018
Messages
3
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
Code:
[COLOR=darkblue][FONT=Verdana][I]Option[/I][/FONT][/COLOR][COLOR=darkblue][FONT=Verdana][I]Explicit[/I][/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Sub[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] ImportCSV()[/FONT][/COLOR]

[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strSourcePath [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]String[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strDestPath [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]String[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strFile [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]String[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strData [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]String[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] x [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Variant[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] Cnt [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Long[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] r [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Long[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Dim[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] c [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Long[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]  Application.ScreenUpdating = [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]False[/FONT][/COLOR]

[COLOR=green][FONT=Verdana]'Change the path to the source folder accordingly[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  strSourcePath = "C:\Path\"[/FONT][/COLOR]

[COLOR=darkblue][FONT=Verdana]If[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] Right(strSourcePath, 1) <> "\" [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Then[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strSourcePath = strSourcePath & "\"[/FONT][/COLOR]

[COLOR=green][FONT=Verdana]'Change the path to the destination folder accordingly[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  strDestPath = "C:\Path\"[/FONT][/COLOR]

[COLOR=darkblue][FONT=Verdana]If[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] Right(strDestPath, 1) <> "\" [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Then[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strDestPath = strDestPath & "\"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]  strFile = Dir(strSourcePath & "*.csv")[/FONT][/COLOR]

[COLOR=darkblue][FONT=Verdana]Do[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]While[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] Len(strFile) > 0[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      Cnt = Cnt + 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      r = Cells(Rows.Count, "A").End(xlUp).Row + 1[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Open[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strSourcePath & strFile [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]For[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Input[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Do[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Until[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] EOF(1)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]              Line [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]Input[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strData[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]              x = Split(strData, ",")[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]For[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] c = 0 [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]To[/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]UBound[/FONT][/COLOR][COLOR=#222222][FONT=Verdana](x)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                  Cells(r, c + 1).Value = Trim(x(c))[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Next[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] c[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]              r = r + 1[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Loop[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Close[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      Name strSourcePath & strFile [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]As[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] strDestPath & strFile[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      strFile = Dir[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana]Loop[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]  Application.ScreenUpdating = [/FONT][/COLOR][COLOR=darkblue][FONT=Verdana]True[/FONT][/COLOR]

[COLOR=darkblue][FONT=Verdana]If[/FONT][/COLOR][COLOR=#222222][FONT=Verdana] Cnt = 0 Then _[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]      MsgBox "No CSV files were found...", vbExclamation[/FONT][/COLOR]
[COLOR=darkblue][FONT=Verdana][I]End[/I][/FONT][/COLOR][COLOR=darkblue][FONT=Verdana][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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
Duplicate https://www.mrexcel.com/forum/excel...-files-into-one-master-sheet.html#post5123449

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top