To export query from Access to Excel in Read/Write mode in VBA

Question

Below is the code which exports the query named 'LatestSNR' from Access to Excel;

Public Sub Expdata()
    Dim rst As DAO.Recordset
    Dim Apxl As Object
    Dim xlWBk, xlWSh As Object
    Dim PathEx As String
    Dim fld As DAO.Field

    PathEx = Forms("Export").Text14 'path comes from the directory given in form
    Set Apxl = CreateObject("Excel.Application")
    Set rst = CurrentDb.OpenRecordset("LatestSNR")
    Set xlWBk = Apxl.Workbooks.Open(PathEx)
    'xlWBk.ChangeFileAccess xlReadWrite
    Set xlWBk = Workbook("PathEx")
    Apxl.Visible = True

    Set xlWSh = xlWBk.Worksheets("Metadatasheet")

    xlWSh.Activate
    xlWSh.Range("A2").Select

    For Each fld In rst.Fields
        Apxl.ActiveCell = fld.Name
        Apxl.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select

    ' selects all of the cells
    Apxl.ActiveSheet.Cells.Select

    ' selects the first cell to unselect all cells
    xlWSh.Range("A2").Select
    rst.Close
    Set rst = Nothing

   ' Quit excel
    Apxl.Quit


End Sub

After the execution of code, the query is transferred to excel sheet and is viewed in 'Read only' mode. If I try to save it, a copy of the excel file is produced. Can the Excel be opened in Read/Write mode ? so as to save the workbook and also to transfer the query to same workbook repeatedly.

If in case the change of mode is not possible, then is there any alternative method? — SriLaks, Mar 05 '15 at 21:32

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Normally, your exported Excel file should not be in read-only mode. Somewhere in your processing you are not ending the Excel instance properly. A good way to check is in your Task Manager under Processes and see if the EXCEL.EXE remains. If so, end the process and make the following code adjustments:

First, properly close and save your Excel file. Quit requires a save or not save confirmation of workbook since it means to Close the Excel application you initialized, not the target workbook. So add before Apxl.Quit:

xlWBk.Close True

Second, properly uninitialize your Excel objects to free computer resources. This is not required but simply good coding practice:

Set Apxl = Nothing
Set xlWBk = Nothing
Set xlWSh = Nothing

By the way, unless you do nuanced coding and formatting, there is a built-in Access to Excel function that exports field names and recordset together: DoCmd.TransferSpreadsheet.

Thanks for the valuable info! – SriLaks Mar 06 '15 at 09:28 — SriLaks, Mar 06 '15 at 09:28

To export query from Access to Excel in Read/Write mode in VBA

1 Answers1