Automate Excel

The Microsoft Template Site has the 2006 calender templates available.

Excel Calender templates are popular for working day calenders and when any type of yearly scheduling graphic is needed.

And if you like these calender’s we’ve also got the links to the 2007 Calender Templates.

Here’s some direct links to the 2006 Excel Calender Templates:

2006 calendar (1-pg., landscape)

2006 calendar (1-pg., landscape, Mon-Sun)

2006 calendar on multiple worksheets (12-pp)

2006 calendar on multiple worksheets (12-pp)

2006 calendar (1-pg., portrait)

2006 calendar (1-pg., portrait, Mon-Sun)

2006 calendar on multiple worksheets (Mon-Sun, 12-pp.)

2006 calendar on one worksheet (12-pp.)

2006 calendar on one worksheet (12-pp., Mon-Sun)

2006 calendar with room for notes (1-pg.)

2006 calendar with room for notes (1-pg., Mon-Sun)

I’m still enjoying David Gainer’s Excel 12 blog. Did everybody catch the screenshots of Excel 12’s new charting engine? Very nice..

I’d like to make a request, or offer a topic for a post you may get to someday: What’s up with programming in Excel 12?

This is the topic that caused the most interest prior to Excel 12 beta, and remains one of the least talked about topics still.

I need to know: Will the scroll mouse work in the Excel 12 Vba IDE? Lol.

Or, will code I write today work in Excel 123 ?

The most I’ve seen is this “Microsoft continues to state that VBA will be included in Office for many, many, more versions.”

Which would answer question 2, but knowing about the wheel mouse and other IDE upgrades could make for an interesting post, maybe.

Many people use the Overture Keyword Tool and Excel to track their Keywords.

This combination allows you to track the words being searched for on any given topic.

And with good reason, even the Google Information for Webmasters states “Think about the words users would type to find your pages, and make sure that your site actually includes those words within it.”

So why not use the Overture Keyword Tool and Excel together? The Excel Nexus put together a nice spreadsheet that does just that. It gets overture keywords into Excel, what to do with the data is up to you.

For the Excel Geeks a web query tutorial and for the keyword geeks an Excel keyword tool: Excel Web Query Secrets Revealed

(For the Download: Scroll down and find the link that says Overture Keyword Suggestion Tool Example)

In case those of you wondering about the Office 12 beta missed the announcement: REDMOND, Wash.

When creating a spreadsheet that connects to the internet, possibly a web service or sending emails, it might be a good idea to test for an internet connection before connecting.

This could prevent a user from getting annoying error messages or having the application just sit there.

While I don’t usually follow this advice, I have been looking into it and found a short script that has been working nicely for me.

The wonderful script is buried at Ron de Bruin’s site. You can find it by visiting Ron’s Tips page then scrolling down a ways, and it’s also archived in the newsgroups over here.

Definitely one to bookmark.

Roundup sounds much better than link dump doesn’t it? Anyway I’ll drop some links occasionally from my bookmark folder to permanent storage here. Maybe you’ll find them useful as well.

Programmable Web Api’s - When a service provider opens up their data via an api, usually that data can be consumed in Excel. Here’s a list of over 100 apis for you to potentially incorporate into your spreadsheets.

Excel 12 Blog - I was reminded last week I hadn’t link there yet, very nice blog.

Andrew’s Excel 12 Bar Graph In Cells - Not ready to wait around for Excel 12, Andrew took a nice stab at recreating Excel 12’s bar graphs in cells.

Yahoo! MapMaker for Microsoft Excel - “a Microsoft Excel template that enables any user of Excel to plot data on a Yahoo! map without programming.” Via ResearchBuzz

Excel Synthesizer - Interesting, via ExcelGeek

I like tools like PingOmatic because I manage some rss feeds that don’t automatically ping web services when I update. So I go there and they do it for me.

The problem with relying on someone else however is when their site is down I can’t send pings, or when my cookies are cleared I need to enter all my input again. (For instance, when I went to link to Pingomatic for this post I got this screen).

I’ve meant to code pinging my rss feeds with PHP, but after exploring sending XML with XmlHTTP I thought it could be done pretty easily with Excel, and it was.

So for the past week or so I’ve been using Excel to ping weblog services like Technorati and Weblogs.com and things seem to be working pretty smoothly.

I simply enter my Blog Name and URL then hit PING! and it will ping the 11 webservices I have chosen. Here’s a picture of the results I get:

I’ll jot down a couple notes in a second, but first here is the download:

Notice: You need Excel 2003 for this spreadsheet to work because of the Version of the XML parser I use. The code is not password protected (as always), so if anybody wants to modify this and post a version that works with earlier versions of excel I’ll post a link to it here. Enjoy.

Download Ping Your Weblog With Excel

Note 1: This can be easily modified to ping multiple rss feeds with a loop or two (which I plan to do).

Note 2: Nothing against Pingomatic, I love the service, just wanted to do it myself.

Note 3: This is also an example of a basic XML-RPC client in Excel. Possibly I’ll post more on this later. What else can I do with this technology?

Note 4: This is the first spreadsheet I’m including a link to the newly created support page. People can’t support your work if you don’t give them a way to I guess. It’s also a small text link so those that aren’t interested can easily ignore or delete it.

Just stumbled on the site Learn Excel from MrExcel Video Podcast.

Bill Jellen already has a couple dozen videos up and it looks like he’s still updating almost daily.

Very cool. The picture-in-picture is a great addition also.

A couple more links: Podcast Feed, Directions on Subscribing with Itunes

(note, the file format is mp4, so you will have to open them with a program that plays mp4 video. I used Quicktime. Winamp opened the file by default and just played the music

Mary Jo Foley and others report that Office 12 has leaked on the internet. She also mentions the beta could be out this month.

Here’s a link from Microsoft describing one way to get the beta. Take the Microsoft Office

The following will connect Excel to a Mysql database, then import a table into Excel.

This was tested on an Excel 2003 machine connecting to MySQL 4.1.14 (this website’s database).

After reading numerous tutorials on how to connect Excel to Mysql they were all saying the same thing, and all of them failed (for me). This was because they were never specific on the driver to use. After reading this quick note and modifying some urls to find the download location, I achieved a successful connection, and here’s how…

Step One
Download and install MyODBC-3.51.11-2-win.msi

Step Two
Add a new datasource to your windows environment. From your start menu click Settings->Control Panel.

In the newly opened folder click Administrative Tools->Data Sources(ODBC).

Click the Add Button, scroll down the list and double click the MySql 3.51 driver. Enter a name for the connection and your database info then hit test. With some luck you will get a Success message. Your data connection is now set up.

Step Three
Back in Excel: On the main menu click Data->Import External Data->Import Data.

In the newly opened dialog box click the New Source button towards the bottom then double click ODBC DSN. You should now see the datasource you setup in the prior section. Double click this.

Select the table you want from the query editor, hit Next->Finish.

Almost finished, you should be brought back to the “Select Datasource” dialog again, click Open now and you will be asked what cell to put your new table data in. Pick your cell and click OK. You will be asked to review your database settings:

Note 1: Triple check your credentials, for some reason my username is truncated here.

Note 2: This is the step that fails by picking a random driver from the MySql site (even though test connection had worked).

Cross your fingers, click OK, and bammo your Mysql dataset is now in Excel. Here’s a snippet of my posts table in Excel: