Integrating with Word, Excel, PowerPoint, Outlook, PDF for Office Automation

[drjohnchun]

While I was working at a pharmaceutical company, I had to generate lots of documents for compliance reasons. In some cases, I had to pull data from ServiceNow to embed them in Word documents, such as requirements, test scripts, traceability matrix, etc. Soon it became very labor intensive so I decided to automate the process using the ServiceNow ODBC driver and Microsoft Office VBA (VisualBasic for Applications, a macro language). This allowed me to extract data from ServiceNow and use them in documents; the time savings was huge.

I originally published this on Share shortly after presenting it at Knowledge 14 in San Francisco. Since then, I lost access to Share and, unfortunately, wasn't able to restore my access. So I'm republishing it here after making some revisions.

Attached are

1. Slide deck that was presented at K14 with instructions on how to run the samples in the Word file.
2. Word macro file containing VBA samples for Word, Excel, PowerPoint, Outlook; pdf files are created by exporting Office files as pdf.

The Word demo file was created using Word 2010 but should work with later versions. Below is the abstract from the deck:

Beyond ITSM, ServiceNow is a powerful platform for any business service request management. Enhance the power by fully integrating with Microsoft Word, Excel, Outlook and PDF, from simple form-based report generation to live, interactive documents. Automatically pull contents from ServiceNow and apply formats to meet business needs. Add password protection or digital signature for enhanced security. Simple, practical do-it-yourself solutions will be demonstrated and best practices discussed. If you ever wanted more than the standard Excel and PDF export from ServiceNow, this is a must-attend session!

The slide deck includes:

1. Solution — Requirements Tracker
2. Solution — Overview of exporting to Word templates
3. Solution — Prepare Word VBA
4. Solution — Open ServiceNow Database
5. Solution — Query ServiceNow Database
6. Solution — Create Word Documents
7. Solution — Export to PDF
8. Solution — Protect Document (using Digital Signature)
9. Solution — Create Excel Spreadsheets
10. Solution — Create PowerPoint from Excel
11. Solution — Create Outlook Email
12. Alternate Solutions
13. Tips — ODBC
14. Tips — VBA

Hope you find it useful and please post questions if you run into anything.

Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse.

John Chun, PhD PMP