Solved

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

Posted on 2013-01-13
6
188 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
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
Comment Utility
thanks
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, rgb192.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now