Solved

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

Posted on 2013-01-13
6
196 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
Independent Software Vendors: 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

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!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

697 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