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
Solved

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

Posted on 2013-01-13
6
195 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
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Turn date into age 17 33
Clear a Text Box 7 25
Expanding and filling year gaps in multiple listings 6 25
Calculate waiting times that spans two days 8 28
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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