Automate Excel

DATEDIF is handy for formulas which find the difference in dates. Particularly, I use it most to quickly find the days from an event or to one.

The DATEDIF syntax:

=DATEDIF(EarlierDate, LaterDate, Format)

=DATEDIF(A3,B3,"d")

For more on the DATEDIF Formats and usage, check out Pearson Consulting: DATEDIF

The following is a picture example that demonstrates using DATEDIF to determine the days to and from an event. The first calculates the days since Tom Quit smoking and the second calculates the days to a wedding.

(Note: 58 days until I attempt a 5k race

A DomDocument is a container (variable/object) for holding an XML document in your VBA code.

Just as you use a String variable to hold a strings value, you can use a DomDocument to hold an XML document.

(for a complete list of a DomDocuments properties, see halfway down this page)

Why do I care what a DomDocument is?
Excel has some very intuitive ways for moving XML into a spreadsheet (XML Maps), but:

•What if you want to manipulate the data after you’ve retrieved it, but before the data appears in your cells?

•What if you want to import XML data into controls instead of mapped cells, like comboboxes, labels, or textboxes?

Both of these tasks are difficult (if not impossible) to accomplish using XML maps. We can however import the XML data to a DomDocument, then pull out the data we need, write to controls, filter the data, or manipulate the data before it appears in a spreadsheet.

How do I Load XML into a DomDocument?
The following example uses Excel 2003. In the Visual Basic Editor Goto Tools->References and place a checkmark in the box for “Microsoft XML v5.0″

Now we need some XML. Recently I created a Google sitemap for this site, and it’s in XML, so lets use that: AutomateExcel Google XML Sitemap

To load my SiteMap XML document into a DomDocument object in Excel, use the following code (which is commented explaining things):

Sub DomDocumentBasic()

Dim oDom As MSXML2.DOMDocument

    'Create the DomDocument Object
    Set oDom = CreateObject("MSXML2.DOMDocument")

    'Load entire Document before moving on
    oDom.async = False

    'Don't Validate
    oDom.validateOnParse = False

    oDom.Load ("http://www.automateexcel.com/sitemap.php")

    MsgBox oDom.XML

End Sub

The code simply loads the XML and displays it in a message box:

Note: If you load XML from a URL and are having trouble, make sure you didn’t forget the line “oDom.async = False“.

How do I traverse the DomDocument?
Now that I’ve got the XML data in an object, how do I “do something” with it?

LOL, I’m still learning this part. I’ll post the basics in a future post and drop a link to it here.

The post title was “What is a DomDocument”, hopefully you have an idea now.

Ross,

Here is a barebones example to return the first result from a Google search in Excel using the google API.

In order for the code to work you need to download the appropriate Soap toolkit, and follow the directions for loading the WDSL. You can find the Google WSDL here.

I’ve tested the code with the 2003 toolkit and it works fine. You may need to tweak it some with the 2002, not sure.

Also, keep in mind that the google API returns html formatted results. Maybe Dick has a solution for that?

You’ll need to add your google developer key to the code also:

Sub MyGoogleSearch()

    Dim GoogleSearch As New clsws_GoogleSearchService
    Dim GStruct As struct_GoogleSearchResult
    Dim GoogleKey As String
    Dim GoogleSearchWord As String

    GoogleSearchWord = "Excel"
    GoogleKey = ""

    Set GStruct = GoogleSearch.wsm_doGoogleSearch _
    (GoogleKey, GoogleSearchWord, 0, 1, True, "", _
    True, "", "utf8", "utf8")

Sheet1.Cells(1, 1) = GStruct.resultElements(0).title
Sheet1.Cells(2, 1) = GStruct.resultElements(0).snippet
Sheet1.Cells(3, 1) = GStruct.resultElements(0).URL

End Sub

Note: I’m sure you could finger it out yourself in 10 minutes, but I thought I’d save you (or others) the time

Last week I bumped into the Coding4Fun article Using the Amazon Search Web Service and made a mental note to accomplish the same result in Excel.

I didn’t actually download the example, but set about to create something based on the screenshot given.

And here’s a screenshot of my version (Download is at the bottom):

One major difference between the Coding4Fun Example and my example is the Codinging4fun uses the Amazon SOAP web service, and my example uses the REST web service.

The choice to use the REST over SOAP came down to two reasons
1. I prefer REST over SOAP
2. Using SOAP (even with Excel 2003) requires an extra Toolbox from Microsoft. Using REST requires only setting a reference to the XML parser* (in this example that is Microsoft XML, v5.0), which should be installed on any Office 2003 Machine)

Compatibility
The Amazon Search UserForm was tested in Excel 2003 on a machine with Internet Explorer installed (for navigating to the Item URL), and an Internet connection.

Notes
The search query response from Amazon is a “Large” response to return the image URL’s also. Depending on your connection speed a search make take a few seconds.

I’m still an XML parsing rookie; The code isn’t password protected so feel free to try your hand at reducing the lines of code. (or creating your own modified version (a large response provides much more information than I actually used))

Credits
I loaded the Item pictures in the form to a picture control, from a URL, using the amazing Juan Pablo Gonzalaz code.

The paths to the XML elements were discovered using the 2003 XML Toolkit

Click this link to download the Amazon Search in an Excel Userform

Try to break it, modify it, let me know what you think.

*You don’t need a reference to the XML parser if you don’t use VBA to parse the XML. Instead simply map the data to your spreadsheet cells.

Microsoft launched a preview site for Office 12: The Future of Microsoft Office: Be the First to Know

I guess they didn’t read the Scoble post: You should be fired if you do a marketing site without an RSS feed.

So, if you want to keep up to date on Office 12 from the official site you need to subscribe to an email newsletter.

There is a “perk” though, the message after registering for the newsletter:

“Thanks for registering! You’ll get the latest news and information about the next version of Microsoft Office

Channel 9 has some video and additional links on the new Office file formats.

They also link to Brian Jones, an Office XML blogger. If you’re interested in reading more about the formats in detail, Brian points to the Whitepapers for the New Office Formats.

Office 12 to Get New File Formats - “On Thursday the company will announce that it plans to make XML-based file formats the default in the version of Office”

and…

“The new Word, Excel and PowerPoint formats will be designated as .docx, .xlsx and .pptx , respectively.”

Read another article at Yahoo