CODEWITHSUNDEEP
excelms-access
user62753
user62753

Reputation: 35

Output custom query to Excel format in MS Access

I know there is DoCmd.TransferSpreadsheet acExport, but this requires a hard query name.

I am trying to loop over a recordset and exporting a file per view, so for example exporting an Excel file for "select * from myQuery where arguments=arg1", another file for "select * from myQuery where arguments=arg2", and so on.

Is there a way to create such an Excel file based on "custom" on the fly SQL query like this?

Upvotes: 0

Views: 73

Answers (1)

Parfait
Parfait

Reputation: 107652

Use CopyFromRecordset which essentially dumps VBA recordsets to Excel worksheet range (referencing only the the upper left corner cell). Below is a subroutine using Access VBA:

Public Sub acToxlRecordsets()

    Dim xlApp As Object, xlwkb As Object
    Dim db As Database
    Dim rst As Recordset
    Dim args As Collection, arg As Variant
    Dim strPath As String, strSQL As String
    Dim i As Integer
    Dim fld As Field

    ' INITIALIZE OBJECTS 
    Set db = CurrentDb()
    Set xlApp = CreateObject("Excel.Application")

    args.Add ("arg1")
    args.Add ("arg2")
    args.Add ("arg3")

    strPath = "C:\Path\To\Excel\Files"

    i = 1
    For Each arg In args
        ' CREATE NEW WORKBOOK
        Set xlwkb = xlApp.Workbooks.Open(strPath & "\ExcelFile" & i)

        ' OPEN NEW RECORDSET
        strSQL = "select * from myQuery where arguments = " & arg
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
        rst.MoveFirst

        ' OUTPUT RECORDSET
        ' FIRST COLUMNS
        xlwkb.Worksheets(1).Activate
        xlwkb.Worksheets(1).Range("A1").Select

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

        ' NEXT ROWS
        xlwkb.Worksheets(1).Range("A2").CopyFromRecordset rst
        xlwkb.Worksheets(1).Range("A1").Select

        ' SAVE AND CLOSE EXCEL WORKBOOK
        xlwkb.Close True

        i = i + 1
    Next arg

    ' UNINITIALIZE OBJECTS
    rst.Close
    Set xlwkb = Nothing
    Set xlApp = Nothing
    Set rst = Nothing
    Set db = Nothing

End Sub

Upvotes: 1

Related Questions