2-WAY ERROR (Excel VBScript)

2-WAY ERROR (Excel VBScript) SearchSearch

SMS & MMS Technical Forum » NowSMS Support - SMS Issues (Product Support Only) » Archive through April 13, 2018 » 2-WAY ERROR (Excel VBScript) « Previous || Next »
Author Message
Chala Tumelo
New member
Username: Tumelo

Post Number: 1
Registered: 01-2013
Posted on Saturday, June 27, 2015 - 08:10 pm:   

Hi, I have trouble ... the gateway is not succesffully reading data from Windows Scripting Host (instead replying "command not completed. system error executing associated command" to the sender's phone). Since I am quite new to Windows Scripting ... please help me troubleshoot the following setup:

1. my incoming SMS has prefix "hello" which must
2: execute the script "c:\Docs\calendar.vbs"

From this point, "calendar.vbs" is able to retireve calendar items from an Excel file and successfully echos them to the Windows Scripting Host message box. IT IS AT THIS POINT THAT NOWSMS IS NOT READING THE ECHO for outbound transmission to the sender.

I am thinking the problem is with the "command to execute" sytnax I am using ... "c:\windows\system32\cscript.exe /Nologo C:\Docs\calendar.vbs". What should I change to make this work.

Please note, when I run "calendar.vbs" on its own (outside NowSMS) it successfuly retrieves the calendar data and displays it on the Windows Scripting Host Message Box. PLEASE HELP.
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 5340
Registered: 08-2008
Posted on Sunday, June 28, 2015 - 01:10 am:   

Hi,

That looks right ... just change /Nologo to //Nologo

I assume that if you open a command prompt window and type the following command, it writes the expected text to the screen...

c:\windows\system32\cscript.exe //Nologo C:\Docs\calendar.vbs

The error "command not completed. system error executing associated command" basically means c:\windows\system32\cscript.exe could not be found, so maybe the path is wrong or there is a typo?

Enable the SMSDEBUG.LOG, and check to see that the command NowSMS is trying to run is correct in the log.

For example, I created a simple f:\temp\echo.vbs script that just echoes back the same text....

 
on error resume next

Set Args = WScript.Arguments
For I = 0 to Args.Count - 1
if (I > 0) Then
LogTxt = LogTxt & " "
End if
LogTxt = LogTxt & Args(I)
Next
Set Args = nothing

Wscript.Echo "Echo " & LogTxt



I defined a 2-way command:

c:\windows\system32\cscript.exe //Nologo f:\temp\echo.vbs @@FULLSMS@@

This is what is shown in SMSDEBUG.LOG when I send in a message:

21:43:19:515 [6] ThreadProcessInboundSMS: Processing 558F5137.in...
21:43:19:515 [6] GetProgramToExecute: Found 2-way command prefix match *
21:43:19:515 [6] GetProgramToExecute: Best match so far for 2-way command prefix *
21:43:19:516 [6] GetProgramToExecute: c:\windows\system32\cscript.exe //Nologo f:\temp\echo.vbs @@FULLSMS@@
21:43:19:516 [6] GetProgramToExecute: c:\windows\system32\cscript.exe //Nologo f:\temp\echo.vbs Test Message
21:43:19:516 [6] ThreadProcessInboundSMS: Executing c:\windows\system32\cscript.exe //Nologo f:\temp\echo.vbs Test Message


What do you see in SMSDEBUG.LOG? Hopefully it is an issue of the Windows directory being in a different location, or a simple typo.

--
Des
NowSMS Support
Chala Tumelo
New member
Username: Tumelo

Post Number: 2
Registered: 01-2013
Posted on Sunday, June 28, 2015 - 03:08 pm:   

Hi Des. Thanks for your response. I have acted on the suggestions you made and the attached is what the debug log shows. I have also added the actual vbs script at the end for you see what is happening - in case there is something wrong in the script itself.


application/vnd.openxmlformats-officedocument.wordprocessingml.documentsmsdebug.log details
DebugLog.docx (157.5 k)
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 5342
Registered: 08-2008
Posted on Sunday, June 28, 2015 - 08:50 pm:   

Hi,

"Command response is blank, no reply will be sent" is progress.

But if c:\windows\system32\cscript.exe //Nologo C:\Docs\calendar.vbs doesn't display a response, that is consistent with NowSMS seeing a blank response.

What if from a command prompt, you run c:\windows\system32\wscript.exe C:\Docs\calendar.vbs?

wscript.exe displays Echo responses as a Windows pop-up, while cscript.exe writes to the console.

If you can get the command to work and display results with cscript.exe, it will work with NowSMS.

One thing I notice from your doc...your working results come from CM1Calendar.vbs, and the non-working results from calendar.vbs

What happens with c:\windows\system32\cscript.exe //Nologo C:\Docs\CM1calendar.vbs?

--
Des
NowSMS Support
Chala Tumelo
New member
Username: Tumelo

Post Number: 3
Registered: 01-2013
Posted on Monday, June 29, 2015 - 12:13 am:   

Hi Des, thanks for being observant. Actually it is 'wscript' that echoes the response in the windows pop up. 'cscript' just starts with a black blank screen that soon disappears and does nothing else. I wonder what I would do next to get the response via 'cscript'.
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 5343
Registered: 08-2008
Posted on Monday, June 29, 2015 - 12:50 pm:   

Hi Chala,

When you do the test with cscript, are you doing it from a command prompt window?

If not, then you will not see the output, because Windows will open a command prompt window and then close it immediately when done.

wscript and cscript results should be the same, the only difference being wscript.exe displays Echo responses as a Windows pop-up, while cscript.exe writes to the console. To test with cscript, it is best to try it with an already open console (command prompt window).

If cscript writes the results to the console, this is a format that NowSMS can capture to use as response to a 2-way command.

--
Des
NowSMS Support
Bryce Norwood - NowSMS Support
Board Administrator
Username: Bryce

Post Number: 8118
Registered: 10-2002
Posted on Monday, July 27, 2015 - 04:41 pm:   

Hi Chala,

I was discussing recent support queries with our team, and I'm not sure that you ever got this problem resolved.

Des is very correct that you need to test and verify the VBScript from a command line window with cscript.exe. This should display the same results as the pop-up window when using wscript.exe.

However, once you get past that issue, you will likely encounter another issue.

In my case, testing with a VBScript that writes the received message to an Excel spreadsheet, I consistently encountered the following error, but only when the VBS was run as a 2-way command:


quote:

c:\temp\2way.vbs(20, 2) Microsoft Excel: Microsoft Excel cannot access the file 'c:\temp\test.xlsx'. There are several possible reasons:

 The file name or path does not exist.
 The file is being used by another program.
 The workbook you are trying to save has the same name as a currently open workbook.




This is because SMS sending and receiving in NowSMS runs in the context of a Windows service, not as a desktop application.

There is a work-around for this described here: https://social.msdn.microsoft.com/Forums/en-US/b81a3c4e-62db-488b-af06-44421818e f91/excel-2007-automation-on-top-of-a-windows-server-2008-x64?forum=innovateonof fice

The short solution is ...

For Windows x64 versions, create this folder:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows x86 versions, create this folder:

C:\Windows\System32\config\systemprofile\Desktop

I posted an example 2-way script that writes received SMS to an Excel spreadsheet here:

http://www.nowsms.com/download/2way-excel.vbs.txt

filename = "c:\temp\test.xlsx" 

'Parse command line arguments

CmdLine = ""

Set Args = WScript.Arguments
For I = 0 to Args.Count - 1
if (I > 0) Then
CmdLine = CmdLine & " "
End if
CmdLine = CmdLine & Args(I)
Next
Set Args = nothing

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

xlApp.DisplayAlerts = False

'write data into the spreadsheet
xlSht.Cells(xlSht.UsedRange.Rows.Count+1, 1) = CmdLine

xlBook.Save
xlBook.Close SaveChanges=True
xlApp.Quit


This script just parses the command line, expecting received SMS messages, and appends them to an Excel spreadsheet.

The spreadsheet must already exist before the script is run the first time.

I added the following 2-way command to NowSMS:

c:\windows\system32\cscript.exe c:\temp\2way-excel.vbs //Nologo @@SENDER@@ @@FULLSMS@@

-bn

Bryce Norwood
Now SMS/MMS Support
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 5382
Registered: 08-2008
Posted on Tuesday, July 28, 2015 - 12:36 pm:   

Follow-up...

I never saw the error that Bryce mentions.

The reason is this line:

on error resume next  


That statement suppresses error messages. It is probably not a good idea to use that statement when testing.

--
Des
NowSMS Support
Chala Tumelo
New member
Username: Tumelo

Post Number: 9
Registered: 01-2013
Posted on Saturday, June 11, 2016 - 05:35 am:   

Hi Bryce and Des

I need a bit more on the above. In the following code "CmdLine & Args(I)" is the whole SMS... I suppose ...

Set Args = WScript.Arguments
For I = 0 to Args.Count - 1
if (I > 0) Then
CmdLine = CmdLine & " "
End if
CmdLine = CmdLine & Args(I)
Next
Set Args = nothing

NOW... using vbScript I would like to extract the phone number (possibly by using "dim phonenumber" and then .... phonenumber = Split(CmdLine)(0)") and use it to verify if the sender is registered to obtain the information they want from the 2-way.

For some reason I can’t excise the phone number out of the CmdLine. How can I do that? My goal is to:
1: split the phone number out of the incoming SMS:
2: open an excel workbook "phone adrs.xlsx":
3: check column 3 (“C:C”) which is the list of registered numbers:
4: if the number exists in the list
the sender gets the reply
Else:
The sender get the message “your phone is not listed in this list”
End if.
The trouble I have had is to get the phone number out of the incoming SMS and use it to check. What should I do? I will also appreciate a means of removing the first four characters “+***” out of the incoming phone number.
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 5680
Registered: 08-2008
Posted on Monday, June 20, 2016 - 07:16 pm:   

Hi Chala,

Change he 2-way command to put the sender as the first parameter:

c:\windows\system32\cscript.exe //Nologo f:\temp\echo.vbs @@SENDER@@ @@FULLSMS@@

Args(0) will be the sender

Then change 0 to 1 where you parse the remaining Args as the text:

Set Args = WScript.Arguments
For I = 1 to Args.Count - 1
if (I > 1) Then
CmdLine = CmdLine & " "
End if
CmdLine = CmdLine & Args(I)
Next
Set Args = nothing


--
Des
NowSMS Support

Login Login / Register Logout Logout Search Last 30 Days Topics Topics