excel-messenger: A Terrible Experiment In VBA

It was a slow day at work and another intern suggested finding a way to send messages discreetly between our computers on the local network.

To accomplish this I chose any true programmer’s favorite tools: excel sheets and VBA.

Part 1: peer-to-peer

The first idea we had:

  • Create two excel workbooks on a local network drive

p2pwindow

  • Have a send message and an inbox cell in each workbook

p2p message

  • Make each person’s inbox cell reference the other persons send message cell

external ref

You’re done!

But….there’s one problem.

If I change my message I need to save my excel file, otherwise the references don’t update.

Here’s where things get real. We’re going to have to bust out some VBA to solve this.

First we make a subroutine that autosaves every 20 seconds. Saving was pretty slow on our computers.

Sub autosave()

    Dim update_period As Integer
    
    update_period = 20 ' seconds

    ActiveWorkbook.Save
    Application.OnTime Now + TimeValue("00:00:" & update_period), "autosave"

End Sub

Next you can throw in a sub that will automatically start our saving routine when the workbook opens.

Private Sub Auto_Open()
    Call autosave
End Sub

Done. We now have our first p2p messaging app.

Part 2: client-server

This is where the madness begins.

The first version of the app was…okay.

It worked but it wasn’t meeting the intense enterprise demands we were facing in the market. We needed something scalable, something more dynamic to meet customer needs. To fix this I pivoted our app to a client-server infrastructure.

Here’s how it went:

  • Instead of two workbooks referencing each other, we create client workbooks and one server workbook

cs-window

  • Clients can input a nickname and a message

cs-client

  • The server loops through all the clients in the folder, grabs nicknames and messages, and creates the master chat window by shifting everything up and ignoring duplicates.

cs-server

That’s basically it. Now we just make all the workbooks autosave every 20 seconds to keep everyone sync’d. Clients all just reference the server sheet.

Here’s what the final result looks like, as a bonus I skinned the client sheet to look like a regular worksheet.

cs-client-real

 

See source code and excel files on github.

 

 

 

Project: ocr-db

In this mini-project, I created a tool for searching contents of scanned PDFs. This was created while working an internship that involved a lot of searching for things in scanned technical documents. A common task was trying to determine which drawing contained certain tags that corresponded to parts of the design. This was my attempt to try and tame the problem using OCR (Optical Character Recognition) technology.

Check it out here.

Making Your Web Server Run Python Scripts

A project I worked on recently involved collecting temperature, pressure, and humidity data using a raspberry pi and displaying it on a simple web interface. I wrote a bunch of data processing functions in python and needed a way to tell the server to run the script on the back end and process the data before it could be loaded into the graph/tables present on the page.

Here are two methods I found that worked with Apache:

Method #1: run on page load

Install php if you haven’t already:

sudo apt-get install php5

Add this to the body of your html file using the path to your script.

<?
php shell_exec('python /var/www/html/script.py'); 
?>

To give the server permission to run the file I set the apache user www-data as an owner of the file.

sudo chown www-data script.py

Lastly to make it work I had to rename my index.html file to index.php.

Thats it.

Now when a user visits the page the server will run the script.

Method #2:

This method is more involved but gives you more flexibility. We can use something called CGI (Common Gateway Interface), which is a standard way to make servers run executables.

First activate the cgi module for apache:

sudo a2enmod cgi

Edit your apache config file /etc/apache2/apache2.conf and add this to it:

<Directory /var/www/html/python>
    Options +ExecCGI
    AddHandler cgi-script .py
</Directory>

This tells apache to look in the folder /var/www/html/python for files ending in .py and to treat them as executables. You can change the directory to the folder with your script.

Add this to the top of your python script so the server knows how to handle the file.

#!/usr/bin/env python
import cgi

print "Content-type: text/html"
print

Restart apache:

sudo service apache2 restart

Now to test it open your browser and type the url of your file,

e.g. 127.0.0.1/myscript.py

Remember to add apache as an owner of the script, as in method #1.

When the server gets a request for the file, it should now run it as an executable.

Passing Parameters

We might want to run the script with different parameters depending on what the user does (e.g. pressing different buttons).

First of all, rather than navigating to the file every time we want it to be run, we can use ajax to dynamically request the server to run the script.

Here’s a JavaScript function that requests the script be run and passes three named arguments as data:

function processData(room_name, sensor_number, short_or_long){

$.ajax({
	  type: "POST",
	  url: "/python/process_data.py",
	  data: { room: room_name, sensor: sensor_number, longshort: short_or_long }
}).done(function( o ) {
	   });

}

The python cgi module allows us to receive these arguments via a dictionary:

arguments = cgi.FieldStorage()

room_name = str(arguments["room"].value)
sensor_number = str(arguments["sensor"].value)
short_or_long_term = str(arguments["longshort"].value)

That’s it.

With this setup I was able to dynamically process different data sets and update graphs and tables depending on which buttons the user selected on the page.