author: Jonathan Stuckey
A common blocker for organisations migrating content between platforms is the dependency on Excel. It is used as a core business engine in many areas of any organisation and the range of issues you can encounter with migration, that limit the end-users ability to operate with the files once migrated, is seemingly extensive and difficult to deal with.
I deal with migrations a lot and this is a recurring theme, so I've decided to address a couple of the higher-profile issues in these articles.
This article covers the background to the issues encountered when Migrating Excel files between repositories (or to put it another way, moving them into SharePoint Online). In next article I will go into detail on a couple of the most frequently encountered issues, and how they can be fixed.
Why is Excel so difficult to address in migrations?
Excel is a has been a critical application for nearly 40 years, because it put managing data and financials in the hands everyone using a computer: from school-kids working on homework, to families managing house-hold budgets, through to highly skilled data-scientists performing modelling and insights over large data-sets.
Excel is pervasive and embedded in nearly every area of computing work. And that's the problem...
Why Excel is a core application for business
It boils down to investment and access because of:
Versatility: Excel can handle a wide range of tasks, from simple data entry to complex financial modeling, up to statistical modelling and beyond.
Ease of Use: Its user-friendly interface allows for quick data manipulation and visualization. Microsoft kept the UI experience consistent on Excel for decades. Enhancing, extending and simplifying access to the range of capability each release.
Familiarity: Most business professionals are familiar with Excel, making it a go-to tool for various tasks for: finances, reporting, project planning, task management, data-analysis and so on.
Integration: Excel integrates well with: Microsoft Office applications; third-party tools, external data-sources, and application services. In fact, anywhere you can layout data in rows & columns, Excel can connect to pull-in data from, or with Data platforms and Microsoft 365 push data to, easily.
Data Analysis: Excel offers robust data analysis features, including pivot tables, charts, formulas, data management and de-duplication, external lookup, pattern referencing, ranking | trends | outliers ...and on, and on.
Shouldn't it be easier to migrate Excel files then?
Well, no. One of the biggest investments organisations make is capturing and streamlining business processes, calculators, or important logic. Automating the more challenging data-centric tasks. Often this is using Excel, because Excel is just part of the Office suite of applications we've (usually) paid for, and because - Excel is really good at what it does.
People training on using Excel find it powerful and flexible - and start to create (capture) these business processes and logic using a combination of: functional logic (formulas), automation of processing steps (macros), and increasingly programmatically (using languages like Python).
The real issue is because Excel has been around for so long, we no longer train people on it, we rely on people's prior exposure and willingness to dig-in to understand it for themselves.
The challenge in business is once you've captured a process, calculator, report or other automation in Excel - if you don't document it well, you are reliant on the creator (let's call them "Bill" for now) until they leave ...and then you are stuck.
The technical migration issues (roadblocks)
There are the usual challenges with migrating Excel files, like poor data quality or file-path length limitations, bad characters in naming and so on, but these are incidental and should be addressed as part of a standard migration practices - if you know what you are doing.
Specific technical blockers to migrating Excel files typically resolve to one of the following:
Deprecated formula functions, commands and APIs
Linked spreadsheets and files
External (data source) dependencies
Embedded macros
#1 - Deprecated functions and service blocking
With Excel files the more challenging situations usually stem from legacy investments. I.e. Continual updates to file-formats, versioning, legacy models / API's / languages (VB, C/C#) etc.
For a list of deprecated formula functions Microsoft reports these, but not in a particularly useful manner: Microsoft | Support - Changes made to Excel Functions
Microsoft have been managing security from malicious code for over a decade, with Microsoft | Support - Blocking Macros from internet in Office and you will need to manage a range of Policies and Trust relationships for documents opened from online.
What makes it more challenging when migrating content to SharePoint Online, is Microsoft have deprecated (legacy) macro execution and actively block running code in Excel files stored in SPO sites. In addition Microsoft has locked down access on SPO sites for scripting and executing code, unless using new (more secure models)
For files which have these kinds of issues, you are looking at rebuilding your business logic or potentially planning for newer tools. Either way its not just about moving your files.
#2 - Linked Files and Data connections
Opening an Excel file to access data or run the embedded logic will first check for linked data connections, with to a another file, application, database, service or even web source.
If you've moved the files - then the links to the external data-source breaks. This is because Excel manages these internal references to original or alternative locations using a fully qualified path name, and for data connections to apps and data-services, often with stored access credentials.
Managing updates to these one-at-a-time can be laborious, time-consuming, difficult and sometimes impossible to resolve if the original information was not migrated, or access is no longer possible...
#3 - Embedded Macros and programming logic
I've put this to the bottom because, well it would take too long to address within anything like justice to what's involved. Needless to say support for Macro logic goes back to XLM in Excel v4 (1992), but most of today's migration problems are an evolution of the VBA programming model which was popularised end of the 1990's and became a cause-celeb in Excel 2003.
..now the likely-hood you will find things with XLM macros is low, but it was still supported running them until 2022 in old documents. And this is why your migration's fail. Because there is little incentive to move on.
With changes in technology, increased focus on code security, access and privacy Microsoft is becoming more and more focused on shutting down these older models as they allow for range of security holes. At this time (2024) XLM is not supported, but VBA and other older options for embedding, are when running in the desktop application - although Microsoft is locking down the ability to run in the cloud, and limit various calls and methods available.
Like I said, to big for this article. Another time maybe, but I will need a lot to drink before I attempt it.
Surely, someone has done this before... at scale?
Yes, because migrations happen all the time. It is often more useful though, to use available tools with reporting functions to size your problem first, work-out what are critical items only which need updating then decide how much risk you are willing to bare vs. cost to fix things.
That leaves you the options of:
Update your embedded file linking
Rebuild your logic, calls & API references
Rewrite the macros in newer tooling / language
Replace your tool | calculator | logic with a modern app
Somethings can be directly addressed by a power-user or non-development literate users, like updating linked file references or SharePoint URL. Other's will need more effort. For the rest - all the files you ignore - have a plan how to address them when and if you ever need to recover or re-run something from an old file for a user.
The next article addresses some of the practical actions, assessment on approach and the steps necessary to fix some of these issues identified.
Afterword
When most organisations complain they don't have time, or the resources, to take on addressing these issues its usually due to lack of understanding behind the problem, how to address it, or through poorly defined business value in fixing it.
Most often its because for years the organisation has had a 'make-do and mend' approach to investment - and now someone needs to add-up all the work-a-rounds and duct-taped apps which are hidden (embedded) in your spreadsheets. Organisations have hidden their dependencies on bright-people for decades, and when desktop tools like Access were phased-out, the power of modelling and presentation over structured data accelerated its move into the Excel application.
Some day v soon you will have to move because no one, and no services will be able to support your 20+yr old lash-up, at that point you either down-tools and fix things or go out of business.
And before you think that kicking the can down the road for AI to fix the issues will happen, ask yourself - will it really fix the issue if you dont know what you are asking it to do, or why?
About the author: Jonathan Stuckey
Comentários