Access Push data to Excel

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In Access VBA I want to push data to Excel using a SQL statement then open the Excel file and sort by one of the columns.
What code would I use?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
How about performing the sorting on Access and then just push the sorted data ?
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi John. I specifically wanted to do it in Excel as there are a few other things that I want to also do in Excel
Software & Systems Engineer
Commented:
If you want to do it on excel you should use Excel Automation...load the Excel Object...push the data (recordset) and then apply the manipulations you want.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks John
John TsioumprisSoftware & Systems Engineer

Commented:
Here is some code :
Public Sub ExportToExcel(frm As Form)
Dim rst As dao.Recordset
    Const StartRow = 3
    Dim MaxRow As Integer
    Dim objXL As Object
    Dim objWkb As Object
    Dim objSht As Object
    Dim FieldNames() As String
    Dim X As Integer
    Dim strSQL As String
    Dim strFields As String
    Dim ctl As Control
    Dim ExcelPath as String
    
    
    
    Set rst = frm.RecordsetClone
    rst.MoveLast
    rst.MoveFirst
    MaxRow = rst.RecordCount
    
    ReDim FieldNames(rst.Fields.Count)
    For X = 0 To rst.Fields.Count - 1
       FieldNames(X + 1) = Nz(rst.Fields(X).NAME, "N.A")
    Next


Set objXL = CreateObject("Excel.Application")
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Add

        Set objSht = objWkb.Worksheets(1)
        objSht.NAME = frm.Name

        With objSht
        


            .Range(.Cells(StartRow, 2), .Cells(StartRow + MaxRow, 2)) _
                    .CopyFromRecordset rst
                    For X = 0 To rst.Fields.Count
                    .Cells(1, 1 + X).Value = Nz(FieldNames(X), "N.A")
                    Next X
        End With
        .Columns.autofit
    End With
ExcelPath = Application.CurrentProject.Path & "\" & frm.Name & Format(Date, "mmm-dd-yy") & ".xlsx"

objXL.DisplayAlerts = False
objWkb.SaveAs ExcelPath , AccessMode:=3
objWkb.Close

objWkb.Close
objXL.Quit


Set rst = Nothing

Exit_cmdExcel_Click:
    Exit Sub

Err_cmdExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmdExcel_Click
End Sub

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi John. What is the code in createRecordSet?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial