There are many different configuration options to consider when designing a MS Access application, or migrating between setups. In this blog we will look at some of the most common configurations.
Continue reading
Did you know you can link live Excel charts and tables within a connected PowerPoint presentation or Word document?
In a recent project that came to us a client was looking for a way to bring multiple sections of an Excel document into different parts of a Word document. The Excel file would be continually updated, so the need was for these live updates to automatically be reflected in the Word document without the need for the users to complete lengthy edits of the Word document every time.
There are a few ways this could have been achieved:
In this particular instance, the Excel file in question was a fully fledged file in its own right that would be updated/accessed outside the need to just update the Word file and so the embedding method was ruled out. The custom VBA approach would require too much development time for this project. So, the linked Excel route was taken.
The Copy and ‘Paste Special’, ‘Paste Link’ method can be used across multiple charts/tables or ranges of cells to bring in multiple separate objects/sections into the Word/PowerPoint doc.
Once the linked objects were now in place, upon opening the Word file the user would receive the following message.
Upon selecting ‘Yes’, MS Word would seek to update any of the linked elements in the document with the very latest data available.
A few things of note here;
It is best to keep the linked files stored within the same root folder and to not rename the Excel file. If stored in the same location and the Excel file is not renamed, they should remain linked.
If the link between Word and Excel does become broken it can be reset in Word by going to File>Info>Edit Links to files.
If you select ‘Yes’ while the Excel file is closed, Word may take upwards of a minute to open and refresh the data. If, however he linked Excel file was opened first, this updating process is instant. As a result, it is highly recommended to first open the Excel file, then open the word file and to select ‘Yes’ to the update links message. This is particular true where there is more than one object/section linked.
If you have a project where linking to Excel is a requirement, please do not hesitate to contact Olas to discuss how we can assist you on the project.
Power Query is a powerful business intelligence tool inbuilt in Excel, that allows you to import data from any number of sources, clean it, transform it, then reshape it accordingly. This once off created query can then be re-used over and over again by simply refreshing it
Continue reading
Technology: Microsoft Power BI and Microsoft Excel
Client: Large distiller and distributor of some of the most famous Irish and internationally spirit brands
Year: 2020
Our client was seeking to develop a new global reporting suite of dashboards for the procurement side of the business, detailing high level spend reports across the business with a focus on a number of newly created KPI’s (Key Performance Indicators) across areas such as Cost Optimisation, Risk and Innovation. In this instance the Microsoft Power BI business intelligence platform was chosen for the project.
The Power BI dashboards were developed for a top senior level executive audience. Data was reported summarised up to the highest level but then with interactive abilities for the user to delve into the detail using slicers to filter the data and to step further down the data hierarchies in place. The reporting suite created, took in data relating to budgets, capex spends and P&L data and produced a very visually pleasing, easily readable, very clearly presented factual figures. With the ability to filter by procurement categories and sub categories.
Olas worked closely with the client to firstly help finalise the metrics/formulas of the newly created KPIs and to help gather all the required data to perform those calculations. The next task was then to design MS Excel templates that would be used to capture all the necessary data. In the end, we ended up with three source data MS Excel based files, that would be maintained and updated by the procurement team for continual data input going forward. Once the data was captured and we had a blueprint on paper of what we needed to achieve from a reporting perspective, Olas then proceeded to take those inputs and to transform the data into a powerful suite of carefully thought-out Power BI dashboard reports. The end result provided very clear views of detailed analytical data covering procurement KPI’s and spend right across the entire global operation. We took advantage of the powerful business intelligence capabilities of the Power BI platform to seamlessly merge and transform data from separate inputs together and to produce interactive reports and visualisations.
The reports were created using Power BI Desktop with an end goal of making the reports more readily available for the wider business audience using the Power BI Service platform so as to be able to host the reports in a web browser. In this instance, by storing the three MS Excel source files from an OneDrive location, the automatic refreshing of the reports could also be achieved. Meaning that users would be able to access these reports themselves in a web browser, loaded and refreshed with the very latest data available to them at that moment in time.
Power BI is Microsoft’s self-service Business Intelligence platform which has taken off massively in recent years and ever continues to grow in popularity. It is a space where creativity and analytics collide!
Continue reading
Wouldn’t it be great if we had robots to do everything for us? Okay, maybe not everything but certainly the mundane and the tedious!
Continue reading
Client: One of Ireland’s Top Universities
Year: 2020
As part of this redevelopment project, Olas breathed new life into an older MS Access application that had evolved internally in the university over a large number of years. The Thesis database was a central repository for recording students, examiners and Thesis data that then would be used in reporting on and creating all external typed letters from the department.
A key incentive for this project to get the go-ahead was the need to find a new home for the database as the traditional file-server networked drive that it sat on was now being decommissioned in favour of newer cloud based technology. What was now once a standalone MS Access application was now redesigned as a MS Access front-end application (screens and functions) with the data migrated to a SharePoint Lists back-end setup. This new setup was successfully achieved with no loss to performance or function.
In redeveloping the solution, we gave it a new improved interface, search features, security and reporting to MS Excel and MS Word based mail merge functions to create letters. We firstly began by streamlined the system by removing obsolete elements and to redesign the setup of the system to make it much more usable and maintainable for the users going forward. The other key part of this project was to introduce a new, detailed, custom mail merge facility. The new setup led to the creation of 35 separate MS Word letter templates which then would be called upon by the systems custom mail merge process, which would further populate the more individual elements of the letters. These generated letters could in turn be saved as MS Word document, as PDF’s, as email attachments or even as the body of an email itself. For a single student or examiner record it could be the case that the system could generate a single email with up to five separate generated documents attached. The setup of the letter templates as MS Word documents has meant the letters are extremely easy to update and format as required by the users going forward, or to even add new letters to the catalogue of documents that is populated by the system.
The end deliverable was a hugely successful migration of an old MS Access database with a soon to be made legacy environment setup, to that of a new setup utilising a SharePoint backend. While also providing a much needed modernised look and feel with an extensive mail merge facility.
These days’ laboratory testing forms an essential part of the brewing process; whether it is to drive for in-house quality, to ensure local/international drink standards, or to meet fair trade and regulatory compliance standards. With tests completed on everything from the raw material received, samples taken at various stages of the brewing process, finished batches, sensory tests and on returns/complaints.
Continue reading
In the pharmaceutical industry, Quality Assurance (QA) is essential for ensuring that products are manufactured and delivered to a safe and consistent standard, adhering to all guidelines, standards and stringent regulatory requirements set upon them. A challenge for all Pharmaceuticals is then how to effectively manage the whole Quality Assurance process as smoothly as possible whilst operating across multiple teams and departments; with the end goal of releasing as many batches on time and right first time.
Continue reading
One of the most common requests to our software development department is to deliver a system with bespoke advanced mail merge functionality. This might be to develop a brand new solution or to develop an extension to an existing tool!
A mail merge is an automated process used to create a batch of documents/emails which are personalised for each record/recipient. A process that would otherwise take a huge volume of time to create manually can instead be completed rapidly at the click of a few buttons.
There are of course mail merge functions built into products such as MS Word that will suit for simple mail merge processes but when the requirements become a little bit more detailed or specific, you will find you need to start quickly looking elsewhere for solutions.
Typically, the systems we deliver have a focus on creating mail merge emails with a number of mail merge attached files added to them. These automated solutions are developed using either MS Access or MS Excel (with VBA Coding elements) as the central platform for the tool. MS Word is most often utilised for the creation of the source templates to be used by the tool (These templates may go on to become the body of the email, an attached MS Word doc or an attached pdf).
In the following sample system screen the user has selected a mail merge process that will first create the body of an email, before attaching two mail merge documents, a MS Excel file and MS Word Form. Also attached are two further static attachment files. The mail merge process will be addressed to the employee but their manager will also be cc’d in on it.
Upon clicking the ‘Start Mail Merge Process’ button, the process would cycle through each applicable record in the datasource, creating the emails and adding the attachments automatically.
Once a record is processed, details can then be recorded to a transaction history log.
On an Administration screen a list of all applicable basic Placeholder mail merge fields would be listed along with formatting instructions for those elements. More complicated mail merge features, such as embedding custom charts or tables of data in the document, would be handled separately to this method.
It is these Placeholder fields (encapsulated between the chevrons) that are then available for the user to use in the creation of the mail merge MS Word template files.
To learn how our development can help design and develop a mail merge system tailored to your exact needs, just send an email to development@olas.ie and we will be back in touch promptly to arrange a call.
To find out more about our Application Development teams services, click HERE
Author: Fergal O’Connor, Senior Training Consultant, Application Development Department.