United States
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers. Turn around expense reports in a snap with Excel 2007 and XML
Applies to: Microsoft Office Excel 2007
Two words that may make you run for cover: expense reports. You dread them, you defer them, and you despise them, especially when Accounting returns them for minute corrections. Put those fears aside and learn to positively love expense reports. Impossible, you say? Not if you team up with Microsoft Office Excel 2007 and Extensible Markup Language (XML) to turn those expense reports around in no time flat. This article explains how you can automate expense reporting by using XML data in an Excel worksheet. We even provide a sample expense report, sample XML schema and data, and sample code to get you started. In this article
Expense reports: the old-fashioned and new-fashioned wayAre you handling your expense reports by printing an Excel template, writing in your expenses by hand, and then turning it in with crumpled receipts and crossed fingers? I sure hope not. But even if your Information Technology (IT) department has expense report automation at the bottom of its backlog, don't despair. You can quickly push expense report automation up that stack of requested applications. How, you ask? Two words that may make you leap for joy: smart client. In a traditional client/server application, data is stored on a network server database (the back end), and forms are displayed on a client computer (the front end). A smart client adds built-in support for XML, leverages the full power of a personal computer, offers a familiar user interface, and has a rich set of features that you can use to analyze, publish, report, and share data. Sounds like we just defined Excel, not to mention the Microsoft Office System. Most users are already familiar with entering data into Excel. Besides, an Excel-based form is a natural for expense reports. You can take advantage of formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), Excel tables, comments, data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.), cell and worksheet protection (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.), and even smart tags (smart tags: Data recognized and labeled as a particular type. For example, a person's name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.), to name a few features. And then, of course, there's XML: the open, industry standard that makes it easy to define, transmit, validate, and interpret structured data between applications and organizations. Now toss the custom XML Schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) feature into the mix. This makes it possible to map an XML schema file (.xsd) to specific cells in a worksheet, save mapped cell data from your worksheet into an XML data file, or import an XML data file and bind the data to these mapped cells. You can think of Excel as an XML data file generator, a powerful front end to new or even earlier applications. In many cases, you won't even need to write code, either Microsoft Visual Basic for Applications (VBA) or Extensible Stylesheet Language Transformation (XSLT). If you do need code, you don't need much. By combining the time-tested features of Excel with the XML schema-mapping features in Excel, you can fill out that expense report and submit it faster than you can say "Show me the money!" IT folks take notice: Custom XML schemas and XML data make it simple to retrofit an existing front end and connect with back-end databases. Your custom applications don't have to be continually redesigned from the ground up. How expense reports get done from the top downThe following diagram illustrates a typical workflow for expense report processing. This workflow uses Excel as a smart client on the front end and XML to communicate with a back-end database on a server. 
 You fill out the expense report by using an Excel worksheet that has been mapped to a custom XML schema and save the data as an XML data file.
 You submit the expense report data in the XML data file to the back-end database.
 Accounting scrupulously checks the expense report for accuracy and adherence to internal business rules and spending limits.
 If Accounting approves, you get a gold star (and a reimbursement check).
 If Accounting does not approve (that fancy restaurant didn't pass muster), they send an e-mail message back to you telling you to adjust the expense report and resubmit it.Let's look at the "Fill out expense report" step more closely. 
First, an IT developer creates a custom XML schema, defined in an XML schema file, and maps the appropriate cells in the Excel worksheet. Note that this is done only once. In practice, users obtain the expense report with the schema already mapped, often by downloading it from an internal Web site.
Then, users enter identification information and line-by-line details into an Excel worksheet that looks just like an expense report.
Finally, after completing the expense report, users save the data to an XML file.Take a stroll through the processGot motivation? Then put your walking shoes on and learn how to use the sample Excel workbook and XML schema file, step by step. First, you'll add the schema to the Excel workbook and map specific data elements to corresponding cells in the expense report worksheet. Then, you can practice typing data into the expense report, and save the data to an XML file for further processing. Finally, you'll learn how you can submit an expense report and how to revise it, if necessary. Examine ExpenseReport.xlsxNow, let's look at a real Excel workbook (.xlsx) just to show we're not pulling your leg. You can use this workbook as a model for designing your own expense report workbook. To download the workbook, do the following:
The expense report is an Excel worksheet that takes advantage of built-in features of Excel to streamline filling out an expense report. 
Use comments to provide context-sensitive assistance for each cell.
Link users to an internal Web site for detailed information about how to use the expense report.
Visually highlight cells that contain formulas.
Lock cells that contain formulas, to protect them from users who are accident-prone.
Locally validate data, such as special codes.In your application, make sure that IT developers protect the entire workbook and create a password before users obtain the workbook, so no one jimmies those locked cells. Savvy IT developers can even add smart tags to the Excel template and turn a smart client into a bona-fide genius. For example, you can use a smart tag to pre-load the expense report with user information, validate cost center codes from an external database, or link to Web pages for detailed information about a cell. Examine ExpenseReportSchema.xsdThe following XML schema file defines the data that you want to capture in the expense report and send to Accounting (the folks with the white shirts and green eyeshades). Usually, IT developers create this schema because they understand best what data needs to be defined and captured.
For detailed information about how to create an XML schema, see XML Schema Part 0: Primer, published by the World Wide Web Consortium (W3C). Add the sample schema to the workbook
The XML map is displayed in the XML Source task pane. The task pane displays a simple hierarchical view of the XML schema that makes it easy to view and map elements to cells. Map the nonrepeating data to single-mapped cellsNonrepeating data, such as information about the user or the purpose of the trip, is mapped to single cells. In an Excel worksheet, cells that contain nonrepeating data are called single-mapped cells.
Map the repeating data to an XML tableRepeating data elements, such as the line item information, are mapped to rows of data. In an Excel worksheet, rows that contain repeating data are XML tables.
Congratulations! You just mapped all the line item columns in a single step, and you also created an XML table. An XML table has the added advantage of automatically expanding when users add new rows to the table in the worksheet. Note Repeating data elements are mapped together into an XML table when the XML Map property Automatically Merge Elements When Mapping is selected (this is the default value). To modify this and other XML map properties, right-click on the XML table, click XML, and then click XML Map Properties. Enter data into the Excel expense reportUsers of the expense report can now organize their crumpled receipts and type the data into the spreadsheet. Still got motivation? Type the following sample data into the expense report and feel the love. Submit the expense report dataAfter you submit the XML data file to a pickup folder, the data can be processed in many different ways, including the system that your enterprise uses to handle expense reports, of which there can be quite a variety. For example, the XML data file could be sent to the Accounting department, perhaps to a dedicated e-mail account that retrieves the XML data and stores it in an equally dedicated folder. The XML file could also be imported into any number of databases, including Microsoft Office Access, Microsoft SQL Server, and third party databases. Or, you may have an Office SharePoint Server custom workflow operation that uses Microsoft Office InfoPath and Forms Server. There are many possibilities, but this attests to the remarkable flexibility of XML. Here are two ways you can submit the data, one without code, and the other with code. Save the expense report data as an XML data file without codeWhen you manually type (or import) data into a mapped workbook, that data becomes part of the workbook when it is saved. But it is not automatically saved as an XML data file. To create an XML data file, do the following:
If all is well, your data should look like the following file.
Save the expense report data as an XML data file with codeWith just a few lines of VBA code, you or your IT developer can add a Submit button that would export the data for you and send it to a pickup folder for subsequent processing and verification. Here's an example that creates a time-stamped XML data file and saves the workbook with the same timestamp so you and Accounting can keep track of each go round:
To add this code, do the following:
Revise the expense report dataIf you need to make changes to your expense report later, because Accounting sent you an e-mail with corrections, you can revise it by opening the workbook, Expensereport.xlsm, making the necessary changes in the workbook, and then resubmitting the data. It's that easy. The bottom lineExpense reports in your company are now a breeze. At that recent business conference in Chicago, you were a hero with your fellow road warriors. Why, just last week, the entire IT department delivered flowers to your office and personally thanked you for lightening their load. The other day, you courted an important customer with an expensive dinner and finally closed that big sale. This morning, even your boss patted you on the back, complimented you on that paisley tie, and said… "Don't forget to fill out those expense reports." |
Email this link
Printer-Friendly Version
Share
Show All
Hide All
, and then click Excel Options.
.| Related Web Sites |
| ||||||
