Sending records to Excel using CopyFromRrecordSet

 ---------------------------------------------------------------------------

Public Sub ReportControlToExcel(Rptcontrol As ReportControl)

      Dim xlObject    As Excel.Application
 
      Dim xlWB        As Excel.Workbook
            

          Set xlObject = New Excel.Application
 
      

          'This Adds a new woorkbook, you could open the workbook from file also
 
          Set xlWB = xlObject.Workbooks.Add
                  

          Clipboard.Clear 'Clear the Clipboard

          'Add Column Headers
             For i = 1 To Rptcontrol.Columns.count
                   xlObject.ActiveSheet.Cells(1, i).Value = _
                        "" & Rptcontrol.Columns.Column(i - 1).Caption
              Next
             
           'Select All Records
                        For i = 0 To Rptcontrol.Rows.count - 1
                         Rptcontrol.Rows(i).Selected = True
                        Next
             'Copy Records to ClipBoard
                      Rptcontrol.Copy
              With xlObject.ActiveWorkbook.ActiveSheet
 
              .Range("A2").Select 'Select Cell A2 (will paste from here, to different cells)

              .Paste              'Paste clipboard contents

              End With

              'Clear Selected Records from Selection
              Rptcontrol.SelectedRows.DeleteAll
           Clipboard.Clear
                      'Format excel Columns
             For i = 1 To Rptcontrol.Columns.count
            xlObject.ActiveSheet.Columns(i).AutoFit
            xlObject.ActiveSheet.Columns(i).HorizontalAlignment = xlCenter
              Next
              'Format Excel Rows
             xlObject.ActiveSheet.Rows(1).Font.Bold = True
          xlObject.ActiveSheet.Rows(1).Interior.Color = &HFFC0C0
         xlObject.ActiveSheet.Rows(2).Select
         'Freeze the First Row
         xlObject.ActiveWindow.FreezePanes = True
        

          ' This makes Excel visible

          xlObject.Visible = True

End Sub

 

 

-----------------------------------------------------------------------------------

'To call the Method Use

--------------------------------------------------------------------------------------