I was mostly interested in the CopyFromRecordSet method.

I've used this with the ADODB.Connection and ADODB.RecordSet objects, and was hoping to just reference the ReportRecords collection/object to the method.

Reason being: 

1) there may be a lot of records that have to be loaded, Using the clipboard with a large record set, may crash the program.

2) some columns may be hidden by the user, removing a column from the ReportControl during runtime, and therefore the program has to consider not to send the hidden column(s) to Excel.

3) Also, I'm using the 1st column as a checkbox. This allows the user to check which records to work with to send to Excel.

Regards,

Mark Stuart

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

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

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

 

Is there any chance that using the Excel Range.CopyFromRecordSet ReportRecords would send records of the ReportControl to Excel?

Anyone using this at all? If so, do you have an example VB6 code that shows how?

Regards,

Mark Stuart