Close Excel after OutpuTo function

[lhanes72]

This is probably easy but it is giving me fits. I have some simple code that transfers an Access table to Excel. Now, I need to know how to close Excel. Obviously the code ActiveWorkbook.Close True doesn't work. I've tried defining the application, workbook and worksheet and tried .Close and .Quit and setting each to Nothing but nothing I seem to try works. My Access, Excel, DAO and Office references are all activated.

Here is the code (as basic as it may be):

Private Sub Form_Load()
DoCmd.OutputTo acOutputTable, "FINALR", acFormatXLSX, "C:\My Documents\WFPLAN.xlsx", True
End Sub

 

[NickHa]

I'm confused by your context. The code snippit creates an XLSX file and starts Excel to open the target file, so why do you want to close Excel at that point? In Excel, ActiveWorkbook.Close depends on your newly created workbook being the active book whilst the macro code is running. Where is your code located - is it in another workbook? You need to have logic in your Excel VBA to scan open workbooks to detect the presence of the one created in Access (bearing in mind that it will be in a different instance of Excel from your code), then do Application.Workbooks("name").Close True from that. The overhead of scanning needs to be considered, so don't do it too frequently.

 

[vbaInet]

If you don't want to close it why open it in the first place?

DoCmd.OutputTo acOutputTable, "FINALR", acFormatXLSX, "C:\My Documents\WFPLAN.xlsx", [COLOR=Red]True[/COLOR]

That is what that parameter does, it opens the spreadsheet once it has been populated. Change it to False or leave it blank.