open text file, get file from directory, update file

[Diana]

I have a text file. Lots of rows. The second field is a date yymmdd. It looks
like:

ABC, 091012, 1.47, 1.51, 1.46, 1.495, 586907
XYZ, 091012, ....
etc...

I want to take the value in the first field, in this case "ABC", open a file
called ABC_XXX.csv. I don't care what XXX is, as long as the ABC part matches.

Format of the csv file is:
ABC, 10-Aug-99, 0.9734, 0.9567, etc.
ABC, 11-Aug-99, etc..
etc...

I want to append the row from the text file to the .csv file.

So that the ABC_XXX.csv file has the row:

ABC, 12-Oct-09, 1.47, 1.51, 1.46, 1.495, 586907

added. Date reformatted from 091012 to dd-mmm-yy.

 

[marcus]

Hi Diana

This should do what you want to do. It assumes you are wanting to use
the first field in the current file as the criteria for the CSV file
to open.

Take care

Marcus

Option Explicit
Sub OpenCSVFile()

Dim sPath As String
Dim sFil As String
Dim strName As String
Dim myRow As Integer
Dim PName As String

Rows(1).Copy
PName = Left(Cells(1, 1).Value, 3) ' Column 1 for the csv criteria
sPath = "C:\" ' Change to suit
sFil = Dir(sPath & PName & "*.csv")

Do While sFil <> ""

strName = sPath & sFil
Workbooks.Open (strName)
sFil = Dir
Loop
'Pastes to the last row in the CSV file
myRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(myRow, 1).PasteSpecial xlValues
Cells(myRow, 2).NumberFormat = "dd-mmm-yy"

End Sub

 

[Jacob Skaria]

If the .csv files are present then you can ignore the second loop...

Sub Macro()

Dim strTextFile As String, strCSVFile As String
Dim intTextFile As Integer, intCSVFile As Integer
Dim strData As String, arrData As Variant
Dim strCSVPath As String, intFile As Integer

strTextFile = "d:\donna.txt"
strCSVPath = "d:"

If Right(strCSVPath, 1) <> "\" Then strCSVPath = strCSVPath & "\"
intTextFile = FreeFile
Open strTextFile For Input As #intTextFile
Do While Not EOF(intTextFile)
Line Input #intTextFile, strData
arrData = Split(strData, ",")
arrData(1) = Right(Trim(arrData(1)), 2) & "-" & Format("13-" & _
Mid(Trim(arrData(1)), 3, 2) & "-2009", "MMM") & "-20" &
Left(Trim(arrData(1)), 2)
strCSVFile = Dir(strCSVPath & arrData(0) & "*.csv", vbNormal)
intCSVFile = FreeFile
Open strCSVPath & strCSVFile For Append As #intCSVFile
Print #intCSVFile, Join(arrData, ",")
Close #intCSVFile
Loop
Close #intTextFile

End Sub