Export data from access 365 to an Excel template using VBA gives errors in the existing Excel formulas

[aijoltnieland]

In my old win7 machine I used to transfer data from a CRM program to excel for calculation. This machine is replaced by a win 10 64 Bits machine and Office 365 64 bits. The CRM program is replaced by ACCESS 365 software.
Now the problem: when I transfer data from ACCESS to EXCEL using vba in ACCESS like here below

Private Sub BtnNaarCalc_Click()

Dim objXLApp As Object
Dim objXLBook As Object
Dim r As Object

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("V:\Data\excel\calcgeg\Calculatie ACCESS 202008.xlsm")
objXLApp.Application.Visible = True
objXLBook.Sheets("Gegevens uit Act").Select

With objXLBook.Sheets("Gegevens uit Act")

.Cells(1, 1).Value = Forms!FrmOffNrs.OffNr.Value
.Cells(2, 1).Value = Forms!FrmOffNrs.OffAcqNr.Value
.Cells(3, 1).Value = Forms!FrmOffNrs.Form!SFrmOffvlgNrs!OffDatum.Value
.Cells(4, 1).Value = 0.25 'prijsnivo
.Cells(5, 1).Value = Forms!FrmOffNrs.OffAanvrager.Value
'some 30 more fields are transferred I left these to keep the question simple
End with
objXLApp.Application.Visible = True
objXLBook.Sheets("Optie 1").Select
Set objXLBook = Nothing
Set objXLApp = Nothing
End Sub

The problem is that the formulas in EXCEL are random supplyed by an @ sign


So =ALS(Kabelafstand_kast1>5;J14*(Kabelafstand_kast1+3);Lengte_hellingbaan1)

becomes =@ALS(@Kabelafstand_kast1>5;J14*(@Kabelafstand_kast1+3);Lengte_hellingbaan1)

resulting in an error #Name

How can I prevent these errors

 

[welshgasman]

I would walk through the code with F8 inspecting each variable/field to try and see where that occurs.?

Failing that use a Replace() to get rid of it, providing of course you do not have any 'real' @ in your cells/fields?

 

[dougmarkham]

It maybe of interest to you that from 2020, Excel formulas have changed.
For instance, an index match with named ranges is now:

=@Index(namedrange1,Match(@NamedRangeA,NamedRangeB,0))

New dynamic formulas - @ in my excel formulas?

Implicit Intersection Operator

Therefore, I'm wondering if excel has just amended your formulas to match the new formula format?

Kind regards,

Doug.

 

[aijoltnieland]

Thanks for the reply Doug,
Is there a way that excel files with "older"formulas are translated to the new 2020 state with @'s in it?
Aijolt

 

[dougmarkham]

Thanks for the reply Doug,
Is there a way that excel files with "older"formulas are translated to the new 2020 state with @'s in it?
Aijolt

Hi Aijolt,

Not that I can find.
The problem only effects dynamic tables (so if you only have a filter but no table, then the formulas should be fine.

The only thing I can suggest---and what I would probably do---is to either:
a) Keep an active copy of an earlier version of excel (should you wish to do no work to fix this)
b) Go through each wb and extract the formulas out. Find out where the @'s go. Then use VBA to insert the correct formulas via dynamic reference.

Others may have a better solution!?

So for instance:

Sub IndexMatch()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Worksheets("InputPostCodes").Activate
Range("LookUpTable[POSTAL TOWN]").Formula = "=@INDEX(PostalTown,MATCH(@PostCodeFirstHalfNew,PostCode,0))"
Range("LookUpTable[POSTAL TOWN]").Copy
Range("LookUpTable[POSTAL TOWN]").PasteSpecial xlPasteValues
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
    
End Sub

Here, the header is POSTAL TOWN in Range("LookUpTable[POSTAL TOWN]").Formula.

Sort of a ball ache, but the auto-change thing only kicks in when you insert a new formula.
All existing formulas just give the #SPILL error.

In my models, I have taken to always coding formulas in via VBA:
1) So if someone accidentally deletes or corrupt the formulas, they can click a button to return them
2) Now, if the formulas need changing, I can alter the VBA and it's sorted.

Kind regards,

Doug.

 

[aijoltnieland]

Doug thank you for the effort to help me.
It appeared to be an effortless exercize, for most of the programming was already done in the Excel 97 period, so automatic convertion was no deal!
I have brought back the calculation to its roots and from there I have build the file up again, and now its working perfectly.
Thanks again for the effort.
Aijolt