Codejock Forums Homepage
Forum Home Forum Home > Codejock Products > ActiveX COM > Report Control
  New Posts New Posts RSS Feed - Copy To ClipBoard
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Copy To ClipBoard

 Post Reply Post Reply
Author
Message
SHAN View Drop Down
Groupie
Groupie
Avatar

Joined: 17 July 2010
Location: Dubai
Status: Offline
Points: 73
Post Options Post Options   Thanks (0) Thanks(0)   Quote SHAN Quote  Post ReplyReply Direct Link To This Post Topic: Copy To ClipBoard
    Posted: 28 July 2010 at 12:49am
Hi,
 
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
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows 7 Professional
Language: Visual Basic 6.0
Back to Top
Aaron View Drop Down
Senior Member
Senior Member
Avatar

Joined: 29 January 2008
Status: Offline
Points: 2192
Post Options Post Options   Thanks (0) Thanks(0)   Quote Aaron Quote  Post ReplyReply Direct Link To This Post Posted: 30 July 2010 at 2:10pm
Hi,
 
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.
 
 
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....
Back to Top
Xander75 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 26 April 2007
Status: Offline
Points: 353
Post Options Post Options   Thanks (0) Thanks(0)   Quote Xander75 Quote  Post ReplyReply Direct Link To This Post Posted: 01 August 2010 at 6:14am
Hi,

I actually created a specific ExportToExcel function that does exaclty this. I placed it in a class and made it dynamic so I could use it with any ReportControl control on any form. However I am not at work as it's the weekend and I am also off work for the next week to get some painting done in the flat!

I will post it as soon as I can, unless anyone beats me to it.
Product: Xtreme SuitePro (ActiveX) v15.3.1
Platform: Windows 7 64-bit (SP1) Professional Edition
Languages: C#.Net using Visual Studio 2012 & Visual Basic 6.0 (SP6)
Back to Top
SHAN View Drop Down
Groupie
Groupie
Avatar

Joined: 17 July 2010
Location: Dubai
Status: Offline
Points: 73
Post Options Post Options   Thanks (0) Thanks(0)   Quote SHAN Quote  Post ReplyReply Direct Link To This Post Posted: 03 August 2010 at 7:10am
Hi,
 
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)
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows 7 Professional
Language: Visual Basic 6.0
Back to Top
Xander75 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 26 April 2007
Status: Offline
Points: 353
Post Options Post Options   Thanks (0) Thanks(0)   Quote Xander75 Quote  Post ReplyReply Direct Link To This Post Posted: 09 August 2010 at 3:11am
Hi Shan,
 
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
                If rpc.Rows(i).Selected Then
                    x = x + 1
 
                    Set Record = rpc.Records.Record(i)
                    For j = 1 To rpc.Columns.Count            
                        .Cells(x, 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(x, j).NumberFormat = "@"
                                    Case True
                                        .Cells(x, j).NumberFormat = "dd/mm/yyyy"
                                End Select
                            Case True
                                .Cells(x, j).NumberFormat = "#,##0"
                        End Select
                    Next 
                Next 
            End If
        Else
            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
 
-------------------------------------------------------------------------------------------------------------
' To Call the function if placed inside a Module
Again like yours to call the function use the following if placed in a module:
 
    Call ExportToExcel(Me.Reportcontrol)
 
Or to export only the selected rows:
 
    Call ExportToExcel(Me.Reportcontrol, True)
 
-------------------------------------------------------------------------------------------------------------
' To Call the function if placed inside a Class
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.
Product: Xtreme SuitePro (ActiveX) v15.3.1
Platform: Windows 7 64-bit (SP1) Professional Edition
Languages: C#.Net using Visual Studio 2012 & Visual Basic 6.0 (SP6)
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.04
Copyright ©2001-2021 Web Wiz Ltd.

This page was generated in 0.156 seconds.