Automate Excel

I’ve been busy over here lately giving myself a crash course in PHP/MYSQL by writing my own blog engine. It’s coming along slowly but surely, yet very far from done. I used the stylesheet from this site, however each line of PHP code is new and written by me.

Here’s some Excel links I’ve been meaning to bookmark, in case you missed them:

Andrew’s Cell Spotter

Colo’s New Chapters on Classes in VBA

VB online book from DDOE

Excel Dashboard Update

Formula Trace Tool

How Excel 2003 Infers XSDs When Importing XML Data

“We hope to pod cast every few weeks and interview industry leaders around the Microsoft Office System.”

Go there: Office Zealot Podcast

I created a Spreadsheet that sends data from Excel to a website, is processed by PHP, then is written to a MYSQL database. In turn the results are viewable via a webpage, Look Here.

To post a message there also, download the VBA-PHP-MYSQL Spreadsheet.

How it works
1. A user enters their Name and a short Message in Excel. When they click the “Send Comment” button, VBA sends the two variables via HTTP to a PHP file sitting on this server.

View the PHP file (I removed the database connection info and changed the extension to text)

2. The PHP file reads the variables sent from VBA and writes them to a MYSQL database, along with the time they were received.

3. Finally I created another PHP file to read from the MYSQL database and display the results.

View the PHP file

Notes
The code still needs work, I’m not sure If I’ll make another update or leave it as is. I’ve provided the files as building blocks to save anyone that has an interest in this an hour or two.

I’ve enclosed both PHP files and the unprotected VBA, here’s a screenshot of the database table I used:

Parse Excel Formulas to tokenized RPN. Over my head but interesting.

The post includes some further reading if you scroll down, like; What is RPN? List of Excel Operators.

One other bookmark from the ewbi site:
Normalize Excel’s SpreadsheetML using XSLT

MS has a new KB article with some basic code examples for filling an array with spreadsheet data, and writing data to a spreadsheet from an array:

Sample Visual Basic macros for working with arrays in Excel

If you always know the length of a string, it’s easy to remove characters from it. Example: If you have a string that is 10 characters and you want to remove 1 character from the Left side, simply return the right 9 characters:

msgbox Right(Mystring, 9)

This doesn’t work for a variable length string, or one which you don’t know beforehand it’s length. In this case you can use the formula (Length – N) to designate how many characters to extract:

MsgBox Right(Mystring, Len(Mystring) – 1)

Where 1 is the number of characters to remove from the left side of the string. This will return the string minus the left most character.

To remove characters from the right side of a string, replace Right with Left

I’m really enjoying the WinHTTP and Web Services (specifically REST) stuff. Even though I need to research quite a bit for each small thing I learn, and I’m just getting started, it’s well worth it.

Last night I figured out how to round trip a variable from VBA to PHP and back. In other words, I learned how to pass a VBA variable to a PHP function (residing in a file on the AutomateExcel server), manipulate the variable, then return the new result back to VBA.

Note: The variable is sent as a string.

For example, the message box in this picture was arrived at by:
1. Sending a variable holding the number 15 over HTTP to a PHP file on AutomateExcel
2. A PHP function takes the variable, Multiplies it by 500, then returns the answer, in this case 7500
3. VBA reads the response and returns the answer in a msgbox

Here’s what the PHP code looks like. Open notepad, copy and paste this info, save the file with the extension php, and upload to your server:

<?php 

$GetNumber=$_GET["PassThis"];
$NewNumber=$GetNumber*500; 

echo $GetNumber. " x 500 = " .$NewNumber; 

?>

I didn’t return the answer in XML to keep the example simple. Here’s the VBA code to send and receive the data, change the connection string to point to the php file you just created:

' Add a reference to Microsoft WinHTTP Services
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0

Private Sub GetItems()
Dim MyCon As New WinHttpRequest
Dim sendthis As Double
Dim myanswer As String

    'variable to send
    sendthis = 15

    'Connection string to send
    MyCon.Open "GET", _
    "http://www.automateexcel.com/pl/server500.php" & _
    "?PassThis=" & sendthis

    'send it
    MyCon.Send

    'return it
    myanswer = MyCon.ResponseText
    MsgBox myanswer

End Sub

Note: Add a reference to Microsoft WinHTTP Services

That’s awesome!

The long way to select all Worksheet Tabs:

1. Select the First Sheet in the Workbook
2. Hold down Shift Key
3. Select the last worksheet in the Workbook

The quicker way to select all Worksheet Tabs:

1. Right click any tab and choose:
Select All Sheets

To unselect all sheets:

1. Left click any individual tab or
2. Right click any tab and choose:
Ungroup Sheets

Another Excel solution from Sam Radakovitz, this one for working with Tivo data: Tivo 2 Excel

“Watch, Report, Chart, and Pivot your Tivo information in Excel!”

I’m not a Tivo user, so I’ll have to sit this one out.

Sam Radakovitz , the guy who created the Halo Spreadsheet(recently updated) has made an extremely nice Calendar Picker Addin.

Load it up like a regular addin, then any column with a date as the column header presents the user with a popup to choose the next date, instead of having to type it manually.

Many nice features, too bad it’s PW protected (share the love Sam!), Transparent effects, fade in, fade out, a cool Smart Tag like icon that is embedded in the cells to activate the Calendar.

One of the nicer Calendar addins available, worth the download to use, or get some ideas.