Fouten in de Business Central Excel-invoegtoepassing: 3 snelle oplossingen

By Thomas Werkhoven, CEO of Exsion365, registered accountant, previously group controller and CFO.

A controller opens the Monday report, presses refresh, and Excel says "Automation error". Another colleague's Exsion ribbon has vanished overnight. A third opened the same report on an English laptop and got a "#VALUE!" in the date cell that has worked for two years. All three tickets landed with the same application manager in one week, and none of them was caused by the add-in. Here are the three errors, what actually causes them, and the fixes, most of which take less than a minute.

The short answer

"Automation error" on refresh usually means the add-in's connection to Business Central did not come up cleanly when Excel started; open the connection dialog, save it without changing anything, and refresh again.

A vanished ribbon is a COM add-in that got unticked, usually after an Office or Intune update; restart Excel, and if it is still gone, tick it again under COM Add-ins.

A date cell that breaks between a Dutch and an English Excel is Excel's TEXT function, whose format codes are language dependent ("uu" means hours in Dutch, "HH" in English); replace the format string with a formula that does not depend on the display language. If none of the three fixes works, that is when you call your reporting vendor, and the best thing you can do for them is call while it is happening.

Error 1: "Automation error" when refreshing a report

Symptom. A user opens a workbook that has worked for months, presses refresh, and gets a generic Excel automation error. It is not tied to a specific report; the customer in our session saw it on light reports (a goods receipt overview) as often as on heavy ones.

Cause. When Excel starts, it loads its add-ins and each add-in initialises its own connection. If that connection does not come up cleanly, for example because a background Excel process is still holding the previous one, the next refresh fails with a generic Excel automation error rather than a Business Central error. There is no error number to look up; the message is Excel's, not the add-in's. Nicky Oudt, Senior Reporting Consultant at Exsion365, sees the same thing occasionally on his own laptop.

Fix, 30 seconds. Open the Exsion connection dialog from the ribbon, click the connection you use, and save it. You do not have to change anything; saving re-initialises the connection. Refresh the report again. In our experience that resolves the large majority of automation errors on first try. If the same user gets the error repeatedly on the same day, close Excel completely and read the next section before restarting.

One thing worth ruling out before you go further: if the error coincides with a morning when the whole team is refreshing at once, you may be looking at a busy tenant rather than a broken connection, and the symptoms overlap. We set out how to tell the difference in Business Central Excel reports slow on Monday morning.

The background Excel process

Excel does not always close when its window does. After a crash, or when an add-in or a linked application keeps a handle open, one or more EXCEL.EXE processes stay running in the background. A fresh Excel window then starts next to them, and add-ins that expect to be the only instance behave unpredictably: connections that were fine a minute ago fail with an automation error.

Before filing a ticket, open Task Manager, look for Excel under Background processes, end every instance, and start Excel again. Then re-save the connection as above. On managed laptops this is worth putting in the first-line checklist, because it is invisible to the user and takes IT ten seconds to spot.

Error 2: the add-in disappears from some users' Excel

Symptom. A user starts Excel and the Exsion tab is gone. It is not all users, and it is not every day. The customer in our session deploys the add-in through Intune and sees this on a handful of machines at a time, typically after an update has run overnight.

Cause. The Exsion Excel add-in is a COM add-in. Excel keeps a list of COM add-ins with a checkbox per add-in, and there are two well-known ways for that box to become unticked without anyone touching it. First, Excel itself disables an add-in it thinks caused a slow start or a crash, and moves it to the Disabled Items list. Second, an Office update or an Intune-pushed change to the Office installation resets the add-in registration, and Excel then reports that it cannot find the add-in. Both are Office behaviour; the add-in files are still on disk.

Fix, one minute. Close Excel and start it again; in most cases the add-in loads on the second start, which is what the customer reported too. If the tab is still missing, go to File, Options, Add-ins, choose COM Add-ins in the Manage list, click Go, and tick the Exsion add-in. If it is not in the list, choose Disabled Items in the same Manage list, enable it there, and go back to tick it. Microsoft documents the dialog in View, manage, and install add-ins for Excel.

If it keeps happening on the same machines. Check which Intune or Office update ran the night before, and whether those machines are on a different update ring from the ones that stayed healthy. Nicky works on an Intune-managed laptop himself and recognised the pattern immediately: an update passes through, and the next morning something in Office needs a nudge. Aligning the add-in deployment with the Office update ring is the durable fix; that is an Intune conversation, not an Excel one, and Exsion builds the deployment package per customer, so ask for the current one when you change rings.

Error 3: the report works in Dutch Excel and fails in English Excel

This one is our favourite, because it looks like a data problem and is nothing of the kind.

Symptom. A user copies a report to OneDrive, opens it there, and gets a "wrong filter type" or "#VALUE!" error around the posting date filter. The same file opens fine for a colleague. When the application manager looked closer, the colleague who had no problem was running Excel in English; the user with the error had switched to English recently as well, and other users were on Dutch, German or Polish. The company runs all four.

Cause. The report has a "last refreshed" cell near the top, in this case B3, with a formula like TEXT(NOW();"dd-mm-jjjj uu:mm"). That formula is correct in Dutch Excel, where "jjjj" is the year and "uu" is hours. Open the same workbook in English Excel and the format codes are read as English: "uu" means nothing, "jjjj" means nothing, and the cell returns an error or a wrong value. Any filter that references that cell then fails, and the error message points at the filter rather than at B3.

Excel's TEXT function format codes follow the display language of the Excel that opens the file, not the language of the person who wrote it. Microsoft's TEXT function reference lists the English codes, and every other language has its own set.

Fix, two minutes. Stop building the timestamp with a language-dependent format string. Two options work in every language.

• Put NOW() in the cell on its own and apply a cell number format (right-click, Format Cells, Custom). Cell number formats are stored in a language-neutral form, so a custom format set in Dutch Excel displays correctly in English Excel.

• If the cell has to be text, for example because it is concatenated into a title, assemble it from language-neutral functions such as DAY, MONTH, YEAR, HOUR and MINUTE. It is uglier, and it works on a Polish laptop.

Why a copy on OneDrive made it worse

The OneDrive copy was a red herring, but a useful one. The user had copied the workbook from the shared location to a personal OneDrive folder and opened it from there. That is the moment the file left the environment where it was built and tested, and where the application team could see it. The date formula was the real fault, but the copy is what turned a known-good report into a mystery ticket. Which leads to the one habit that prevents most of these tickets in the first place.

The habit that prevents most tickets

The customer in our session has one rule for its 100-plus users: every report lives as a read-only original in one shared folder on a conventional file server, and every user starts from that original. If something goes wrong with their copy, they do not repair it; they go back to the original. The application manager was candid about why: without that rule, one accidentally overwritten formula in a definition turns into a rain of tickets, and the person who broke it rarely knows they did.

It is a low-tech rule and it works. Two refinements make it stronger. Keep the definition (which tables, which fields, which filters) in the workbook where a reviewer can read it, so a broken copy is diagnosed by comparing it with the original; we wrote about that in a Business Central Excel report should explain itself. And do as much of the logic as possible inside the query rather than in worksheet formulas, so there is less for a user to overwrite by accident; the trade-off between an expression in the query and an XLOOKUP on the sheet is the subject of item attributes in Excel from Business Central.

When it is the add-in after all

Sometimes none of the three fixes works. The connection re-saves and the automation error returns; the add-in is ticked and still does not load; the formula is language neutral and the filter still fails. That is a support case, and two things make it a short one.

First, call while it is happening. An automation error is a generic message, and a screenshot of it an hour later tells support almost nothing. A screen share at the moment of the error shows which report, which query, which step, and whether there are three EXCEL.EXE processes in the background. Nicky's request to the customer was exactly that: when it happens again, ring immediately, so someone can watch.

Second, send the version numbers. Exsion add-in version, Excel build and channel, and whether the machine is Intune managed. Half of the diagnosis is knowing which update ring the laptop is on.

If you run Exsion Reporting, support is part of the packages; the pricing page shows what each tier includes. If you run another Business Central Excel tool, the first two fixes and the date formula apply to you just the same; they are Excel, not Exsion.

Frequently asked questions

[[FAQ_START]]

What causes an "Automation error" when refreshing a Business Central report in Excel? | Usually Excel started before the add-in finished establishing its connection, or a stale connection was left from a previous session. Open the connection dialog, save it without changing it, and refresh again.

Why did my Business Central Excel add-in disappear from the ribbon? | The COM add-in was disabled, most often after an Office or Intune update. Restart Excel first. If the tab is still missing, go to File, Options, Add-ins, manage COM Add-ins and tick it, or enable it under Disabled Items.

Why does my Excel report work in Dutch Excel but fail in English Excel? | Excel's TEXT function uses language-dependent format codes, so a Dutch format string is invalid in English Excel. Replace it with NOW() plus a custom cell number format, or assemble the timestamp from DAY, MONTH, YEAR, HOUR and MINUTE.

Does copying a Business Central Excel report to OneDrive break it? | Not by itself. Copies break because they drift from the tested original and because language or setting differences surface there first. Keep one read-only original in a shared location and have users start from it.

Why are there several Excel processes running in the background? | After a crash, or when an add-in or linked application keeps a handle open, EXCEL.EXE can stay running without a window. End all Excel processes in Task Manager before restarting.

What should I send to support for a Business Central Excel add-in error? | The add-in version, the Excel build and update channel, whether the laptop is managed by Intune, and ideally a screen share while the error is happening rather than a screenshot afterwards.

[[FAQ_END]]