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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top