Export Access Reports to excel which are having sub reports,group totals to excel with RTF file using VBA

Iam explaining how to export complex access report which are having sub reports,group totals  to excel with rtf file using automation Process and Vba


First we will  export our report to rtf file using docmd method like as below


DoCmd.OutputTo acOutputReport, "ReportName", acFormatRTF, "ReportPath", False


Next we will create  an excel object and copy all the word content to this file and save  it using following code

Please add Microsoft word,excel as references (click on tools and option refernces to get list of available refernces)

Sub DemoOfPasteToExcelFromWord()

    Dim oWord As Object, oDoc As Object
    Set oWord = CreateObject("Word.Application")
 
    '~~> Open the Attachement
    Set oDoc = oWord.Documents.Open(FileName:="Path of rtf file", ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=0, XMLTransform:="", _
        Encoding:=1200)

    '~~> Get the comeplete text and copy it

    oDoc.Range.Copy
    oDoc.Close

   ' create excel workbook
   Dim oExcelApp As Object
   Dim oExcelWrkBook As Object

   Set oExcelApp = CreateObject("Excel.Application")
   Set oExcelWrkBook = oExcelApp.Workbooks.Add
   oExcelWrkBook.Application.Visible = False
   oExcelWrkBook.Worksheets(1).Range("A1").Select
   oExcelWrkBook.Worksheets(1).Paste
   oExcelWrkBook.SaveAs ("Path where we want to save file with extension like xls or xlsx")
   oExcelWrkBook.Close
 
   'Clean objects
   Set oDoc = Nothing
   Set oExcelApp = Nothing
   Set oWord = Nothing
   Set oExcelWrkBook = Nothing


End Sub