Vbscript Problem

[KReidy]

Hello,

 

I hope someone can help me, I am new to Voiceguide and VB scripting I am trying to run a script that does the following

 

 

A user Inputs a 5 digit or more code, program looks up an excel spreadsheet, if Input is 5

digits it looks up column 1, if more than 5 looks up colum 2. If empty cell encountered it's a fail, if number found success.

 

Later I want to ammend program so if number found a value stored in column 3 will be returned to program.

 

I have attached the program for you to look at.

 

 

Thanks in advance

 

Dim xlApp, xlBook, xlSht 
Dim filename, value1, value2

filename = "C:\Test.xls"

Set xlApp = CreateObject("Excel.Application")
Set vg = CreateObject("vgservices.commandlink")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.ActiveSheet

Set vg = CreateObject("vgservices.commandlink")
vg.Admin_TraceLogAdd 0, 0, "This Trace was printed from an external VB Script"
Set vg=Nothing

strValueEnteredByCaller = $RVSecurity Code
if len(strValueEnteredByCaller) = 5 then
iColumn = 1
else
iColumn = 2
end if

'scan through all entries until match found or a blank cell is reached
i = 1
while xlSht.Cells(i, iColumn) <> strValueEnteredByCaller and xlSht.Cells(i, iColumn) <> ""
i = i + 1
wend

if xlSht.Cells(i, iColumn) = "" then
Set vg = CreateObject("vgservices.commandlink")
vg.Run_ResultReturn $RV_LINEID, "fail"
Set vg=Nothing

else
Set vg = CreateObject("vgservices.commandlink")
vg.Run_ResultReturn $RV_LINEID, "success"
Set vg=Nothing


end if
xlBook.Close False
xlApp.Quit

'always deallocate after use...
set xlSht = Nothing 
Set xlBook = Nothing
Set xlApp = Nothing
Set vg=Nothing

 

 

[SupportTeam]

What is the actual problem with the script?

 

It's best to add more vg.Admin_TraceLogAdd calls in the script. This way you will be able to trace how the script is executing and narrow down any problem areas. The Admin_TraceLogAdd function places log entries in the vgEngine log file.

 

[KReidy]

What is the actual problem with the script?

 

It's best to add more vg.Admin_TraceLogAdd calls in the script. This way you will be able to trace how the script is executing and narrow down any problem areas. The Admin_TraceLogAdd function places log entries in the vgEngine log file.

 

 

vgEngine.txt

[KReidy]

What is the actual problem with the script?

 

It's best to add more vg.Admin_TraceLogAdd calls in the script. This way you will be able to trace how the script is executing and narrow down any problem areas. The Admin_TraceLogAdd function places log entries in the vgEngine log file.

 

 

When I run the script entering a correct code it takes the fail path, When I look at the Line Status Monitor when my VGscriptit enters the VBScript module - iRunwait =1 is displayed for 40 seconds (Excel file only contains 2 cells in the column I'm checking) then it takes the fail path.

 

I have attached the VG engine file for you to look at.

vgEngine.txt

[SupportTeam]

Not sure what is happening as you did not place sufficient tracing in the VB Script to let us see what is going on.

 

Also you have are seeing three CreateObject("vgServices.CommandLink") entries in script. You should only activate the "vgServices.CommandLink" object once per script. no need ot do this multiple times.

 

Try modifying the script as per below. It will better show what is happening in the script, and where the delays are.

 

 

 

Dim xlApp, xlBook, xlSht

Dim filename, value1, value2

filename = "C:\Test.xls"

 

Set vg = CreateObject("vgServices.CommandLink")

 

vg.Admin_TraceLogAdd 0, 0, "**** calling CreateObject Excel.Application"

Set xlApp = CreateObject("Excel.Application")

 

vg.Admin_TraceLogAdd 0, 0, "**** calling WorkBooks.Open"

set xlBook = xlApp.WorkBooks.Open(filename)

 

vg.Admin_TraceLogAdd 0, 0, "**** calling xlApp.ActiveSheet"

set xlSht = xlApp.ActiveSheet

 

vg.Admin_TraceLogAdd 0, 0, "**** calling len(strValueEnteredByCaller)"

 

strValueEnteredByCaller = "$RV[security Code]"

if len(strValueEnteredByCaller) = 5 then

iColumn = 1

else

iColumn = 2

end if

 

vg.Admin_TraceLogAdd 0, 0, "**** scaning through all entries until match found or a blank cell is reached "

 

'scan through all entries until match found or a blank cell is reached

i = 1

while xlSht.Cells(i, iColumn) <> strValueEnteredByCaller and xlSht.Cells(i, iColumn) <> ""

i = i + 1

wend

 

if xlSht.Cells(i, iColumn) = "" then

vg.Run_ResultReturn $RV_LINEID, "fail"

else

vg.Run_ResultReturn $RV_LINEID, "success"

end if

 

vg.Admin_TraceLogAdd 0, 0, "**** calling xlBook.Close "

xlBook.Close False

 

vg.Admin_TraceLogAdd 0, 0, "**** calling xlApp.Quit"

xlApp.Quit

 

vg.Admin_TraceLogAdd 0, 0, "**** deallocating"

 

'always deallocate after use...

set xlSht = Nothing

Set xlBook = Nothing

Set xlApp = Nothing

Set vg=Nothing

[KReidy]

I have amended the program and rerun the script to generate a more informative VGEngine file which I have attached.

vgEngine1.txt

[SupportTeam]

Your trace shows:

 

020730.546 9 1 RunVBScriptFile [wscript][C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs][] timeout=60 (vbsPath=C:\Program Files\VoiceGuide\temp), start (new Process)

 

020734.093 9 1 RunVBScriptFile just started. Handle=33704, Id=3300, StartTime=19/06/2008 02:07:33, HasExited=False

020734.203 9 1 VBScript started [C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs], ProcessHandle=[33704], VbsOrExeProcess.WorkingSet=[65536]

 

020748.734 16 rem vgRemotingCommandLink constructor start, parmameterless

 

020750.656 18 0 comlg **** calling CreateObject Excel.Application

 

020755.250 18 0 comlg **** calling WorkBooks.Open

 

 

and the trace "**** calling len(strValueEnteredByCaller" never appears..

 

Top start off the scripts are running very slow on your system. You should make sure that your system is fast enough and that nothing else is using the CPU or Memory too much.

 

Net you should test VBScripts outside VoiceGuide first to confirm that they execute fine, and the once you have them working then you can try running them in VoiceGuide again.

[KReidy]

Your trace shows:

 

020730.546 9 1 RunVBScriptFile [wscript][C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs][] timeout=60 (vbsPath=C:\Program Files\VoiceGuide\temp), start (new Process)

 

020734.093 9 1 RunVBScriptFile just started. Handle=33704, Id=3300, StartTime=19/06/2008 02:07:33, HasExited=False

020734.203 9 1 VBScript started [C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs], ProcessHandle=[33704], VbsOrExeProcess.WorkingSet=[65536]

 

020748.734 16 rem vgRemotingCommandLink constructor start, parmameterless

 

020750.656 18 0 comlg **** calling CreateObject Excel.Application

 

020755.250 18 0 comlg **** calling WorkBooks.Open

 

 

and the trace "**** calling len(strValueEnteredByCaller" never appears..

 

Top start off the scripts are running very slow on your system. You should make sure that your system is fast enough and that nothing else is using the CPU or Memory too much.

 

Net you should test VBScripts outside VoiceGuide first to confirm that they execute fine, and the once you have them working then you can try running them in VoiceGuide again.

 

 

 

I have done as you suggested and I have tested the program outside VoiceGuide (see attached VoiceGuideExample2.txt) and the program returns a Success and Fail.

I have also rerun the script, the VgEngine log now displays the trace "**** calling len(strValueEnteredByCaller"

vgEngine2.txt

VoiceGuideExample2.txt

[SupportTeam]

Trace now shows that the VBScript runs and completes, although it still takes a long time to start and perform the CreateObject(Excel.Application)

Perhaps you should look at using a database instead, it would probably be faster to perform a search in a database.

 

 

111419.078 9 1 VBScript started [C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs], ProcessHandle=[33624], VbsOrExeProcess.WorkingSet=[65536]

...

111432.671 18 0 comlg **** calling CreateObject Excel.Application

...

111447.562 18 0 comlg **** calling WorkBooks.Open

...

111450.765 18 0 comlg **** calling xlApp.ActiveSheet

111450.781 18 0 comlg **** calling len(strValueEnteredByCaller)

111450.796 18 0 comlg **** scaning through all entries until match found or a blank cell is reached

111450.890 18 1 cl Run_ResultReturn [fail]

 

[KReidy]

Trace now shows that the VBScript runs and completes, although it still takes a long time to start and perform the CreateObject(Excel.Application)

Perhaps you should look at using a database instead, it would probably be faster to perform a search in a database.

 

 

111419.078 9 1 VBScript started [C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs], ProcessHandle=[33624], VbsOrExeProcess.WorkingSet=[65536]

...

111432.671 18 0 comlg **** calling CreateObject Excel.Application

...

111447.562 18 0 comlg **** calling WorkBooks.Open

...

111450.765 18 0 comlg **** calling xlApp.ActiveSheet

111450.781 18 0 comlg **** calling len(strValueEnteredByCaller)

111450.796 18 0 comlg **** scaning through all entries until match found or a blank cell is reached

111450.890 18 1 cl Run_ResultReturn [fail]

 

 

Thank you for your efforts so far, could you explain why the VBScript is taking the fail path and not the success path when I am inputting a valid number that is stored in the excel spreadsheet?

 

The excel spreadsheet I'm testing has only 4 cells with information in it (2 in column 1 and 2 in column 2,), the system I am using is Windows XP (SP2), Processor 1.2Ghz, Memory 128mb. Is this Spec a bare minimum?

[KReidy]

Trace now shows that the VBScript runs and completes, although it still takes a long time to start and perform the CreateObject(Excel.Application)

Perhaps you should look at using a database instead, it would probably be faster to perform a search in a database.

 

 

111419.078 9 1 VBScript started [C:\Program Files\VoiceGuide\temp\vbs_1_1.vbs], ProcessHandle=[33624], VbsOrExeProcess.WorkingSet=[65536]

...

111432.671 18 0 comlg **** calling CreateObject Excel.Application

...

111447.562 18 0 comlg **** calling WorkBooks.Open

...

111450.765 18 0 comlg **** calling xlApp.ActiveSheet

111450.781 18 0 comlg **** calling len(strValueEnteredByCaller)

111450.796 18 0 comlg **** scaning through all entries until match found or a blank cell is reached

111450.890 18 1 cl Run_ResultReturn [fail]

 

 

Thank you for your efforts so far, could you explain why the VBScript is taking the fail path and not the success path when I am inputting a valid number that is stored in the excel spreadsheet?

 

The excel spreadsheet I'm testing has only 4 cells with information in it (2 in column 1 and 2 in column 2,), the system I am using is Windows XP (SP2), Processor 1.2Ghz, Memory 128mb. Is this Spec a bare minimum?

 

When I replace the variable $RVSecurity Code on the "StrEnteredByCaller" line of my code with for example 12345 (which I know is in a cell in the excel spreadsheet) the VBScript follows the success path, could you have a look at the syntax around $RVSecurity Code?

 

 

 

[SupportTeam]

$RVSecurity Code is an invalid expression. You need to use:

 

strValueEnteredByCaller = "$RV[security Code]"

 

I think we fixed that line for you before in the example we provided. Please see post #5 in this thread.

 

[KReidy]

$RVSecurity Code is an invalid expression. You need to use:

 

strValueEnteredByCaller = "$RV[security Code]"

 

I think we fixed that line for you before in the example we provided. Please see post #5 in this thread.

 

 

I have tried that already with no joy, any ideas?

[KReidy]

$RVSecurity Code is an invalid expression. You need to use:

 

strValueEnteredByCaller = "$RV[security Code]"

 

I think we fixed that line for you before in the example we provided. Please see post #5 in this thread.

 

 

I have tried that already with no joy, any ideas?

 

 

I have attached a copy of the VBScript I'm tryung to run, a log file showing success path (with hardwired result) and a fail path using "RV[secirity Code]" and the VoiceGuide script I'm trying to run.

 

Thanks

vbscript2006.txt

0620vgEngine.txt

TASPIN_SCRIPT.vgs

[SupportTeam]

if using

 

strValueEnteredByCaller = 12345

 

results in correct operation then maybe try:

 

strValueEnteredByCaller = $RV[security Code]

 

(ie: without the quoted around the $RV[security Code])

 

Adding tracing to the script should let you debug your script and trace though it in detail to narrow down why the script is executing the way it is.

[KReidy]

if using

 

strValueEnteredByCaller = 12345

 

results in correct operation then maybe try:

 

strValueEnteredByCaller = $RV[security Code]

 

(ie: without the quoted around the $RV[security Code])

 

Adding tracing to the script should let you debug your script and trace though it in detail to narrow down why the script is executing the way it is.

 

 

Excellent, I tried without quotations and it worked, thank you very much for your help