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

aijoltnieland

New Member
Joined
Jul 3, 2018
Messages
4
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880
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

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

aijoltnieland

New Member
Joined
Jul 3, 2018
Messages
4
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

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Jul 3, 2018
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,966
Messages
5,621,874
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top