Excel: Most Recent data won't import after making export updates.

[gg80]

Using Access 2007 and excel 2003. I am trying to export and import data to and from an excel file.

For export I am using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QueryName", CurrentProject.Path & "\" & "ExcelName.xls", -1, "RangeName"

Some calcs are made in Excel, then results plus other data are immediately imported back to Access using:

DoCmd.RunSQL "DELETE * FROM [TableName]"
DoCmd.TransferSpreadsheet acImport, 8, "TableName", Application.CurrentProject.Path & "\" & "ExcelName.xls", True, "SheetName!h150"

This works fine on first use. Then, when change data in Access and export, the results that are returned from Excel are the same as the previous import! I opened the excel file and the correct data had been exported from access and new result was correctly calculated. Yet, it was the previous result that was imported.

I set up a test file with separate export and import buttons, but get same problem.

I find that if I manually open and close the excel file after the export, the new result will then be imported correctly.

Is there some way I can make the import data represent the updated excel file rather than the previous action? Maybe some way to open and close the excel file with vba. I have searched, but can’t find anything.



Thre is a much more complex export method on several websites, one full of “set” expressions and one that transfers cell by cell. It works without this problem, but it is very very slow.

I know that it would be better to do everything in Access, but the user is adamant about doing it this way as he is very familiar with Excel and prefers making certain updates and changes there. Any help much appreciated.

[gg80]

In case someone else is having this problem. Here is reply I got on an excel forum from Hans Vogelaar MVP
When you export data to a closed workbook, formulas that refer to the exported data are NOT updated because it is not the workbook itself that updates formulas, but the Excel application. So when you import the values of the formulas immediately after exporting, you'll retrieve the stored, non-updated values.
To update the formula results, you need to open the workbook in Excel.

Unfortunately, if working with a slow system like this one. it makes things take much much longer than if didn't have to open/close Excel