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
