Just another Microsoft MVPs site

Anyone up for some Office Automation?

Ok, I’m going to try to make this quick.  After I left the office this afternoon, I stopped to check out the new Best Buy that opened up close to home.  While I was there, I picked up the Harry Potter and the Prisoner of Azkaban (great movie – but the book was WAY better.)  I want to watch it yet tonight, but I’ve gotten caught up in some email, and I want to get this post out since it’s been in my head all day.  So . . .


I’m going to go out on a limb here and say that Microsoft Office is probably one of the most under-valued application suites in the small business space.  Come on – just take a look at all of the functionality available under the hood.  You can do more with Office than most small businesses could ever imagine.  Let’s take Excel as an example.  We’ve all seen what most small businesses use Excel for – it can usually be boiled down to lists of some form (maybe for a mail merge), or using the built-in functionality in Peachtree or QuickBooks to dump a report to Excel and sort it differently.  You do have a few that may use some basic formulas to sum columns, etc. – but not much else.


There is SO MUCH we can do with Office that it isn’t even funny.  I myself am an Access junkie – and spend a lot of time messing around with VBA in both Access & Excel.  I’m working with one client where we’ve built some reports in Excel that save them so much time & effort it isn’t funny.  I’d normally do something like this in Access, but they had a previous solution that was using Excel, and that’s what everyone was used to.  They upgraded their accounting system, which required that the solution be recreated since the entire underlying data connections weren’t valid anymore (and the previous individual who created the original solution had key functionality embedded in XLAs that were locked down and inaccessible to edit – and he was long gone).  This particular client has extended the functionality of their accounting software by creating a Project Status Report (PSR) template.  Whenever they are awarded a job, they enter their itemized breakdown of cost & revenue estimates, and also list each subcontractor with the subcontractor’s contract amount.  The PSR also allows for Change Order information to be added.  One of the custom solutions we have pulls all of the job data out of the accounting solution and organizes it according to their job designations.  This results in a single Excel workbook with multiple worksheets – one for each job class (A Jobs, B Jobs, C Jobs, etc.), one for only the active jobs for each class (A Active, B Active, C Active, etc.) one for each Project Manager, and a summary sheet that provides statistics by Project Manager (total projects, % of total revenues, markup estimated, markup earned, etc.  In addition, as this custom workbook is built, as it is processing each job from the accounting system, it looks to see if there is a PSR for that job.  If so, it opens the PSR and updates all of the individual line items (cost incurred to date), and updates the total billings from each subcontractor for that job.  If a subcontractor’s total billings exceed their contract amount, another workbook is opened with adds a worksheet for that subcontractor and builds a custom Account Ledger for all activity for that subcontractor / job, and adds the job, subcontractor, total contract amount, total billings amount and total overage amount to the summary sheet.  They have a lot of data, so it takes this about 7-8 minutes to run.  (70% of that is due to the lackluster performance of the ODBC driver for their accounting application).  When it is all said and done, they have up-to-date performance numbers for each of their Project Managers, PSRs give an exact picture of how a job is evolving, and the subcontractor workbook gives a single report of all subs who have overbilled their contract, as well as a custom ledger for each subcontractor showing exactly the information / activity our client wants to include to help their sub reconcile the discrepancy . . .   Cool huh?   And it’s all thanks to the built-in functionality of MS Office.

2 Comments

  1. Terry Constable

    I’m not surprised, Chad. My day job is with one of the country’s big mortgage companies in their mortgage processing center; and most of what I do is in Access and Excel. I’m half DBA and half Business Analyst using Office to crunch billion dollar numbers because the schmucks back in IT don’t know how to make useful SQL reports and won’t let anyone else play on their servers. Heck, I’d wager to bet that half our financial reporting comes out of Access and that 80% of the essential reports around here are done in Office programs. But it keeps me in a job and I love bending Access around to do new and interesting things!

  2. Chad

    Hey Terry – if you haven’t done so already, take a look at Microsoft’s SQL Reporting Services. I’m starting to use this more and more – and it’s pretty impressive. The best part is that it makes report delivery so simple since it’s all web based – you don’t have to worry about users messing with data connections, etc. Build the report once, specify the data connection & parameters – then the users can provide the parameter values & run them via their web browser whenever they want. And Reporting Services provides built-in functionality to export the reports to multiple formats, including Excel & PDF . . .

Leave a Reply

Your email address will not be published. Required fields are marked *