excel export-import

[gg80]

I am using Access 2007 to export and import data to excel 2003. I can successfully export to excel using the TransferSpreadsheet command. I fthen ollow the export command with a TransferSpreadsheet command to import results of new data. The problem is, that I can't get the new calculation result from excel unless I manually open and close the spreadsheet after the export. It only returns the result from the previous export. I have tried some methods found on the web, but they don't work. Any help much appreciated. Going bats.

[June7]

Do you have code to save the workbook after export?

[gg80]

Do you have code to save the workbook after export?

Thanks much for the quick reply.

I tried some things, but didn't work. When on another project, I exported to specific cells with code more complex than the transfer method. There, I didn't have the problem. But, admittedly, I didn't create the code. Can you show me how to do this while using the TransferSpreadsheet method?

In case it matters, this is the code that I have in the Excel file to prevent getting the "do you want to save etc" msg

Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False
'ThisWorkbook.Saved = False
Application.DisplayAlerts = True
Application.Quit
End Sub

[June7]

I am confused. How do you use TransferSpreadsheet to export to xlsm workbook and then do calcs on worksheet on that data?

Why do you need to do calcs in Excel?

If you want to provide files for analysis follow instructions at bottom of my post.

[gg80]

The person generating results likes to do his rather complex calcs in excel. He is familiar with excel so it allows him to make changes, updates etc. easily. Excel however is not good for people using the data. The Access file does other things related to his calcs as well and provides users an easy way to enter data, get results, print reports etc. I have done this once before, but used a more complicated way to transfer data to certain cells in excel, not the transferspreadsheet command.

I am having trouble attaching files. This may be a duplicate

[gg80]

I goofed on excel file. Please delete the excel sheet Sheet_DBmod2018. It has confidential calcs.

[June7]

There is no workbook. Can't analyze issue.

[gg80]

I will try again. Attached is an Access file and an excel file showing what my problem is. I need some way to open and close the excel file with VBA after exporting data. Appreciate your doing this.

[June7]

According to MS, using the Range argument for export with TransferSpreadsheet should cause failure but apparently it does not. Something must have changed in Excel or that guidance has always been bad. Also, HasFieldNames argument should be -1 or 0 (True/False) - if left blank it defaults to false - and has no relevance to export.

Procedure exports to workbook then immediately imports from the same workbook just 1 cell. I think would not get new calc because workbook is not opened. Workbook only recalcs when it is opened. FollowHyperlink can easily open the workbook but then closing it is not so simple. So instead of FollowHyperlink, use Excel automation code between the export and import:

Dim xl As Excel.Application, wb As Excel.Workbook
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(CurrentProject.Path & "\" & Me.FileName)
wb.Close True
xl.Quit

Code in workbook not needed.

[gg80]

This again may be a duplicate. I marked it as solved and my reply disappeared. Just in case didn't get my reply, just want to say thanks so much. I looked all over the web and couldn't find this answer. Works great.

Minor thing in case someone else uses it, \ not shown in Set wb expression. Thanks again.

[June7]

Glad it's working. Corrected my post to show \.