Weather Data Average Macro

[Benito334]

I have a macro that pulls meteorological data from various sensor data files (txt CSV files) on a network and puts them in a spreadsheet when the button is pushed. The macro reads the data files until the last/newest string is read, as a loop function. It takes the newest/last value and enters it in the spreadsheet. For the wind data I would like to take the average of say the last 10 measurements instead of the very last one. Can anyone help me with the best way to do this? Can I do it with a similar loop function?

Here is a sample string of the wind data file:
The values I want to average are the first number just after the $DERIV which is the wind direction

09/08/2011,00:00:05.682,$DERIV,40.47,36.98,38.7990604473582,45.4833040628672,60,
09/08/2011,00:01:05.682,$DERIV,40.89,38.42,38.3839658290715,44.3314055621409,59,
09/08/2011,00:02:05.682,$DERIV,35.3,39.38,34.487867337798,48.7034351812146,60,

Here is the Macro so far. I haven't written in anything for the wind data yet but you can see the functions for the other data:

Const NavDirectory = "GPS_Data\"
Const DriveSpec = "U:\"
Const GPGGAFileSpec = "*GP150-NMEA-GPGGA*"
Const MetDirectory = "Meteorlogic_Data\"
Const SAMOSDirectory = "SAMOS\"
Const SSTFileSpec = "*SAMOS-SST-DA_*"
Const YOUNGFileSpec = "*YOUNG-NMEA-WIXDR_*"
Const SeparatorCharacter = ","


Public Function GetLastString(FileName As String) As String
Dim FileNum As Integer, tLine As String
FileNum = FreeFile ' next file number
On Error GoTo exitFunction
Open FileName For Input Access Read Shared As #FileNum ' open the file for reading
Do While Not EOF(FileNum)
Line Input #FileNum, tLine ' read a line from the text file
Loop ' until the last line is read
Close #FileNum ' close the file
'MsgBox tLine, vbInformation, "Last log information:"
GetLastString = tLine
exitFunction:

End Function
Public Function GetLatLonFileName() As String
GetLatLonFileName = NewestFile(DriveSpec, NavDirectory, GPGGAFileSpec)
End Function
Public Function GetYOUNGFileName() As String
GetYOUNGFileName = NewestFile(DriveSpec, MetDirectory, YOUNGFileSpec)
End Function
Public Function GetSSTFileName() As String
GetSSTFileName = NewestFile(DriveSpec, SAMOSDirectory, SSTFileSpec)
End Function
Function NewestFile(Drive, Directory, FileSpec)
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*TSG*").
' Returns an empty string if the directory does not exist or
' it contains no matching files
Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
FileName = Dir(Drive & Directory & FileSpec, vbNormal)
If FileName <> "" Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(Drive & Directory & FileName)
Do While FileName <> ""
If FileDateTime(Drive & Directory & FileName) > MostRecentDate Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(Drive & Directory & FileName)
End If
FileName = Dir
Loop
End If
NewestFile = Drive + Directory + MostRecentFile
End Function
Function ParseDelimitedString(InputString As String, SepChar As String) As Variant
' returns a variant array containing each single item in
' InputString separated by SepChar
Dim i As Integer, tString As String, tChar As String * 1
Dim sCount As Integer, ResultArray() As Variant
tString = ""
sCount = 0
For i = 1 To Len(InputString)
tChar = Mid$(InputString, i, 1)
If tChar = SepChar Then
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
tString = ""
Else
tString = tString & tChar
End If
Next i
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
ParseDelimitedString = ResultArray
End Function


Sub UpdateFields()
Rownum = ActiveCell.Row

LatLonValues = ParseDelimitedString(GetLastString(GetLatLonFileName()), SeparatorCharacter)
LatDeg = (LatLonValues(5) - Right("00" + LatLonValues(5), 7)) / 100
LatMin = Right("00" + LatLonValues(5), 7)
LatSign = LatLonValues(6)
LonDeg = (LatLonValues(7) - Right("00" + LatLonValues(7), 7)) / 100
LonMin = Right("00" + LatLonValues(7), 7)
LonSign = LatLonValues(8)
YoungValues = ParseDelimitedString(GetLastString(GetYOUNGFileName()), SeparatorCharacter)
Press = YoungValues(16)
AirTemp = YoungValues(4)
WBAirTemp = YoungValues(13)
SSTValues = ParseDelimitedString(GetLastString(GetSSTFileName()), SeparatorCharacter)
SST = SSTValues(4)

Range("B" & Rownum) = LatDeg
Range("C" & Rownum) = LatMin
Range("D" & Rownum) = LatSign
Range("E" & Rownum) = LonDeg
Range("F" & Rownum) = LonMin
Range("G" & Rownum) = LonSign
Range("A" & Rownum) = Date + Time
Range("O" & Rownum) = Press
Range("P" & Rownum) = AirTemp
Range("Q" & Rownum) = WBAirTemp
Range("N" & Rownum) = SST
Selection.Font.Bold = True
Range("A" & Rownum).Select

ActiveWorkbook.Save
End Sub

 

[Gary McMaster]

I would be inclined to split each line into pieces at the comma as it is read by Line Input. The vSplit variable (variant array) would contain each of the delimited elements. I would write the third element (zero based) "Wind Dir" into a 10 element array. When the array is filled, start overwriting at element 1 so as to end up with the last 10.

Hope this helps.

Gary


Untested due to lack of proper files & paths:
(Existing lines in red)

Dim iCount As Integer
Dim vSplit As Variant
Dim sWindDir(1 To 10) As String 'Last 10 readings
Dim dTotal As Double

iCount = 1

Open FileName For Input Access Read Shared As #FileNum ' open the file for reading

Do While Not EOF(FileNum)

Line Input #FileNum, tLine ' read a line from the text file

vSplit = Split(tLine, ",") 'Split the string at the commas
sWindDir(iCount) = vSplit(3) 'Add the 3rd comma delimited value to the array
If iCount = 10 Then iCount = 1 ' Continuously overwrite array to capture only last 10

Loop ' until the last line is read

For iCount = 1 To 10
    dTotal = dTotal + Val(sWindDir(iCount))
Next iCount

MsgBox dTotal / 10'Average of last 10


Close #FileNum ' close the file