Merge CSV files into one master sheet

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='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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,801
Office Version
  1. 365
Platform
  1. Windows
Hi

I haven't done this for a while but last time I did I used an old DOS command:

Code:
RET = Shell("cmd.exe /C copy  """ & strSourcePath & "*csv"" """ & strSourcePath & "Combine.csv""", 0)
 

ManeVane

New Member
Joined
Aug 12, 2018
Messages
3
Hi Jon,
Thank you for your reply.
Current if I'm wrong but I need to define the name of the sheets that I want to merge right? This won't really work for me because the file name will be different every time.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,007
Messages
5,526,247
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top