We help IT Professionals succeed at work.

Access VBA manipulate Excel object

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

What Access VBA code would I use to create an Excel object, push data to a sheet in it
and then format certain ranges?

Thanks
Comment
Watch Question

Do more with

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

Commented:
Hi Murray
I posted the code on the previous question :  https://www.experts-exchange.com/questions/29166288/Access-Push-data-to-Excel.html#a42991565
Take it for a spin and see what is missing ...
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks John. Will do
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi John. Is there by any chance a more simple example. I am finding that code a bit tricky
John TsioumprisSoftware & Systems Engineer

Commented:
Hi Murray...you are right...this is the *Complex* edition... :)
This is the simple one
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:
I found the following code. Instead of specifying a path is it possible to just generate  a new Excel file?

Sub ToExcel()


    Dim ExceApp As Object
    Dim wbk As Object
    Dim rst As Recordset
    Set ExcelApp = CreateObject("Excel.Application")
    Set wbk = ExcelApp.workbooks.Open("Your Excel Workbook Path")
    Set rst = CurrentDb.OpenRecordset("Select * from t_Arms")
    wbk.worksheets(1).range("a1").copyfromrecordset rst
    wbk.Save
    wbk.Close
    ExcelApp.Quit
    Set rst = Nothing
    Set wbk = Nothing
    Set ExcelApp = Nothing

End Sub

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Take a look at the "simpler" edition
Senior Developer
Commented:
E.g.

Option Compare Database
Option Explicit

Public Sub Test()

  Dim Recordset As DAO.Recordset
  
  Set Recordset = CurrentDb.OpenRecordset("SELECT * FROM MSysObjects;", dbOpenSnapshot)
  ExcelExport Recordset, "MSysObjects"
  Recordset.Close
  Set Recordset = Nothing
  
End Sub

Public Function ExcelExport(ByVal CRecordset As DAO.Recordset, ByVal CSheetName As String) As Object

  On Local Error Resume Next

  Dim Excel As Object ' Excel.Application
  Dim Workbook As Object ' Excel.Workbook
  Dim Worksheet As Object ' Excel.Worksheet
  
  Dim Count As Long
  
  Set ExcelExport = Nothing
  Set Excel = CreateObject("Excel.Application")
  Set Workbook = Excel.Workbooks.Add
  Set Worksheet = Workbook.Sheets(1)
  Worksheet.Name = CSheetName
  For Count = 0 To CRecordset.Fields.Count - 1
    Worksheet.Range("A1").Offset(, Count).Value = CStr(CRecordset.Fields(Count).Name)
  Next Count
  
  Worksheet.Range("A2").CopyFromRecordset CRecordset
  Set ExcelExport = Worksheet
  Excel.Visible = True
  Set Worksheet = Nothing
  Set Workbook = Nothing
  Set Excel = Nothing

End Function

Open in new window

And formatting certain ranges is not hard, but depends on your use-case.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Murray,

you may like to review https://www.devhut.net/2017/03/15/ms-access-vba-export-recordset-to-excel/ to see an example of various elements of Excel automation.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much