Export to Excel (1 Viewer)

[Gunnerp245]

2/24

I have read with great interest this thread http://www.access-programmers.co.uk/forums/showthread.php?t=157331&highlight=query+export+excel and with some modification it does what I want to do; Almost.

I am using Access 2003 and this is the code;
------------------------------------------------------
'Export Query To Excel.
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet

Dim strSQL As String
Dim rs As DAO.Recordset

Set xlapp = New Excel.Application
Set wb = xlapp.workbooks.Open("F:\0. MSC\CopyMRTMaster\ExecSumm\ExecSumCopy.xls")
Set ws = wb.worksheets("LastOccurances")

strSQL = "SELECT SHIPS.Hull, SHIPS.Name, Assignment.Assignment, MRT.[End Date] FROM [CLASS MANAGERS] INNER JOIN (Assignment INNER JOIN (SHIPS INNER JOIN MRT ON SHIPS.ID = MRT.SHIPS_ID) ON Assignment.ID = MRT.Assignment_ID) ON [CLASS MANAGERS].CMCODE = SHIPS.CMCODE"

Set rs = CurrentDb.OpenRecordset(strSQL)

ws.range("LastOccurances").copyfromrecordset rs

xlapp.Visible = True
'xlapp.Dialogs(xlDialogSaveAs).Show

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
-----------------------------------------

The code is initated by a command button on a form. The correct data is extracted to the correct excel workbook and worksheet, however when opening the excel file 'after' exporting the data the file reports it is "locked for editing" and will only open as read-only.

** The same error is displayed even after exiting Access. **

Suggestions?

Gunner...

 

[Gunnerp245]

Obiviousily, I had the closing commands in the wrong place initially, not at the end. The excel file closes properly now and opens without error.

Gunnerp245