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.

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTQName, strPath, True, strSheetName

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.

Please help.
Thanks,


LVL 1
sophisticatediowanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
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.
0
Rob HensonFinance AnalystCommented:
Is the data going into a Table?

Silly question, you say that the calculation is set to Auto but is it set to Auto except Tables.

Thanks
Rob H
0
Rory ArchibaldCommented:
FYI, Data Tables are not the same as Tables and it is Data Tables (which are formulas) that are affected by the calc options.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rory ArchibaldCommented:
Never mind - misunderstood your point, I think.
0
Dale FyeOwner, Developing Solutions LLCCommented:
You could put some code in the Workbook_Open event.

rivate Sub Workbook_Open()

    Dim intLoop As Integer
   
    For intLoop = 1 To ActiveWorkbook.Sheets.Count
   
        ActiveWorkbook.Worksheets(intLoop).Calculate
   
    Next
   
    MsgBox "Recalc complete!"

End Sub

Or you could open Excel from Access, open the workbook, and do the same thing, something like:

Public Sub RecalcWorkbook(Workbookname)

    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim sht As Excel.Worksheet, intLoop as integer
   
    Set xl = CreateObject("Excel.application")
    'xl.Visible = True
    'Set wbk = xl.Workbooks.Open(Workbookname, True, False)

    For intLoop = 1 to wbk.sheets.count
        Set sht = wbk.Sheets(intLoop)
        sht.Calculate
    Next

    set sht = nothing
    Set wbk = nothing
    set xl = nothing

End Sub


0
sophisticatediowanAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Are your macros enabled?
0
sophisticatediowanAuthor Commented:
Macros are enabled.
Thanks,
0
sophisticatediowanAuthor Commented:
Oh, and this was tried on different computers.
0
sophisticatediowanAuthor Commented:
One more thing I just noticed.  simple copy and paste of the data transferred will update the calcs too.
0
sophisticatediowanAuthor Commented:
rorya:
I'm a bit confused by your comments.  

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,
0
sophisticatediowanAuthor Commented:
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,
0
Rory ArchibaldCommented:
Confused by which comment?
0
sophisticatediowanAuthor Commented:
"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,
0
Rory ArchibaldCommented:
The "never mind" part was aimed at Rob, rather than you :)

Before we get into automation, if you simply run:

Sub RecalcAll()
Application.CalculateFull
End Sub

does that update the formulas?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sophisticatediowanAuthor Commented:
Hey, sweet.  That updated everything.
I'll just throw that statement into the workbook's open subroutine.

Private Sub Workbook_Open()
Application.CalculateFull
End Sub

Thank you,
Greg L.
0
sophisticatediowanAuthor Commented:
Thanks,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.