Excel VBA programming and Word template creation for Montblanc Deutschland GmbH

Development of an Excel tool and creation of Word templates for Excel pro forma invoices as PDFs

In spring 2024, Montblanc Deutschland GmbH (Hamburg) commissioned eWorks, a Frankfurt-based Microsoft partner and VBA service provider, to professionalize, optimise and expand an existing Excel profile tool. The Excel tool had developed steadily over the years and was in use, but had reached its technical limits.

The technical starting point was an existing Excel pro forma invoice generator that was already generating PDF documents using Word templates, but needed to become more stable, maintainable and functional so that it could once again meet the requirements of day-to-day business. On a day-to-day basis, it became clear that product data and prices had to be reliably up to date, that master data changes such as locations, templates and tax rates had to be consistently taken into account and that operation, maintainability and performance no longer met the requirements as soon as regular changes were made and several variants had to be reliably covered.

As a first major step, the Excel workbook was restructured, inputs, outputs and master data were cleanly separated and the calculation core was centralized to make maintenance easier, eliminate typical sources of error and make processing noticeably smoother. Terminology and user guidance, such as the differentiation between document <-> order confirmation <-> delivery bill, have been deliberately retained, but the interface has been tidied up at the same time - so that new users can find their way around more quickly, while experienced Excel specialists can continue to get to grips with it. An Excel form was created to facilitate future changes to product data, location data and other master and transaction data so that Montblanc can maintain these independently during operation - in future without the need for Excel programming.

In the next step, the Word programming was consistently updated so that the Excel VBA programming reliably generates the PDF output via Word invoice templates and delivery note templates and cleanly maps the corporate design. To do this, the Word specialists had to create new Word templates that consistently display prices and costs, including the VAT calculation as well as headers and footers, and do not turn changes into a template puzzle every time. In addition, country-specific Word templates were created and stored in order to map foreign locations and also take into account other languages, VAT rates and currencies. This process is completely transparent for users, without them having to become Word experts: press a button - and the pro forma invoice PDF is ready!

A special technical component concerns the way in which the Word templates are provided in the Excel tool: The Word templates are stored as Base64-encoded content in the workbook, reconstructed as temporary .docx files if required, opened in Microsoft Word, filled with Excel data via bookmarks and then output as a PDF; this means that the templates do not have to be distributed and maintained as loose files during deployment, but are included in the tool in a controlled manner. The VBA programmers took care of all aspects, such as the product database, Excel calculation logic, Word document creation, PDF generation, input and output, right through to deployment, in close cooperation with the client, so that the tool could be used reliably by the intended user group in the branches. During the ongoing process, the creation is started via a central "Print" button and a progress bar shows the status during processing. The Excel tool was then enhanced to use the current data maintained, adjust headers and footers as required and use current prices consistently so that cost calculations are traceable and correct; the special "admin mode" with password ensures that master data is not changed inadvertently.

After commissioning, Montblanc uses a compact Microsoft Excel tool on a day-to-day basis, which exists independently of the heavyweight ERP system, can be expanded and modified with agility and offers the team a responsive, pleasant user interface with the familiar look and feel of Microsoft Office. The eWorks Excel programmers accompany the client during operation as an "extended workbench", provide support with bug fixes and error corrections, take on change requests, project extensions and implement them quickly so that the tool continues to mature through real use instead of becoming obsolete after the project is completed. Such references also fundamentally show why small Microsoft Office tools are often a pragmatic addition to cumbersome standard software in the corporate environment, because processes can often be mapped faster and closer to the specialist area with Microsoft Excel, Microsoft Word, Outlook, PowerPoint or Access and VBA, Office-based solutions are often accepted on site as a "tool" and, depending on requirements, a solution can function both as a temporary solution and as a permanent "permasorium", while at the same time serving as a prototype that practically tests feasibility, usability and process logic for later implementation in the core system.

Would you also like to digitize processes and are looking for a VBA developer? Would you like to program Excel, have Word templates created or do you need other job programming for Microsoft Word, Excel, Access, PowerPoint or Outlook with VBA or Microsoft .NET and C#? We would be happy to advise you and look forward to hearing from you!