The term ‘office automation’ can mean different things to different people, from installing mail sorting equipment, or copy and binding machines, to adding macros to Microsoft Office. For our purposes, we define Office Automation as anything that makes it possible for businesses to improve their productivity by optimising office procedures. This can take many shapes and forms but in any business the low-hanging fruit is generally to be found in re-engineering the use of existing off-the-shelf software to automate repetitive processes, or protect data from corruption or loss. Here are just a few examples of the kind of projects we have successfully completed:
- Create worksheets that have to be validated before the system lets the user save the spreadsheet.
- Create worksheets that can read information from another system (i.e. database) to populate dropdown boxes, etc
- Create company expense report that saves the information into a database for summary/analysis purposes
- Combine data from multiple Excels spreadsheets/worksheets to create a new formatted Excel spreadsheet
- Create automations to eliminate people having to rekey, massage, or format data.
- Create documents that can read information from another system (i.e. database) to populate dropdown boxes, etc.
- Create documents that have to be validated before the system lets the user save the document.
- Create documents that upon saving can save the information on the document into a database.
- Create documents that can popup and format an Outlook email with data from the document to be sent out upon saving a document.
- Contacts, Appointments, Sales, Employees, Expenses, Telemarketing, Service, Human Resources, Ordering, Inventory, Engineering, etc.
- Create Word documents on the fly to populate letters, envelopes and custom reports based on data in a database.
- Create functionality that can open up a Word template, fill the document with data, print the document and save the document with 1 click of a button.
- Create functionality that can open up an Excel spreadsheet and populate it with data.
- Create functionality that can open up an Excel spreadsheet and populate it with information as well as determine where to insert/delete rows if needed to keep the integrity of formulas intact.
When you want to create a desktop application with the kind of functionality that doesn’t readily fit into any of the above solutions, then the fallback is VB.NET. Not to be confused with VBA, which is a subset used for automation in the Office packages, VB.NET is a development platform used for building industrial strength applications that can connect to a myriad of databases and API’s. We’ve used it to create processing systems for sensor data, stock control systems for manufacturing entities, and training management systems for large corporate clients.
Google is working hard to make inroads into Microsoft’s domination of the office applications market, and their equivalent to MS Office is called G-Suite. Its challenger to MS Excel is called Google Sheets. Its big advantage for small businesses is that it is free and online, so that you can access your data from anywhere. Not as powerful as Excel, but more than adequate for most of the tasks that the average business, or department of a larger one, will need to perform. Automation is via Google Apps Script, with HTML and CSS used to create user interfaces.
Other Google Offerings
A powerful word processor, again with everything shareable online, and very intuitive use. We’ve used it to collaboratively create and share large documents across teams with members located all over the world without any problems. We also use it as part of our CRM system to mail merge standard letters and manage mailing lists without having to go outside of the suite and use something like MailChimp of Constant Contact.
OK, just kidding! Google doesn’t have an equivalent to MS Access, so it all gets a bit tricky if you want to create fully-fledged databases in G-Suite. It can be done, but we wouldn’t recommend making it your first automation project without talking to us first.