Txt Files and Max Formulas??

[BKexcel]

I'm helping a friend with a research project and I've met my match.

She has roughly 1000 .txt files that contain data (when opened in excel) in A15:IV254, (yes, that's 61,440 data points per sheet!) and she is trying to

a. find the max value in each file,
b. find the cell location of that value.

One solution I had for the first part was writing an Max(Indirect( formula to reference the file location and the data set, but I'm trying to avoid converting all the files to excel as I don't know a way to do this besides opening each one and saving as an excel workbook and then copy/pasting the file location in some master file. I'm sure there is no simple solution, but any help to take a few steps out of the mix would be awesome.

Thanks!
BK

 

[Domenic]

The following macro loops through each text file within the specified folder, and uses the Open statement to find the maximum values. A new worksheet is created, where it lists the filename, cell address, and maximum value for each text file.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] ListMaxValsForTextFiles()

    [COLOR=darkblue]Dim[/COLOR] sSourceFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sCurrFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aResults() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] MaxVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sAddress [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] FileCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LineCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sTextLine [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aTemp() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Const[/COLOR] StartRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] = 15 [COLOR=green]'data starts in Row 15[/COLOR]
    
    sSourceFolder = "C:\Users\Domenic\Desktop\" [COLOR=green]'change the path accordingly[/COLOR]
    [COLOR=darkblue]If[/COLOR] Right(sSourceFolder, 1) <> "\" [COLOR=darkblue]Then[/COLOR]
        sSourceFolder = sSourceFolder & "\"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    sCurrFile = Dir(sSourceFolder & "*.txt")
    [COLOR=darkblue]If[/COLOR] Len(sCurrFile) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "No text files found.", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    FileCnt = 0
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(sCurrFile) > 0
        FileCnt = FileCnt + 1
        [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aResults(1 [COLOR=darkblue]To[/COLOR] 3, 1 [COLOR=darkblue]To[/COLOR] FileCnt)
        aResults(1, FileCnt) = sCurrFile
        LineCnt = 0
        MaxVal = 0
        sAddress = ""
        [COLOR=darkblue]Open[/COLOR] sSourceFolder & sCurrFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #1
            [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] EOF(1)
                LineCnt = LineCnt + 1
                Line [COLOR=darkblue]Input[/COLOR] #1, sTextLine
                [COLOR=darkblue]If[/COLOR] LineCnt >= StartRow [COLOR=darkblue]Then[/COLOR]
                    aTemp() = Split(sTextLine, vbTab)
                    [COLOR=darkblue]For[/COLOR] i = 0 To [COLOR=darkblue]UBound[/COLOR](aTemp)
                        sTemp = aTemp(i)
                        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsEmpty(sTemp) And IsNumeric(sTemp) [COLOR=darkblue]Then[/COLOR]
                            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]CDbl[/COLOR](sTemp) > MaxVal [COLOR=darkblue]Then[/COLOR]
                                MaxVal = [COLOR=darkblue]CDbl[/COLOR](sTemp)
                                sAddress = Evaluate("Address(" & LineCnt & ", " & i + 1 & ", 4)")
                                aResults(2, FileCnt) = sAddress
                                aResults(3, FileCnt) = MaxVal
                            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                    [COLOR=darkblue]Next[/COLOR] i
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Loop[/COLOR]
        [COLOR=darkblue]Close[/COLOR] #1
        sCurrFile = Dir
    [COLOR=darkblue]Loop[/COLOR]
    
    Worksheets.Add
    [COLOR=darkblue]With[/COLOR] Range("A1:C1")
        .Value = Array("Filename", "Cell Address", "Max Amount")
        .Font.Bold = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Range("A2").Resize(UBound(aResults, 2), UBound(aResults, 1)).Value = Application.Transpose(aResults)
    Columns("A:C").AutoFit
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[BKexcel]

The following macro loops through each text file within the specified folder, and uses the Open statement to find the maximum values. A new worksheet is created, where it lists the filename, cell address, and maximum value for each text file.


Wow - I'm blown away. Thank you so much for the help this is exactly what we were looking for!

 

[Domenic]

That's great, thanks for the feedback!

Cheers!

 

[bielsk]

That's great, thanks for the feedback!

Cheers!

Hi,

I am the friend BKexcel was helping with the research project. Your last solution worked perfectly and I really appreciate your help.

I have tried altering your MACRO to answer my next question, but I can't figure it out. I am interested in finding the exact number of cells that are greater than 200 (not equal to) in all of the same 1,000+ .txt files in rows 15-150. Any suggestions would be greatly appreciated!

Thanks,
B