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

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