Automate Excel

“This article describes the resources and documentation available to you for automating Microsoft Office applications.”

How To Find and Use Office Object Model Documentation

Watch or listen to movies in Excel, while you work in Excel:

1. On the main menu click Insert->Object
2. Select Windows Media Player from the Object Type box and hit OK
3. Move and resize the WMP to your liking

Note: If you can’t resize the WMP object, switch to “Design Mode” by clicking the design mode button

Now we’ll add some code to tell the Windows Media Player object to Play and what file to play. Open the Visual Basic Editor(alt+F11), double click the worksheet that contains the WMP you just inserted and Paste the following code:

Sub PlayMovie()
'URL is the location of the movie - change appropriately
WindowsMediaPlayer1.URL = "C:/rb.wmv"
WindowsMediaPlayer1.Controls.Play
End Sub

Play the movie just as you would run a Macro: Tools->Macro->Macros

You may also want to create a couple of shortcuts to Hide and Stop playing the Movie if the boss approaches. Add the following code below the previous code:

Sub HideQuick()
WindowsMediaPlayer1.Visible = False
WindowsMediaPlayer1.Controls.pause
End Sub

Sub ShowQuick()
WindowsMediaPlayer1.Visible = True
WindowsMediaPlayer1.Controls.Play
End Sub

Next add shortcuts to the 2 macros (Tools->Macro->Macros->Options) and you have an instant ON/OFF switch at your fingertips.

SideNote: While watching a movie directly in Excel is interesting, my favorite way to watch a movie while working is using the stand alone Media Player with a few different options set.

From the Tools->Options menu check the box “Display On Top when in Skin Mode”. From the View menu select “Skin Mode” (a small skin works best). Move your movie to the bottom right or left of your screen.

With this setup I can work and also switch between apps and always have my movie visible, yet out of the way. Thanks to Kingzter.

“Addison-Wesley and Developmentor have provided TheServerSide.NET with the entire book of Essential XML Quick Reference for free download. Essential XML Quick Reference is for anyone working with today’s mainstream XML technologies.”

Essential XML Quick Reference free download link.

You can also Buy the Book.

I mentioned earlier an interest in the Amazon Research Services in Excel, mainly out of curiosity. Well after killing some time with the long tutorial and downloaded documentation, I still hadn’t arrived at a working solution.

I quit trying when I found an awesome demo Excel spreadsheet amongst the uncompressed download folder. Screenshot.

Basically all the nuts and bolts are there for querying the Amazon Research Services and returning the data in an Excel spreadsheet, and doing so with a minimal amount of code.

For those interested in incorporating Amazon Web Services data in a spreadsheet:

1. Download and Run this file
2. Goto the Folder “Office Samples”
3. Goto the Folder “OfficeCreateAnAmazon
ResearchServiceSample”
4. Open the Spreadsheet MyAmazon.xls

I’m not sure of any practical uses for this yet, if you do please share. Maybe some Amazon lookup UDF’s? Or possibly there is a creative way to monetize the addins people make, instead of selling them outright?

About Subscription ID
You usually need a Subscription ID or Token to use Amazon Web Services, however with this Microsoft download there is one already included in the code. Still, I recommend getting your own (it’s an easy and automated process).

Sidenote on Monetization… After I finish writing this post I’ve got a 70$ product to buy on Amazon. If there was an addin I used frequently or shareware I really liked, and I downloaded them for free; I wouldn’t hesitate to click through an Amazon associate link embedded in the product and support the author. Possibly on the about page?

I’m not an affiliate expert, but a quick guess is: 70$ x 5% = 3.50$. I’d much rather give that to somebody deserving and not let Amazon keep it.

The official way to provide suggestions and feedback to Microsoft Products:
Microsoft Product Feedback

Chip Pearson has a good rundown of some various tasks in programming the VBE itself, which include:

Adding/Deleting A Module
Adding/Deleting A Procedure
Deleting All Code From A Module
Listing All Modules In A Workbook
Copying Modules Between Projects
…..for starters.

Bookmark: Programming To The Visual Basic Editor

Thanks Ross.

If you use the hyperlinks that open webpages in Excel you may want to change the text that identifies the link(called Anchor text in webspeak).

Usually you can just type a URL in a cell and it automatically converts to a Hyperlink. This works fine for linking to Domains, however when I link to individual pages the link looks long and ugly. Fortunately Excel provides a way to choose the text that displays. Excel calls it friendly_name.

To control the text that displays for a Hyperlinked cell, enter your link as follows:

Syntax

=HYPERLINK(link_location,[friendly_name])

Example

=HYPERLINK(”http://www.automateexcel.com”,”Automate Excel Home Page”)

I recently posted how to Unhide All Worksheets and John Mansfield asked how do you approach the problem if the VBA Project is protected?

A good question since the solution could require running new code in a VBA Project that’s protected. I didn’t have an immediate answer, however Ross had a good idea.

In summary:

1. In a blank workbook enter your code in the Visual Basic Editor
2. Save the blank workbook as an addin (.xla file)
3. In the workbook that is protected, import the addin you just created and run the addin code

Update 12-28-04: Nick Burns points out (see comments) that the immediate window in the VBA project serves the same purpose

Microsoft updated it’s Excel Viewer – “Open, view, and print Excel workbooks, even if you don’t have Excel installed.” The old link is also still live.

(via DDOE)

Andrew Engwirda just released a UDF addin for Excel, Andrew’s Custom Functions. It includes the following functions right out of the “box”:

hasformula, getformula, getformat, getfont, getcomment, sheetsname, booksname, fullname, usersname, rowsize, columnsize, firstinrow, firstincolumn, lastinrow, lastincolumn, millions, thousands

Excellent if any of these functions are useful to you, or as an addin to be built upon.