Copy To ClipBoard

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   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

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
SHAN Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 17 July 2010 Location: Dubai Status: Offline Points: 73	Post Options Post Reply Quote SHAN Report Post Thanks(0) Quote Reply 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

Aaron Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 29 January 2008 Status: Offline Points: 2192	Post Options Post Reply Quote Aaron Report Post Thanks(0) Quote Reply 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....

Xander75 Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 26 April 2007 Status: Offline Points: 353	Post Options Post Reply Quote Xander75 Report Post Thanks(0) Quote Reply 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)

SHAN Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 17 July 2010 Location: Dubai Status: Offline Points: 73	Post Options Post Reply Quote SHAN Report Post Thanks(0) Quote Reply 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

Xander75 Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 26 April 2007 Status: Offline Points: 353	Post Options Post Reply Quote Xander75 Report Post Thanks(0) Quote Reply 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)