How to copy Report Control data with Column Headers to ClipBoard ?.

 

I want to Export data with Headers to Excel...!

 

If clipboard  option available it is easy to paste in excel?

 

Any help for this?.

 

Thanks in Advance

 

You can use Copy method of ReportControl. Don't know if they will copy column headers as well... Another possibility is to use Excel Object and create own function to add columns and contents to Excel sheet.

 

 

 

Thanks for You Reply...!

 

 I tried myself a method  and it works...! This is the Code for Converting Report Control Data with Headers to Excel

 

 

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

 

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

 

call ReportControlToExcel(Reportcontrol1)

 

Glad you got it sorted out on your own, I have attached the code I created to export either all rows or the selected rows from a ReportControl and format the cells accordingly.

 

Please Note: I opted out of using the Copy function of the ReportControl as this only copies selected rows, instead I use the record of each row to do what I need to do to copy and format all or only the selected rows. This means I don't have to set each row individually to be selected then remove the selection as your code suggests. This then allows a users selected rows not to be removed by the code "ReportControl.SelectedRows.DeleteAll".

 

Public Sub ExportToExcel(rpc As ReportControl, Optional ExportOnlySelected As Boolean = False)
    Dim Record As ReportRecord
    Dim xlsApp As Excel.Application
    Dim xlsWSheet As Excel.Worksheet
    Dim i As Long, j As Long, x As Long
    
    ' Get or Create Excel Object
    On Error Resume Next
    Set xlsApp = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then
        Set xlsApp = New Excel.Application
        Err.Clear
    End If
   
    ' Create the WorkSheet
    Set xlsWSheet = xlsApp.Workbooks.Add.ActiveSheet
    

    With xlsWSheet 
        ' Export Data from the ReportControl

        If ExportOnlySelected Then

            x = 1

 

            For i = 0 To rpc.Records.Count - 1

                Set Record = rpc.Records.Record(i)
                For j = 1 To rpc.Columns.Count            
                    .Cells(i + 2, j) = Record.Item(j - 1).Value

 

                    ' Format the Cell, formats are Date, Number & Text
                    Select Case IsNumeric(Record.Item(j - 1).Value)
                        Case False
                            Select Case IsDate(Record.Item(j - 1).Value)
                                Case False
                                    .Cells(i + 2, j).NumberFormat = "@"
                                Case True
                                    .Cells(i + 2, j).NumberFormat = "dd/mm/yyyy"
                            End Select

                        Case True
                            .Cells(i + 2, j).NumberFormat = "#,##0"
                    End Select
                Next 
            Next 
        End If

 

        ' Format the Columns

        For i = 0 To rpc.Columns.Count - 1

            ' Export ColumnHeaders & set as bold
            .Cells(1, i + 1) = rpc.Columns(i).Caption

            .Cells(1, i + 1).Font.Bold = True

 

            ' Autofit column headers

            .Columns(i).AutoFit

 

            ' Format the Column Alignments
            Select Case rpc.Columns.Column(i).Alignment
                Case xtpAlignmentCenter
                    .Columns(i + 1).HorizontalAlignment = xlCenter
                Case xtpAlignmentRight
                    .Columns(i + 1).HorizontalAlignment = xlRight
                Case Else
                    .Columns(i + 1).HorizontalAlignment = xlLeft
            End Select
        Next 
    
        ' Move to first cell to unselect
        .Range("A1").Select
    End With

    ' Show the Excel Window
    With xlsApp
        .ActiveWindow.SplitRow = 1
        .Visible = True
    End With

    
    Set xlsApp = Nothing
    Set xlsWSheet = Nothing
End Sub

 

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

 

    Call ExportToExcel(Me.Reportcontrol)

 

Or to export only the selected rows:

 

    Call ExportToExcel(Me.Reportcontrol, True)

 

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

If you put it into a class as I have lets say called "Functions" then use the following:

 

    Call Functions.ExportToExcel(Me.Reportcontrol)

 

Or to export only the selected rows:

 

    Call Functions.ExportToExcel(Me.Reportcontrol, True)

 

Class objects are better for this kind of dynamic code as it's more robust to store reusable code and it makes finding what functions you have available to your project easier to locate as typing the class name and pressing the "." key displays all avaliable funtions in a dropdown list.