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:
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.