Writing the GUI for an application can be 90 percent of the battle. Here's a lazy way to make the user interface: maintain the data in Microsoft Excel, but write a macro that uploads the data to a server for processing.
Once, I created an entire application this way. We had a web site that listed various special events. I was tired of updating the web page manually, but I knew the secretary wasn't technical enough to maintain the web site herself.
I started planning out a user interface that would let anyone do updates. It was grand—a big MySQL database with a PHP frontend that would let people log in, do updates, add new events, and so on. The system would then generate the web pages listing the events automatically. It was wonderful on paper, and I'm sure if I'd had 100 years to write the code, it would have been great.
Instead, I realized that only one person would actually be doing updates. Therefore, I gave her access to a spreadsheet that captured all the information that needed to be collected and to a macro that would save the file twice: once on the server as a tab-separated file and again as an XLS file. A process on the server would parse the tab-separated file and generate the web page automatically.
You can see the spreadsheet in Figure 13-2.
Making the button takes a few steps.
First, use the macro recorder to do what you want:
Record the macro: Tools > Macro > Record New Macro.
Name the macro 'Save.'
Perform the actions to save the file as a tab-separated file on the network file server.
Save the file as an MS Excel Workbook (
It is important that the last place you save the file is the richest format (Workbook) because this choice sets the default save format. If someone saves the file using File > Save, you want it to default to this format.
Click Stop on the macro record toolbar.
Next, create a button and attach the macro to it:
View the Forms toolbar: View > Toolbars > Forms.
Click on the Button (looks like a plain rectangle).
Draw a button where you want it to appear in the spreadsheet.
When asked, select the macro created earlier.
If you need to edit the button later, Ctrl-click it.
Now, test this by clicking the button. Voila! It works! Check the dates on the files to make sure that the file really got saved twice. (Yes, it may ask you twice whether it's OK to replace the file. Click Yes.)
If you want to clean up the macro a bit, that's easy, too. In fact, one of the first things I did was edit exactly where the file gets saved:
Go to the macro editor: Tools > Macro > Macros.
Select the macro we just created and click Edit.
Save and exit when you are done.
Tip
In Microsoft macros, the line-continuation symbol is the underbar (_).
The final macro looks like this: Sub Save() ' ' Macro recorded 5/22/2005 by Thomas Limoncelli ' ActiveWorkbook.SaveAs Filename:= _ 'Y:calendarEventList.txt', FileFormat:= _ xlText, CreateBackup:=False ActiveWorkbook.SaveAs Filename:= _ 'Y:calendarEventList.xls', FileFormat:= _ xlNormal, Password:='', WriteResPassword:='', _ ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub
Now that I have the tab-separated version being stored on a file
