[SOLVED]Reading RC-send Column names/data to Excel
Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Report Control
Forum Description: Topics Related to Codejock Report Control
URL: http://forum.codejock.com/forum_posts.asp?TID=17398
Printed Date: 15 November 2024 at 3:31pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: [SOLVED]Reading RC-send Column names/data to Excel
Posted By: mstuart
Subject: [SOLVED]Reading RC-send Column names/data to Excel
Date 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 |
Replies:
Posted By: Aaron
Date 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.... |
Posted By: mstuart
Date 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 |