Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Command line to run VB script from Excel

Status
Not open for further replies.
alibongo

alibongo

IS-IT--Management
Sep 6, 2001
29
GB
Hello,

I posted this question I while ago and got no replies so I thought I would try again.

Does anybody know how to write a command line or create a macro to run VB script from within excel. (A button which runs a script).

The script uses regular expressions and as such I dont think you can use VBA to do it.

I have a button in the web browser window which runs the VB script source. But, how do you do run it from within Excel, I am guessing you must use Microsoft script editor to do this, correct?

Thanks in advance for your help.
 
Sort by date Sort by votes
You can use the ShellExecute call to fire up the script. Do a search for ShellExecute in this forum, the VBA forum or the VB5&6 forum for a plethora of examples.
 
Upvote 0 Downvote
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=&quot;VBScript&quot;>

<!--

'Global constants

Const ForReading = 1
Const ForWriting = 2
Const cFileLocation = &quot;C:\Mediation Files\&quot;


'**********************************************
'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 = &quot;0&quot; 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(&quot;Scripting.FileSystemObject&quot;)


If (FSO.FileExists(cFileLocation & strFileName & &quot;.txt&quot;)) Then

Set TextStream = FSO.OpenTextFile(cFileLocation & strFileName & &quot;.txt&quot;,ForReading, FALSE)


strFileData = TextStream.ReadAll

'Create the output file
Set OutputFile = FSO.CreateTextFile(cFileLocation & strFileName & &quot;.csv&quot;,TRUE)
OutputFile.WriteLine(&quot;'This file is created by Parserv1.htm&quot;)

'Write out the headings
OutputFile.WriteLine(&quot;No of CDRs,Date,Seq Number,Switch ID&quot;)


strDataRecs = getinfo(strFileData, &quot;\d+\s\d{8}\.\d{5}\.\d{3}\.IACAMA13\.\w+\.id3&quot;, &quot;:&quot;)

iRecCount = getStrItem(strDataRecs,1,&quot;:&quot;)

'Msgbox &quot;The number of records is &quot; & iRecCount

For i = 2 to iRecCount + 1
'Msgbox &quot;strDataRecs = &quot; & strDataRecs
strLine = getStrItem(strDataRecs,i,&quot;:&quot;)

'Msgbox &quot;Record number &quot; & i - 1 & &quot;is &quot; & strLine


'Get the number of CDRs
strOutputLine = getStrItem(getinfo(strLine,&quot;\d+\s&quot;,&quot;:&quot;),2,&quot;:&quot;)

'Msgbox &quot;The number of CDRs is &quot; & strOutputLine

'Add the date string
strOutputLine = strOutputLine & &quot;,&quot; & getStrItem(getinfo(strLine,&quot;\d{8}&quot;,&quot;:&quot;),2,&quot;:&quot;)

'Add the Sequence number
strOutputLine = strOutputLine & &quot;,&quot; & Mid(getStrItem(getinfo(strLine,&quot;\.\d{5}\.&quot;,&quot;:&quot;),2,&quot;:&quot;),2,5)

'Add the Switch ID
strSwitchID = getStrItem(getinfo(strLine,&quot;IACAMA13\.\w+&quot;,&quot;:&quot;),2,&quot;:&quot;)
strOutputLine = strOutputLine& &quot;,&quot; & Mid(strSwitchID,10, Len(strSwitchID))

'Write out the data
Outputfile.WriteLine(strOutputLine)

Next


TextStream.Close

OutputFile.Close

Msgbox iRecCount & &quot; records extracted&quot;

Else

Msgbox &quot;File &quot; & cFileLocation & strFileName & &quot;.txt not found, please run again&quot;

End If

End Sub 'ExtractMedationData
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

shytott
  • Locked
  • Question
Excel - Toggle Button Code 1
Replies
10
Views
863
shytott
shytott
duytoi
  • Locked
  • Question
How to add a head to the search results in Excel 2016 using VBA
Replies
2
Views
1K
Andrzejek
Andrzejek
Bijugate
  • Locked
  • Question
Excel update question
Replies
0
Views
291
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
287
combo
combo
renigar
  • Question
Excel Power Query refresh error
Replies
3
Views
628
SoftwareRT
SoftwareRT

Part and Inventory Search

Sponsor

Back
Top