2-way SMS access to Microsoft Office, Excel and VBScript

Topic Keywords: 2-way SMS, Excel, VBScript

NowSMS 2-way applications are usually built on web technologies, using PHP or ASP.Net scripting.  But we do get a fair number of support questions from customers who want to use VBScript.

The advantage of VBScript is that there are numerous examples available for interfacing with Microsoft Office, especially Excel, and no extra programming tools or environments are required.

As a starting point for VBScript, we often refer to this example shared by one of our customers 13 years ago:  http://support.nowsms.com/discus/messages/1/333.html

That example VBScript accepts received messages and writes them to a text file log.

If you want to return an SMS reply when processing a received SMS, from within your VBScript, use WScript.Echo to output your SMS reply.  When processing a 2-way command, anything that the script writes to the display console is instead sent as an SMS reply.

For example, here is a simple script that echoes back the same text back to the message sender:

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

The above script loops through all of its command line parameters and stores them as a combined string of text, then uses Wscript.Echo to deliver the reply. Assuming the script is named echo.vs and saved in the folder c:\temp, the following 2-way command can be defined in NowSMS:

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

 

Use * as the Keyword to route all received messages to this 2-way command.

We recommend testing the script from a command prompt window before attempting to deploy as a 2-way command.

From a command prompt window, type that same command, substituting a simulated text message for @@FULLSMS@@.  For example:

c:\windows\system32\cscript.exe //Nologo c:\temp\echo.vbs Test Message

 

The above should display:

Echo Test Message

 

Interfacing with Microsoft Office applications  such as Excel is slightly more difficult.

Below is an example 2-way script that writes received SMS to an existing Excel spreadsheet:

https://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 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.

To test it, add the following 2-way command to NowSMS:

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

Unfortunately, you may encounter errors trying to interface with Microsoft Office applications from VBScript using a NowSMS 2-way command.  This is because SMS sending and receiving in NowSMS runs in the context of a Windows service, not as a desktop application.

In my case, testing with the above VBScript, I consistently encountered the following error, but only when the VBScript was run by NowSMS as a 2-way command:

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.

 

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

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

 

Once the missing folder is created, the script should be able to access the Office application.

As a further example, it is also possible to extract a range from the spreadsheet to return as an SMS reply.  As an example, the following would return the content of cell A1 as a reply.  Be sure to perform this command before closing the workbook.

Wscript.Echo xlSht.Range("A1").Value

 

Additional detail and discussion can be found at our support forum:  http://support.nowsms.com/discus/messages/1/73332.html