[SOLVED]Reading RC-send Column names/data to Excel

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

Hi all,I'm building a VB6 DLL method that will receive the RC object and read the column names and records, then populate Excel.
When the RC columns are moved to a different position or columns are removed, they don't match up in Excel with what is showing in the RC.

Here's a simple example.
Columns in original order: Check box, ID, Name, Manager, Type
The user clicks a button which runs VB6 code to load the column names and records to Excel. This works fine at this point. Columns and data are in the correct Excel columns.

The user then wants to change the order of the columns. They move the RC Type column in between Name and Manager. Now the RC column layout is:
Check box, ID, Name, Type, Manager

The user clicks the button again to send to Excel. The column names and record values go to Excel fine, but the data doesn't match up with the column name.

Here's some screen shots.

RC before column move:

Excel before moving RC column:

RC after moving column:

Data appears correctly in RC object.

Excel after RC column move:

In the screen shot above, notice the Type column contains the data for the Manager column.

This is the problem I'm having. The data doesn't match up with the column name.

Here's a partial VB6 code listing that reads the reportControl object, extracts the record data. populates Excel , then reads the Columns object and populates Excel with the column names.

==================================
    ExportSelected = Me.ckExportSelected.Value

    With oSheet
       If ExportSelected = True Then     '// if records checked
            'preset the starting row for data
            x = 6

            'read every record in the Records object
            For i = 0 To RC.Records.Count - 1
                Set Record = RC.Records.Record(i)

                'left most column should be the checkbox
                'check the state of it: 1=checked (ie: user selected the record)
                If Record.Item(0).CheckboxState = 1 Then

                    For j = 1 To RC.Columns.Count
                        .Cells(x, j) = Record.Item(j - 1).Value
                    Next j

                    x = x + 1
                End If
            Next i
        Else                             '// all records in view

            For i = 0 To RC.Records.Count - 1
                Set Record = RC.Records.Record(i)
                For j = 1 To RC.Columns.Count
                    .Cells(i + 6, j) = Record.Item(j - 1).Value
                Next j
            Next i

        End If

        'format Columns
        For i = 0 To RC.Columns.Count - 1
            'set column title and set as Bold
            .Cells(5, i + 1) = RC.Columns(i).Caption
            .Cells(5, i + 1).Font.Bold = True

            'column alignment
            Select Case RC.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 i
    End With
============================================
Maybe it would be better to read the columns in a For loop and inside that, read the Records object in a For loop.

Any help on this would be appreciated.

Regards,
Mark Stuart

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
mstuart Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 06 April 2010 Location: United States Status: Offline Points: 36	Post Options Post Reply Quote mstuart Report Post Thanks(0) Quote Reply Topic: [SOLVED]Reading RC-send Column names/data to Excel Posted: 08 October 2010 at 2:05pm
	Hi all, I'm building a VB6 DLL method that will receive the RC object and read the column names and records, then populate Excel. When the RC columns are moved to a different position or columns are removed, they don't match up in Excel with what is showing in the RC. Here's a simple example. Columns in original order: Check box, ID, Name, Manager, Type The user clicks a button which runs VB6 code to load the column names and records to Excel. This works fine at this point. Columns and data are in the correct Excel columns. The user then wants to change the order of the columns. They move the RC Type column in between Name and Manager. Now the RC column layout is: Check box, ID, Name, Type, Manager The user clicks the button again to send to Excel. The column names and record values go to Excel fine, but the data doesn't match up with the column name. Here's some screen shots. RC before column move: Excel before moving RC column: RC after moving column: Data appears correctly in RC object. Excel after RC column move: In the screen shot above, notice the Type column contains the data for the Manager column. This is the problem I'm having. The data doesn't match up with the column name. Here's a partial VB6 code listing that reads the reportControl object, extracts the record data. populates Excel , then reads the Columns object and populates Excel with the column names. ================================== ExportSelected = Me.ckExportSelected.Value With oSheet If ExportSelected = True Then '// if records checked 'preset the starting row for data x = 6 'read every record in the Records object For i = 0 To RC.Records.Count - 1 Set Record = RC.Records.Record(i) 'left most column should be the checkbox 'check the state of it: 1=checked (ie: user selected the record) If Record.Item(0).CheckboxState = 1 Then For j = 1 To RC.Columns.Count .Cells(x, j) = Record.Item(j - 1).Value Next j x = x + 1 End If Next i Else '// all records in view For i = 0 To RC.Records.Count - 1 Set Record = RC.Records.Record(i) For j = 1 To RC.Columns.Count .Cells(i + 6, j) = Record.Item(j - 1).Value Next j Next i End If 'format Columns For i = 0 To RC.Columns.Count - 1 'set column title and set as Bold .Cells(5, i + 1) = RC.Columns(i).Caption .Cells(5, i + 1).Font.Bold = True 'column alignment Select Case RC.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 i End With ============================================ Maybe it would be better to read the columns in a For loop and inside that, read the Records object in a For loop. Any help on this would be appreciated. Regards, Mark Stuart
	Regards, Mark Stuart Product: Xtreme SuitePro (ActiveX) v13.2.1 Platform: WinXP (32bit)/Win7 (64bit) Language: VB6 (SP6), Magic eDeveloper v9.4, uniPaaS v1.9

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: 10 October 2010 at 6:13am
	Hi Mark, If you want always same postion of RecordItems loop the record collection. Dim rec As ReportRecord Dim recItem As ReportRecordItem Dim row As ReportRow Dim col As ReportColumn For Each rec In Me.ReportControl1.Records For Each recItem In rec Debug.Print recItem.Caption 'Every item has same position as you added it to RC Next recItem Next rec Suppose user sorted or grouped columns and want to export the "visual" part (as user sees on screen) loop rows collection For Each row In Me.ReportControl1.Rows Set rec = row.Record For Each col In Me.ReportControl1.Columns Debug.Print rec(col.ItemIndex).Caption Next col Next row Hope this helps
	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....

mstuart Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 06 April 2010 Location: United States Status: Offline Points: 36	Post Options Post Reply Quote mstuart Report Post Thanks(0) Quote Reply Posted: 11 October 2010 at 7:39pm
	Hi Aaron, Thank you very much for your reply. Today I finally got it working. I expanded on your code and here's how it came out. What this VB6 code does is to extract the Column and Row objects and send the "data" to MS Excel. ========================================= With oSheet '// column names r = 5 'column names go into row 5 c = 0 For Each Column In RC.Columns If Column.Visible = True And Column.Tag <> "<Tag>" Then c = c + 1 .Cells(r, c) = Column.Caption .Cells(r, c).Font.Bold = True Select Case Column.Alignment Case xtpAlignmentCenter .Columns(c).HorizontalAlignment = xlCenter Case xtpAlignmentRight .Columns(c).HorizontalAlignment = xlRight Case Else .Columns(c).HorizontalAlignment = xlLeft End Select End If Next Column 'At this point if the record count > 1000, make Excel visible If RC.Records.Count > 1000 Then oExcel.Visible = True End If '// rows r = 6 'row var c = 0 'column var For Each Row In RC.Rows If ExportSelected = True Then '// selected records only If Row.Record.Item(1).Checked = True Then For Each Column In RC.Columns If Column.Visible = True And Column.Tag <> "<Tag>" Then c = c + 1 .Cells(r, c) = Row.Record(Column.ItemIndex).Value End If Next Column 'increment Excel row number (r) r = r + 1 'reset to 0 for each row (c) c = 0 End If Else '// all records For Each Column In RC.Columns If Column.Visible = True And Column.Tag <> "<Tag>" Then c = c + 1 .Cells(r, c) = Row.Record(Column.ItemIndex).Value End If Next Column 'increment Excel row number (r) r = r + 1 'reset to 0 for each row (c) c = 0 End If Next Row End With =========================================
	Regards, Mark Stuart Product: Xtreme SuitePro (ActiveX) v13.2.1 Platform: WinXP (32bit)/Win7 (64bit) Language: VB6 (SP6), Magic eDeveloper v9.4, uniPaaS v1.9