For all of you who want to open multiple text files from within VBA here is the code that will do just that. Here I open a Open File Dialog and enable multiple file selection. I then parse the return string to gather the path and individual file names. The data is then processed – I am looking for a file with a date and a value separated by a comma. You can basically do anything you want with VBA and you also have the EXCEL functions right at your fingertips. Plus output to a worksheet is ultra simple.
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
Set ffs = .Filters
With ffs
.Clear
.Add "CSV Data", "*.csv; *.txt"
End With
.AllowMultiSelect = True
If .Show = False Then Exit Sub
fcnt = 0
maxDate = DateValue("01/01/1900")
minDate = DateValue("12/31/2200")
prevSymbol = "**"
dateNewestToOldest = False
For Each it In fd.SelectedItems
fcnt = fcnt + 1
filePath(fcnt) = it
fileName(fcnt) = Right(filePath(fcnt), Len(filePath(fcnt)) - InStrRev(filePath(fcnt), "\"))
symbol(fcnt) = Left(fileName(fcnt), 2)
Set DataHolder = New DataClass
If symbol(fcnt) <> prevSymbol Then symbolHolder.Add symbol(fcnt)
prevSymbol = symbol(fcnt)
dataCnt = 0
Open filePath(fcnt) For Input As #1
rowNumber = 1
Do Until EOF(1)
Line Input #1, lineFromFile
dataCnt = dataCnt + 1
If dataCnt > skipLines Then
lineFromFile = Replace(lineFromFile, " ", "")
lineItems = Split(lineFromFile, ",")
numItems = UBound(lineItems) - LBound(lineItems) + 1
isDash1 = InStr(lineItems(0), "-")
isSlash1 = InStr(lineItems(0), "/")
If isDash1 > 0 Or isSlash1 > 0 Then
inputDate = CDate(lineItems(0))
Else
myYear = Left(lineItems(0), 4)
myMonth = Mid(lineItems(0), 5, 2)
myDay = Right(lineItems(0), 2)
inputDate = DateSerial(Left(lineItems(0), 4), Mid(lineItems(0), 5, 2), Right(lineItems(0), 2))
End If
If dataCnt > 1 Then
If inputDate < tempDate Then dateNewestToOldest = True
End If
If inputDate > maxDate Then maxDate = inputDate
If inputDate < minDate Then minDate = inputDate
Set DataHolder = New DataClass
DataHolder.SymbolName = symbol(fcnt)
DataHolder.SymbolNum = fcnt
DataHolder.EquDate = inputDate
DataHolder.EquVal = lineItems(1)
myEquityCollection.Add DataHolder
tempDate = inputDate
End If
Loop
Close #1
Next it
End With
Hello to All! I just published the first book in this series. It is the Foundation Edition and is designed for the new user of EasyLanguage or for those you would like to have a refresher course. There are 13 tutorials ranging from creating Strategies to PaintBars. Learn how to create your own functions or apply stops and profit objectives. Ever wanted to know how to find an inside day that is also a Narrow Range 7 (NR7?) Now you can, and the best part is you get over 4 HOURS OF VIDEO INSTRUCTION – one for each tutorial. All source code is available too, and if you have TradeStation, so are the workspaces. Plus you can always email George for any questions. george.p.pruitt@gmail.com.
Pick you your copy today – e-Book or Paperback format!
