contactdouglas
New Member
- Joined
- Aug 27, 2012
- Messages
- 3
I am trying to extract data from multiple text files in a folder. I have a rough macro but I am having problems determining the line number where my data is located or actually extracting the data. The line number of the data may change from file to file but I have located a key word only present once in the file a few lines before the data I can key off of (CalibrateOffsets). UO and VO appear in other places so I cannot use them to search. The line number changes so I cannot use a set line number. I have not found a good way in the macro to find the line number of my key word or better yet read in my data a few lines after into excel. Here is a snapshot of the text file where I need the data uo and vo :
2011-01-05 10:46:01.93 Step Current Calibration CalibrateOffsets
Error Code:
Results: SUCCESS: Calibrated offsets on 2 tries. Max variation=0 counts between tries, <= 1 limit.
UO=4 VO=6 WO=0
My excel macro below can step through the files but I cannot find away to grab the data.
Thanks!
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub CalibrationOffset()
'
'
Dim dataFolder As String
Dim dataFile As String
Dim strData As String
Dim Dataline As Integer
Dim LineNo As Integer
Dim UO As Integer
Dim VO As Integer
Dim SearchString As String
Dim fs, f
Dim Data As String
fs = FreeFile
SearchString = CalibrateOffsets
Dataline = 1
LineNo = 0
UO = 0
VO = 0
dataFolder = "C:\data\"
dataFile = Dir(dataFolder & "*.log")
'Loop through each text file within the folder
Do While Len(dataFile) > 0
'Open the current text file
'Set f = fs.OpenTextFile(dataFolder & dataFile, 1, TristateFalse)
Open dataFolder & dataFile For Input As #fs
'Import the data from the current text file
Do Until EOF(1) ' Loop until end of file
'CalibrateOffsets = search term just before actual UO and VO values to be extracted
'LineNo = StringExistsInFile("CalibrateOffsets")
'LineNo = InStr(SearchString, c)
Line Input #fs, Data
ActiveSheet.Cells(5, 4) = Data
LineNo = LineNo + 1
Loop
UO = 0
VO = 0
dataFile = Dir 'Call the next text file
Loop
MsgBox "Completed!", vbInformation
End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]
2011-01-05 10:46:01.93 Step Current Calibration CalibrateOffsets
Error Code:
Results: SUCCESS: Calibrated offsets on 2 tries. Max variation=0 counts between tries, <= 1 limit.
UO=4 VO=6 WO=0
My excel macro below can step through the files but I cannot find away to grab the data.
Thanks!
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Sub CalibrationOffset()
'
'
Dim dataFolder As String
Dim dataFile As String
Dim strData As String
Dim Dataline As Integer
Dim LineNo As Integer
Dim UO As Integer
Dim VO As Integer
Dim SearchString As String
Dim fs, f
Dim Data As String
fs = FreeFile
SearchString = CalibrateOffsets
Dataline = 1
LineNo = 0
UO = 0
VO = 0
dataFolder = "C:\data\"
dataFile = Dir(dataFolder & "*.log")
'Loop through each text file within the folder
Do While Len(dataFile) > 0
'Open the current text file
'Set f = fs.OpenTextFile(dataFolder & dataFile, 1, TristateFalse)
Open dataFolder & dataFile For Input As #fs
'Import the data from the current text file
Do Until EOF(1) ' Loop until end of file
'CalibrateOffsets = search term just before actual UO and VO values to be extracted
'LineNo = StringExistsInFile("CalibrateOffsets")
'LineNo = InStr(SearchString, c)
Line Input #fs, Data
ActiveSheet.Cells(5, 4) = Data
LineNo = LineNo + 1
Loop
UO = 0
VO = 0
dataFile = Dir 'Call the next text file
Loop
MsgBox "Completed!", vbInformation
End Sub
[/TD]
[/TR]
</TBODY>[/TABLE]