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!
<TBODY>
</TBODY>
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!
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 |
<TBODY>
</TBODY>