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)
|