update stock prices

[Diana]

I have about 200 csv files in a directory.

For example, C:\myfolder\amd.csx, C:\myfoler\bux.csx, C:\myfolder\abc.csx ....

The csv files have the following format:

AMD, 12-Apr-00, 1.4, 1.3, 1.22, 1.5, 627044
AMD, 13-Apr-00, 1.33, 1.23, 1.45, 1.33, 667788
AMD..etc..

First value is the same as the file name, second value is a date
(dd-mmm-yy), the rest are numbers.

Now I have a .txt file. The format is:

AMD, 091012, 1.88, 1.67, 1.45, 1.23, 345678
BUX, 091012, 11, 11.35, 10.9, 11.2, 627044
ABC, 091012, 10.86, 10.89, 10.75, 10.75, 476009
etc.

The first value is the name of the csv file. The second value is the date
(yymmdd). The rest are numbers.

I get one such txt file per work day.

I want to open the text file, then open each corresponding csv (if found).
The first value of each line tells me which csv file to open. Now update the
csv file with the info in the .txt file. ie. it appends the relevant line
from the txt file to the csv file.

Thanks in advance!

 

[Dave Peterson]

Lightly tested. When you test it, copy a few of the .csv files to a test folder
and run against that.

If it blows up, you don't want to destroy your real data.

Option Explicit
Sub testme()
Dim TxtWks As Worksheet
Dim CSVWks As Worksheet
Dim TxtFileName As String

Dim myCell As Range
Dim myRng As Range

Dim myPath As String
Dim DestCell As Range

myPath = "C:\myfolder\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'make sure it has a .txt extension
TxtFileName = "C:\myfolder\textfilenamehere.txt"

Workbooks.OpenText Filename:=TxtFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlYMDFormat), _
Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), _
Array(5, xlGeneralFormat), _
Array(6, xlGeneralFormat), _
Array(7, xlGeneralFormat))

Set TxtWks = ActiveSheet

With TxtWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set CSVWks = Nothing
On Error Resume Next
Set CSVWks = Workbooks.Open _
(Filename:=myCell.Value & ".csv").Worksheets(1)
On Error GoTo 0

If CSVWks Is Nothing Then
MsgBox "No CSV file named: " & myCell.Value & ".csv"
Else
With CSVWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
'copy the entire row
myCell.EntireRow.Copy _
Destination:=DestCell
'dates are funny, so do extra work
With DestCell.Offset(0, 1)
.NumberFormat = "dd-mmm-yy"
.Offset(0, 1).Value2 = myCell.Offset(0, 1).Value2
End With
'save the CSV file
.Parent.Close savechanges:=True
End With
End If
Next myCell

TxtWks.Parent.Close savechanges:=False

End Sub

It's important that the text file has an extension of .txt. If it has an
extension of .csv, then VBA can't control how that date field comes in.

The CSV files are fine (I think). The date field is only slightly ambiguous. I
would have used a 4 digit year -- just to make sure there's never a doubt.