I am trying to create a VB script that will copy the last time value from a text file to a specific spreadsheet cell. An example is
BEGIN OF CPPIG000_Lamda_Batch(Morning) DATE: 01/12/2012 01:29:11ENDED OF CPPIG000_Lamda_Batch(Morning) DATE: 01/12/2012 01:50:01 Elapsed Time: 00:20:50
===========================================================================================================BEGIN OF CPPIG000_Lamda_Batch(Morning) DATE: 04/12/2012 04:23:30
ENDED OF CPPIG000_Lamda_Batch(Morning) DATE: 04/12/2012 04:28:48 Elapsed Time: 00:05:18
===========================================================================================================BEGIN OF CPPIG000_Lamda_Batch(Morning) DATE: 05/12/2012 03:19:46
ENDED OF CPPIG000_Lamda_Batch(Morning) DATE: 05/12/2012 03:35:14 Elapsed Time: 00:15:28
===========================================================================================================BEGIN OF CPPIG000_Lamda_Batch(Morning) DATE: 06/12/2012 02:46:39
ENDED OF CPPIG000_Lamda_Batch(Morning) DATE: 06/12/2012 02:57:01 Elapsed Time: 00:10:22
===========================================================================================================The value is the 00:10:22.
I have tried reading the text file line by line but cnt seem to get it to store just the value i want to a var.
Any help would be very much appreciated.
Thank you in advance
Currently at home now so do have access to the script bt ill post it in the am. What i have so far is im comparing the log file from the day before and then outputting the last run to a seperate text file as the logs tend to get to big and from my little understanding it will be easier to work with something that i know the exact positions.
The actual problem that i see will be to output the value to a specified excel cell.
Will try figure it out when at work and post the progress.
Thanx for the repy.
What i have so far is 4 separate scripts, one to compare the file against the previous run and output the difference to a text file. One to delete the top line, one to delete the preceding characters and one to delete the characters on the end. So I just end up with 00:10. Now what really boggles me is how am i going to get that value in to excel on a specified cell?
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Cepig0002.txt", ForReading)strCurrentDevices = objFile1.ReadAll
objFile1.CloseSet objFile2 = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Cepig000.txt", ForReading)
Do Until objFile2.AtEndOfStream
strAddress = objFile2.ReadLine
If InStr(strCurrentDevices, strAddress) = 0 Then
strNotCurrent = strNotCurrent & strAddress & vbCrLf
End If
LoopobjFile2.Close
Set objFile3 = objFSO.CreateTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt")
objFile3.WriteLine strNotCurrent
objFile3.Close
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFS.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt")
Set objFS1 = objFS.CreateTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\temp.txt",true)
objFS1.close
set objTextFile2 = objFS.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\temp.txt",8)
strFile = "C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt"Do Until objTextFile.AtEndOfStream
strLine = objTextFile.ReadLine
If InStr(strLine,"BEGIN")> 0 Then
strLine = Replace(strLine,"BEGIN",1)
Else
objTextFile2.writeline strLine
End If
Loopset objTextFile = nothing
set objTextFile2 = nothingobjFS.copyFile "C:\Documents and Settings\g9007l2\Desktop\scripts testing\temp.txt", "C:\Documents and
Settings\g9007l2\Desktop\scripts testing\Differences.txt", true
objfs.DeleteFile "C:\Documents and Settings\g9007l2\Desktop\scripts testing\temp.txt"set objfs = nothing
set objfs1 = nothingConst ForReading = 1
Const ForWriting = 2Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForReading)
strFile = objFile.ReadAll
objFile.CloseintLength = Len(strFile)
strEnd = Right(strFile, 2)strFile = Right(strFile, intLength - 90)
Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForWriting)
objFile.Write strFile
objFile.Close
Const ForReading = 1
Const ForWriting = 2Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForReading)
strFile = objFile.ReadAll
objFile.CloseintLength = Len(strFile)
strEnd = Right(strFile, 2)strFile = Left(strFile, intLength - 7)
Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForWriting)
objFile.Write strFile
objFile.Close
Alright, if we're going to do this, we're going to do this right. What, exactly, are you looking for in the initial file? The "Elapsed Time" of the most recent "ENDED OF CPPIG000_Lamda_Batch(Morning)?"
What cell, exactly, are you looking to fill? Which workbook? Which sheet? Is the address static? Dynamic? A certain range? Based on what?
