• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

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,


0
sophisticatediowan
Asked:
sophisticatediowan
  • 9
  • 5
  • 2
  • +1
2 Solutions
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
Never mind - misunderstood your point, I think.
0
 
Dale FyeCommented:
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 FyeCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

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.

  • 9
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now