ExcelBanter - Import CSV to 1 sheet

I'm not clear on whether the data contained in the files can be dumped
straight into a worksheet or requires some effort to parse / calculate
the required data - this would affect whether to use WOrkbooks.Open or
Open #.

Here's something that should start you in the right direction. I'm
assuming the files are formatted as one line something like:

NAME, DATE, RATE

Read tgrough my code carefully to understand what it's doing - in
particular, you might not like that I delete the original file. So
please check.

Sub DailyFIleProcess()
Dim wb As Workbook
Dim sh As Worksheet
Dim F As Integer
Dim myFile As String
Dim myData As String
Dim mySplitData() As String
Dim iRow As Integer

Const myFolder As String = "\\server\myfolder\"
Const myFolderBackup As String = "\\server\myfolder\bkup\"

'make a new workbook and worksheet
Set wb = Workbooks.Add
Set sh = wb.Sheets(1)

'Write in the headers
iRow = 1
With sh
.Cells(iRow, 1) = "NAME"
.Cells(iRow, 2) = "DATE"
.Cells(iRow, 3) = "RATE"
End With

'Go through the folder until all the files
'have been processed.
myFile = Dir(myFolder & "*.csv")
Do While myFile < ""

'Get the data from the file
F = FreeFile
Open myFolder & myFile For Input As #F
Line Input #F, myData
Close #F

'split the data into an array on the comma delimiter
'(XL2000 and above only)
mySplitData = Split(myData, ",")

'write the data onto the worksheet
iRow = iRow + 1
With sh
.Cells(iRow, 1) = myData(0)
.Cells(iRow, 2) = myData(1)
.Cells(iRow, 3) = myData(2)
End With

'copy file to back up folder
FileCopy myFolder & myFile, myFolderBackup & myFile _
& "_" & Format(Now, "yyyy.mm.dd_hh.nn.ss")
'delete original file
Kill myFolder & myFile

'Check for another file
myFile = Dir(myFolder & "*.csv")
Loop

End Sub

JR wrote:
Hey guys, here's my situation...

We have about 15 individual CSV files come in daily. I would like to create
a macro to go through each of those files, however many they may be, and
separate the information into premade columns. As an example, I would like a
Name header, a Date header, and a Rate header. When I run the macro, I would
need it to shoot through maybe a dozen CSV files, take the name, date, and
rate information, and place it into the respective columns. It would then be
1 sheet, 12 rows long (not counting headers), with the individual
information.

I'm very new to macros. I've made a few attempts at piecing together macros
from other peoples' issues, but have had no luck. Any help would soo
appreciated!

Thanks,
JR