Excel: calculations are not automatically updating after running transferspreadsheet method from Access

I don’t understand why, so I am looking for an answer as to why and a more elegant/correct way of fixing the problem than what I had done.

Using Access 2007 and Excel 2007
Excel file type .xlsm
Recalculate is set to auto in excel

I have data in an access 2007 DB. The data that I am having trouble with is number:double type. The data is transposed from columns to rows using a SQL statement the data is then used to create an access table (make table query) . That data is then transferred to an excel workbook using the transfer spreadsheet method. This all works great. The data types of the created table are number:double.

In the statement above, the strSheetName is actually a named range; it is not the name of a worksheet.

So when I open up the excel file, the formulas that reference the cells that contained the transferred data, still display values from the previous data that was replaced.
Example of the formulas that are not updating:

=IF(COUNTBLANK(Data!J2)=0,Data!J2,"")

Data!J2 is a cell that contains data that was transferred.

So here’s the funny thing: If I click inside the cell that contains the formula as though if I were going to edit it , then click out or press enter, the calculation updates.
Also, if I copy the data that was transferred, then paste it over itself as values, the calculations update.

My workaround to this problem is to copy the named range that was transferred from access and then paste it as values.

If you update the data while the workbook is closed, then Excel is unaware that it needs to recalculate the formulas. You could try either automating Excel to populate the workbook, or add some code to open the workbook and calculate the range explicitly.

Fyed,
Manual calculation does not work.
"calculate now" does not work.
ActiveWorkbook.Worksheets(intLoop).Calculate did not work work either. from w/ in access nor excel.
How strange.

robhenson:
the data is going into a worksheet, cells a1 to z302 (named range = DataRange). What do you mean to auto accept tables? How would I check to answer your question?
Thanks,

Range("DataRange").Copy ("DataRange")
This statement running when the spreadsheet opens does the trick of correcting the problem, but I would like to know why it acting this way.
thanks,

"FYI, Data Tables are not the same as Tables and it is Data Tables (which are formulas) that are affected by the calc options. " and "Never mind - misunderstood your point, I think. "
Am I suposed to nevermind the quoted comment or all of your comments?

"If you update the data while the workbook is closed, then Excel is unaware that it needs to recalculate the formulas. You could try either automating Excel to populate the workbook, or add some code to open the workbook and calculate the range explicitly. "
Adding code to recalculate the ranges does not work. I don't understand why recalculating once the workbook is open does not work. What do you mean by automating Excel to populate the workbook? Automate what in excel?
Sorry for my confusion,
Thanks,

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.