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
