VBA Code to consolidate .csv data

Question

I am trying to consolidate a specific range of date from many csv files. What I want to do is to select this range and paste it into a master sheet in a separate workbook. The .csv files are all arranged in one folder and all have 1 sheet in the same format. The range can be dynamic so this code will need to be able to select all the rows below a cell or be able to delete blank rows from a larger range. I would appreciate any help.

Thanks

Answer 1

I've used a batch file to something like this in the past. This code does not handle deleting Aggregate.csv if the batch file is ran again.

@ECHO OFF  
Set loc=C:\Test\
Copy %loc%*.csv %loc%\Aggregate.csv

Once in Excel, you can delete all the header rows and filter date ranges with VBA. You can also use VBA's Shell method to aggregate with Copy.

Edit: You can also create a data source in Other Data Sources > MS Query in order to query Aggregate.csv with Microsoft Text Driver using date ranges, etc.

Answer 2

Some pointers how to go about the solution:

First, enumerate the files using the FileSystemObject.

Set fso = CreateObject("Scripting.FileSystemObject")
set fld = fso.GetFolder("path\to\my\folder")
For Each file in fld.Files
    If file.Name Like "*.csv" Then LoadFile file.Name
Next

Declare fso, fld, file as Object. LoadFile will be a function you have to write, which processes a single file. It will look approximately like this:

Sub LoadFile(filename as String)
    dim buffer() as variant
    dim wb as workbook, ws as worksheet
    dim i as Long, beginrow as long, endrow as long

    Set wb = Workbooks.Open(filename)
    Set ws = wb.Worksheets(1)  ' .csv always has 1 worksheet
    buffer = ws.Range("A1:A10000")  ' put a sensible upper bound here
    for i = 1 to 10000
         ' replace (..first..) and (..last..) with your search interval
         if buffer(i, 1) <= (..first..) Then beginrow = i
         if buffer(i, 1) < (..last..) Then endrow=i
    next
    ' now beginrow and endrow hold the interval to cut
    ws.Cells(beginrow, 1).Resize(endrow-beginrow+1, column_count).Copy destination:=(... to be determined ..)
    wb.Close
End Sub

The function opens the file; then searches the first column for the interval to copy; then copies the cells and closes the file.

The code is not runnable as-is, but should hopefully give you the right ideas.