sophisticatediowan
asked on
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, acSpreadsheetTypeExcel12Xm
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 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,
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, acSpreadsheetTypeExcel12Xm
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 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,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FYI, Data Tables are not the same as Tables and it is Data Tables (which are formulas) that are affected by the calc options.
Never mind - misunderstood your point, I think.
You could put some code in the Workbook_Open event.
rivate Sub Workbook_Open()
Dim intLoop As Integer
For intLoop = 1 To ActiveWorkbook.Sheets.Coun
ActiveWorkbook.Worksheets(
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(Workbooknam
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet, intLoop as integer
Set xl = CreateObject("Excel.applic
'xl.Visible = True
'Set wbk = xl.Workbooks.Open(Workbook
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
rivate Sub Workbook_Open()
Dim intLoop As Integer
For intLoop = 1 To ActiveWorkbook.Sheets.Coun
ActiveWorkbook.Worksheets(
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(Workbooknam
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet, intLoop as integer
Set xl = CreateObject("Excel.applic
'xl.Visible = True
'Set wbk = xl.Workbooks.Open(Workbook
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
ASKER
Fyed,
Manual calculation does not work.
"calculate now" does not work.
ActiveWorkbook.Worksheets(
How strange.
Manual calculation does not work.
"calculate now" does not work.
ActiveWorkbook.Worksheets(
How strange.
Are your macros enabled?
ASKER
Macros are enabled.
Thanks,
Thanks,
ASKER
Oh, and this was tried on different computers.
ASKER
One more thing I just noticed. simple copy and paste of the data transferred will update the calcs too.
ASKER
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,
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,
ASKER
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,
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,
Confused by which comment?
ASKER
"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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'll just throw that statement into the workbook's open subroutine.
Private Sub Workbook_Open()
Application.CalculateFull
End Sub
Thank you,
Greg L.
ASKER
Thanks,
Silly question, you say that the calculation is set to Auto but is it set to Auto except Tables.
Thanks
Rob H