Sorry I have looked at shell execute in these pages but have not found anything that fits my purpose, or more to the point nothing i can figure out how to change.
Instead here is the script I am using to parse my file, it runs from the web browser. How do I do this in excel?
<HTML>
<HEAD>
<TITLE>Mediation extract</TITLE>
<SCRIPT LANGUAGE="VBScript">
<!--
'Global constants
Const ForReading = 1
Const ForWriting = 2
Const cFileLocation = "C:\Mediation Files\"
'**********************************************
'Subs to execute the main routines on a button press
'These should match with the buttons specified at the end of the page
'and the actual routines
Sub ButtonMediation_OnClick
ExtractMediationData
End Sub
Sub ButtonMIU_OnClick
ExtractMIUData
End Sub
'**********************************************
'Sub to extract data from the mediation report
Sub ExtractMediationData()
Dim FSO
Dim TextStream
Dim OutputFile
Dim strFileName
Dim StrLine
Dim strOutputLine
Dim strFileData
Dim strDataRecs
Dim iRecCount
Dim i
Dim strSwitchID
strFileName = FileNameInfo 'Get the file name
If strfileName = "0" Then Exit Sub '0 is returned if cancel is pressed
' Set up global data.This line sets up a variable to access the file system
Set FSO = CreateObject("Scripting.FileSystemObject"
If (FSO.FileExists(cFileLocation & strFileName & ".txt"
) Then
Set TextStream = FSO.OpenTextFile(cFileLocation & strFileName & ".txt",ForReading, FALSE)
strFileData = TextStream.ReadAll
'Create the output file
Set OutputFile = FSO.CreateTextFile(cFileLocation & strFileName & ".csv",TRUE)
OutputFile.WriteLine("'This file is created by Parserv1.htm"
'Write out the headings
OutputFile.WriteLine("No of CDRs,Date,Seq Number,Switch ID"
strDataRecs = getinfo(strFileData, "\d+\s\d{8}\.\d{5}\.\d{3}\.IACAMA13\.\w+\.id3", ":"
iRecCount = getStrItem(strDataRecs,1,":"
'Msgbox "The number of records is " & iRecCount
For i = 2 to iRecCount + 1
'Msgbox "strDataRecs = " & strDataRecs
strLine = getStrItem(strDataRecs,i,":"
'Msgbox "Record number " & i - 1 & "is " & strLine
'Get the number of CDRs
strOutputLine = getStrItem(getinfo(strLine,"\d+\s",":"
,2,":"
'Msgbox "The number of CDRs is " & strOutputLine
'Add the date string
strOutputLine = strOutputLine & "," & getStrItem(getinfo(strLine,"\d{8}",":"
,2,":"
'Add the Sequence number
strOutputLine = strOutputLine & "," & Mid(getStrItem(getinfo(strLine,"\.\d{5}\.",":"
,2,":"
,2,5)
'Add the Switch ID
strSwitchID = getStrItem(getinfo(strLine,"IACAMA13\.\w+",":"
,2,":"
strOutputLine = strOutputLine& "," & Mid(strSwitchID,10, Len(strSwitchID))
'Write out the data
Outputfile.WriteLine(strOutputLine)
Next
TextStream.Close
OutputFile.Close
Msgbox iRecCount & " records extracted"
Else
Msgbox "File " & cFileLocation & strFileName & ".txt not found, please run again"
End If
End Sub 'ExtractMedationData
