VB script to copy last time value to spreadsheed

December 6, 2012 at 08:12:52
Specs: Windows XP, e8400
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:11

ENDED 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


See More: VB script to copy last time value to spreadsheed

Report •


#1
December 6, 2012 at 08:34:54
Well, let's see what you have so far, and we'll go from there.

How To Ask Questions The Smart Way


Report •

#2
December 6, 2012 at 12:09:57
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.


Report •

#3
December 7, 2012 at 06:40:18
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.Close

Set 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
Loop

objFile2.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

Loop

set objTextFile = nothing
set objTextFile2 = nothing

objFS.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 = nothing

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForReading)
strFile = objFile.ReadAll
objFile.Close

intLength = 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 = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\g9007l2\Desktop\scripts testing\Differences.txt", ForReading)
strFile = objFile.ReadAll
objFile.Close

intLength = 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


Report •

Related Solutions

#4
December 7, 2012 at 08:47:33
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?

How To Ask Questions The Smart Way


Report •


Ask Question