Read external data from dynamic text range, then calculate stats

[raymam1]

Using VBA and Excel, I need to create a workbook that will summarize a variety of statistics from a log. I need to read the log file from the text into Excel using VBA. I need to make the code dynamic enough to allow for different products (rows). I then need to summarize these statistics in a table, and create a button to run the program.

This does not seem overly difficult, though I don't know where to start searching to find my answers. Any help would be great!

[Tinbendr]

This does not seem overly difficult, though I don't know where to start searching to find my answers. Talk about a oxymoron (http://www.merriam-webster.com/dictionary/oxymoron?show=0&t=1296834339).

Maybe these will get you started.
Much ADO About Text Files (http://msdn.microsoft.com/en-us/library/ms974559.aspx)
Incorrect Field Types in ADODB Recordsets (http://www.vbaexpress.com/forum/showthread.php?t=35779)

We need Kenneth. KENNETH, WHERE ARE YOU?

[raymam1]

Talk about a oxymoron.

Tin, you're absolutely right! It was late, and I wanted to make sure everyone was on their toes... haha

Thanks for those links. The Microsoft page appears to have my answer. I'll work on it and repost if I run into problems (which is rather likely).

[raymam1]

OK, I now have the code to read my external text file without prompting me to choose the file.

However, now I need to identify each unique entry listed in the first column of the text file, then output those entries into new rows in Excel.

Here is my code so far:

Public Sub ReadTextFile()

Dim iPtr As Integer
Dim sFileName As String
Dim intFH As Integer
Dim sRec As String

sFileName = "Y:\Desktop\__002c Trade Log.txt"
Close
intFH = FreeFile()
Open sFileName For Input As intFH

Do Until EOF(intFH)
Line Input #intFH, sRec
' ---------------------------------------------------------------------

'Can I identify the unique entries and display them in the loop?

' ---------------------------------------------------------------------
DoEvents
Loop

Close intFH

End Sub

Thanks!

[raymam1]

OK, now I'm having trouble applying this code:

Sub FilterUnique()
Dim rng As Range, Dn As Range
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, ""
End If
Next
Range("H2").Resize(.Count).Value = Application.Transpose(.Keys)
End With
End Sub

to my original posted code that allows me to read the data from a text file.

Any help would be great!

[Kenneth Hobs]

The method that I would choose depends on the format of the text file. Typical methods like Collections and Dictionary Object could be added to your text reading code to remove duplicates probably. It is probably more efficient to do it in your text file reading than doing it from a worksheet range.

To help you best, post short example text and xlsm files. Usually, the more simple the better.

This collection method could be adapted if you want to do the removal in a range.
Sub test()
Dim r As Range, vRange As Variant, s As String
Set r = Range("G2", Cells(Rows.Count, "G").End(xlUp))
vRange = UniqueValues(r)
s = Join(vRange, ";")
Debug.Print s
End Sub


Public Function UniqueValues(theRange As Range) As Variant
Dim colUniques As New VBA.Collection
Dim vArr As Variant
Dim vCell As Variant
Dim vLcell As Variant
Dim oRng As Excel.Range
Dim i As Long
Dim vUnique As Variant
Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
vArr = oRng
On Error Resume Next
For Each vCell In vArr
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
colUniques.Add vCell, CStr(vCell)
End If
End If
vLcell = vCell
Next vCell
On Error GoTo 0

ReDim vUnique(1 To colUniques.Count)
For i = LBound(vUnique) To UBound(vUnique)
vUnique(i) = colUniques(i)
Next i

UniqueValues = vUnique
End Function