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.

If you would like Rows (at the top) or Columns (at the left) of your spreadsheet to remain visible while scrolling you can “freeze” them.

For instance, if Row 1 contains column headings and your screen view requires scrolling, you may want to be able to see the headings regardless of how far you scroll.

In the following picture Row 1 contains my headings and is frozen. I have also scrolled to Row 150. Notice that my headings are still visible:

Freezing Rows
Select the Entire Row Below the rows to Freeze and on the main menu select Window->Freeze Panes. This will Freeze all of the rows above your selection.

Freezing Columns
Select the Entire Column to the right of the Columns to Freeze and on the main menu select Window->Freeze Panes. This will Freeze all of the Columns to the left of your selection.

Freezing Rows and Columns
Select the first Cell below the Rows and to the right of the Columns to Freeze and on the main menu select Window->Freeze Panes. This will Freeze all of the rows above your selected Cell and all of the Columns to the left.

View how to Freeze Panes using VBA.

Charles Maxson: “We are putting specifications together for a XML toolkit for Excel 2003 developers”…”So if you are working with Excel XML and know of something that you would really like to see added to the toolkit, please post your feedback”. You can post your feedback here.

I know the Excel XML basics so I have no comment, however if you’re an Excel XML power user, it sounds like a good opportunity to have input on the tools you use.

I don’t use the Research Task Pane much but these links have sparked my interest:

The Definitive “Hello World” Custom Research Service Tutorial

Build Your Own Research Library with Office 2003 and the Google Web Service API

Creating an Office 2003 Research Service Using the Amazon Web Service API

I’m most interested in the Amazon link, unfortunately it’s also the longest. 34 printed pages? Also “In order to use this content from Amazon.com, you are required to apply for a developer’s token ”

The obvious way to remove blank rows from a data set is to simply sort the data. This moves the blank rows to the bottom of the data and “removes” them. But what if you want the blank rows removed, however you don’t want the data sorted? VBA.

The following macro will remove blank rows without any sorting (turn off screen updating to go faster).

Sub DeleteBlankRows()
Dim x As Long

With ActiveSheet
    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _
        To 1 Step -1

        If WorksheetFunction.CountA(.Rows(x)) = 0 Then
            ActiveSheet.Rows(x).Delete
        End If

    Next
End With

End Sub

There are a couple of easy ways to compute exponents in Excel. Let’s assume we want to find 5 to the 10th power, we could use either formula:

=5^10

=POWER(5,10)

You can use a scroll bar to change the value of a cell that contains a variable number. For a good example of using scroll bars to control cells that drive a chart, download this Projectile Motion Spreadsheet.

Add a Scroll Bar to a Worksheet
1. Open the Forms Toolbox by going to View->Toolbars->Forms
2. Click the Scroll Bar icon in the Forms Toolbox

3. Click and drag on the spreadsheet to create the Scroll Bar

Link a Scroll Bar to a Cell
1. Right click the scroll bar and select Format Control
2. Make sure the Control Tab is selected and you’ll see

3. Set the options as desired and hit OK

Summary of the Options
Minimum Value: The Minimum Value of the Linked Cell

Maximum Value: The Maximum Value of the Linked Cell

Incremental Change: The amount to increment the linked cell value when the Arrow on the scrollbar is pressed

Page Change: The amount to increment the linked cell value when the Scroll Bar Face is clicked

Cell Link: The address of the cell to be linked to the Scroll Bar

Dummies.com has a nice summary of the various formula errors and their common causes.

John Walkenbach (aka J-Walk) has just released the newest version of his Power Utility Pak v6.

It’s free to use for 30 days and has a nice list of features.

John Battelle points to the new Yahoo Video Search. If you have some patience and enjoy video tutorials, you might try:

Excel Video Search
Microsoft Excel Video Search (less results)

The Environ Function can return the logged in User name, Number of CPU’s, Temp directory, Windows directory and more.

Link: Excel VBA: Function to get logged in user name (plus the Environ Function)