Free EasyLanguage, Uncategorized

VBA for Excel Code to Open Multiple Text Files

Leave a comment

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
VBA code to open multiple text files

Discover more from George Pruitt

Subscribe to get the latest posts sent to your email.

Leave a Reply