Copy

Newsletter by jkp-ads.com

View this email in your browser

jkp-ads.com Newsletter for June 2016

Handling problems with the ABAP2XLSX library

This month I've been working on a project which involved Excel templates containing some hefty VBA code and many cell styles and named ranges which were being processed by the ABAP2XLSX library. These templates date back as far as 9 years, when I first laid their ground work (and yes, they have been functioning just fine thank you!) but now the company implemented some changes. One of them being the prepopulating of data in those files using ABAP2XLSX. And lots of things broke because of that.
Working through the list of issues I discovered some shortcomings of this tool which I'd like to share with you. Pretty handy in case you ever encounter spreadsheets touched by this library.

Styles are not retained

Sadly, the tool is incapable of saving cell styles. So if your template is well designed (like mine is) using cell styles, you are hosed as those are not supported. I ended up writing all cell styles to a worksheet, listing all their properties in a table and reading that worksheet back to redefine the styles.
Unfortunately, given that Cell styles are removed, so is the style information from the cells, so you need a way to reapply the styles. Luckily, this template makes use of an intricate system of range names so I used a table of range names with -next to it- the cell style of each name so I could run through that. Any unnamed cell looses its style information.

Problem inserting a worksheet

Our templates had a problem, being opened for the first time after "editing" by ABAP2XLSX, we were unable to insert any worksheets into them. This issue could be resolved by opening the file, saving it from Excel, closing it and subsequently opening it again. I wrote a bit of VBA code to handle that process.

Textboxes not supported

The tool also zaps all your textboxes (don't know about other shapes, but...). I reverted to replacing those with text in cells. Just because that was the simplest solution in our case.

Local range names not supported

The tool also does not support range names local to worksheets, these are simply removed. The only solution we had was to make sure all range names are global (workbook scope).

The Excel VBA Masterclass

This in-house training covering advanced VBA subjects such as Error handling, Userform design, Class modules and etcetera has been opened for registration. So if you would like to learn VBA from the master, make sure you register soon!

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.
 

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility
.
 

Nederlandse cursus: Excel VBA voor Financials

Kost uw maandrapportage u vele uren saai en repeterend werk? Dan is het de hoogste tijd voor onze cursus Excel VBA voor Financials!.
De Volgende cursus wordt gegeven op 1, 8 en 15 juni 2016
 
Copyright © 2016 JKP Application Development Services, All rights reserved.


unsubscribe from this list    update subscription preferences 

Email Marketing Powered by MailChimp