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:
“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.
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:
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:
Tom | 
02 28th, 2005| 
' 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.
“provide information about a selected cell’s XML properties, create XSD files for XML maps, rename the Root and Row elements, or refresh all of the XML maps in a workbook at once.”
Download: Excel 2003 Add-in: XML Tools Add-in
(via Patrick Tisseghem’s Blog)
More WinHTTP/XML fun…
If you have Excel 2003 and use the social bookmarking tool Del.icio.us, you can backup your bookmarks in Excel.
1. Download the Del.icio.us Excel Spreadsheet
2. Press the “Backup Del.icio.us” Button
3. Enter your Del.icio.us Username and Password then click GO!
That’s it. Your spreadsheet should now look like the picture.
A few additional benefits:
• Save your data multiple ways; as Excel (.xls), Comma Delimited Text File (.csv) or XML.
• I like having the URL’s as clickable links, so when I do a quick browse if I see something interesting, I click, and I’m taken to the bookmark
• I can do data analysis on my bookmarking behavior if desired, possibly bookmark frequency chart, tag chart, filtering, etc. This requires additional modification.
Notes:
You need to click YES when opening the workbook and prompted to enable macros for things to work correctly. However, if you’re hesitant, the code is unprotected so hit NO, then open the VB editor(alt F11) and inspect the code first.
This workbook makes use of the del.icio.us API, specifically only one call: /api/posts/all.
Update: On May 16, 2006 there was announced a new URL for the del.ico.us API. The spreadsheet has been updated to reflect this. If you downloaded the spreadhsheet prior to May 16, 2006 and arrived here wondering why your old version doesn’t work anymore, simply download again and replace your old file.
You might also like these posts:
2007 Calender Templates
Excel Mortgage Calculator
The MS Webcast site now has RSS feeds.
And if you’re interested, the Bill Gates Keynote at the Office System Developer Conference 2005 is online. The VSTO demo is at around minute mark 24:30
This chapter is taken from “Excel 2002 VBA Programmer’s Reference”
Chapter 5: Using Ranges
I learned from the Amazon Web Services workbook that it’s extremely easy to access a REST Web Service using VBA and import the data to an XML list.
One missing piece after viewing the Amazon workbook was how to also send a login and password with a request if needed. Turns out to be very Simple. To send a login and password just add one additional command WinHttpRequest.SetCredentials (Example code below)
I’ve been trying to improve my XML knowledge lately(or lack thereof) by working on a learning project using the Bloglines Web Services, which allows HTTP requests, and returns XML (OPML, Unread Items, etc).
The learning curve I encountered was instead of simply pushing the XML through a map into cells, I wanted to use MSXML2 (Excel’s XML parser) to gain total control of the data to populate Treeviews, Listboxes, and other UserForm stuff.
The project goal is to to read my bloglines subscriptions in Excel(I’m 60% done), sorta like a Bloglines version of Colo’s EZRSS, then possibly port the effort to OutLook when I’m finished(another learning curve).
Here is example code to pull your Bloglines subscriptions into Excel, it sends your Username and Password for authentication:
'HttpRequest SetCredentials flags.
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
Private Sub ListSubs()
Dim MyRequest As New WinHttpRequest
MyRequest.Open "GET", _
"http://rpc.bloglines.com/listsubs"
'Set credentials
MyRequest.SetCredentials "USERNAME", "PASSWORD", _
HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
' Send Request.
MyRequest.Send
'And we get this response
MsgBox MyRequest.ResponseText
End Sub
Notes:
Set a reference to Microsoft WinHTTP Services
Replace USERNAME and PASSWORD with your Bloglines info
This returns the XML to a msgbox for demonstration, you can import it a map or load it to a MSXML2.DOMDocument(I’ve got working code, I’m still experimenting, I’ll follow up). Here is a snippet of the msgbox:
Mr. Excel has another book coming out called “Learn Excel from Mr. Excel”. If you’re willing to give up your email address the site claims you can preview it for free.
“Not loaded with party tricks that you will never use” <g>
I’m still looking for the update to the photo contest, did I miss it?
Smart tags in Excel are the little triangles in the corner of cells that sometimes appear, and when clicked lead you to a menu.
Their intent is to provide the user with added options based on the content in a cell. For example; if a user types a company name in a cell, the smart tag triangle could appear, and on the smart tag menu there might be options to see the Company’s stock price, Homepage, Contact info, etc.
I usually ignore most smart tags, however I always pay attention to the one that says “Inconsistent Formula”, in which case I always double check my work to verify I intended the inconsistency.
Developing Simple Smart Tags by Paul Cornell is an excellent article on the basics of rolling your own smart tag. After a 10 minute read I had a smart tag created, The Bored Smart Tag: When the word “Bored” is typed in a cell a smart tag displays a list of Websites to visit.
Try it Yourself
1. Right Click the file Bored.xml and Choose SAVE TARGET AS
2. Save the file to the directory:
C:\Program Files\Common Files\Microsoft Shared\Smart Tag\Lists
3. Open Excel and type the word Bored in a cell
Notes:
You can easily modify the download to add you own websites.
Smart tags require a later version of Excel to work.
Unfortunately the smart tag is stored in an external file which doesn’t make the solution easily portable.
Does anyone know of a simple “Hello World” tutorial for running a macro from a Smart tag menu item? Update: I found my answer, “The down side to MOSTLs are that the actions are limited to Hyperlinks.”
A couple links for sending email via VBA:
Different Ways to Take Advantage of the E-mail Features of Excel - Knowledge Base Article
DataPig Video Tutorial - Just a few lines of code.
Andrew on Excel and Email - Code to Email the Active Sheet and Active Book
