Codejock Developer Community : [SOLVED]Reading RC-send Column names/data to Excel

[SOLVED]Reading RC-send Column names/data to Excel : Hi Aaron,Thank you very much for...

Mon, 11 Oct 2010 19:39:25 +0000

Author: mstuart
Subject: 17398
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 <> "" 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 <> "" 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 <> "" 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

=========================================

[SOLVED]Reading RC-send Column names/data to Excel : Hi Mark, If you want always...

Sun, 10 Oct 2010 06:13:09 +0000

Author: Aaron
Subject: 17398
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

[SOLVED]Reading RC-send Column names/data to Excel : Hi all,I'm building a VB6...

Fri, 08 Oct 2010 14:05:26 +0000

Author: mstuart
Subject: 17398
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