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
