Solved

combine 13 similar files  (without opening each and copy paste)

Posted on 2013-01-13
6
197 Views
Last Modified: 2013-01-13
my bank has csv statements for each month (I called and they do not have for all year)


so how can I combine 13 files

first file downloaded is datafile0.csv to datafile12.cvs
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38772855
Hi, rgb192.

Please see attached. Let me know if you want the second and subsequent files to skip a header row.

The code is...
Option Explicit

Sub Process_CSV()
Dim xCSVFileName  As Variant
Dim xCSVFile      As Workbook
Dim xBook         As Workbook
Dim xSheet        As Worksheet
Dim xOut_New_Last As Long

Set xSheet = Sheets.Add
xOut_New_Last = 0

Application.ScreenUpdating = False

    For Each xCSVFileName In Array("datafile0.csv", "datafile1.csv", "datafile2.csv", "datafile3.csv", "datafile4.csv", "datafile5.csv", "datafile6.csv", "datafile7.csv", "datafile8.csv", "datafile9.csv", "datafile10.csv", "datafile11.csv", "datafile12.csv")
        Set xCSVFile = Workbooks.Open("D:\" & xCSVFileName)
    
        ActiveSheet.UsedRange.Copy Destination:=xSheet.Range("A" & xOut_New_Last + 1)
        xOut_New_Last = xSheet.Range("A1").SpecialCells(xlLastCell).Row
        
        xCSVFile.Close savechanges:=False
    Next
  
Application.ScreenUpdating = True
  
End Sub

Open in new window

Regards,
Brian.Merge-CSV-s.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772882
rgb192,

The attached skips row 1 for all files except the first. The code is...
Option Explicit

Sub Merge_CSVs()
Dim xCSVFileName As Variant
Dim xCSVFile     As Workbook
Dim xBook        As Workbook
Dim xSheet       As Worksheet
Dim xOut_Last    As Long
Dim xCSV_Last    As Long
Dim xCount       As Long

Set xSheet = Sheets.Add

Application.ScreenUpdating = False

    For Each xCSVFileName In Array("datafile0.csv", "datafile1.csv", "datafile2.csv", "datafile3.csv", "datafile4.csv", "datafile5.csv", "datafile6.csv" _
                                 , "datafile7.csv", "datafile8.csv", "datafile9.csv", "datafile10.csv", "datafile11.csv", "datafile12.csv")
        Set xCSVFile = Workbooks.Open("D:\" & xCSVFileName)

        xCount = xCount + 1
        If xCount <> 1 Then xOut_Last = xSheet.Range("A1").SpecialCells(xlLastCell).Row
        xCSV_Last = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
        
        ActiveSheet.Range(IIf(xCount = 1, 1, 2) & ":" & xCSV_Last).Copy Destination:=xSheet.Range("A" & xOut_Last + 1)
        
        xCSVFile.Close savechanges:=False
    Next
  
Application.ScreenUpdating = True
  
End Sub

Open in new window

Regards,
Brian.Merge-CSV-s-V2.xlsm
0
 

Author Comment

by:rgb192
ID: 38772898
yes row 1 is the header


how do I run your code
can I create a file in notepad and right click on your code on the desktop
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38772919
rgb192,

Just open Merge-CSV-s-V2.xlsm and click on the blue button.

Alternatively, simply opening the attached version causes the macro to run. So you could leave this on your Desktop and just double-click on it to do the job.

Edit: Please let me know which folder the CSV's are in and I'll update the macro - currently it looks for them in "D:\". If you prefer, the macro could prompt you each time for the folder.

Edit2: I've just remembered that there's a bug in at least some versions of Excel for users whose system date is not in "mm/dd/yyyy" format - if a macro reads in a CSV file with a date column then the date is messed up. What format is your system date?

Edit3: The blue button in the attached now points to the correct macro.

Regards,
Brian.Merge-CSV-s-V3.xlsm
0
 

Author Closing Comment

by:rgb192
ID: 38773162
thanks
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38773169
Thanks, rgb192.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question