Rich Text Table To Excel

Really - I'm almost there but just failing to get what I want to excel. My brain is not wrapping around this:

I have a rich text table that I am able to pull data from. The Rich Text Table has 4 columns and a variable number of rows.

The issue is that I want to ignore the first 8 cells and at the beginning of each row insert a date from the document. I'm just not getting the excel placement right.... The cell I want to be in column 2 is in column 5.

      xlWs.Range("A1").OffSet(0,0).value= "Report Date" 'A
      xlWs.Range("A1").OffSet(0,1).value= "Name" 'B
      xlWs.Range("A1").OffSet(0,2).value= "Time Up"'C
      xlWs.Range("A1").OffSet(0,3).value= "Time Down" 'D
      xlWs.Range("A1").OffSet(0,4).value= "Comments" 'E
      Dim rttableData() As String
      Set db = session.CurrentDatabase
      Set dc = db.UnprocessedDocuments
      Set doc = dc.GetFirstDocument
      Dim docnum As Integer
      cp=0 ' reset the column position       
      While Not doc Is Nothing
            Set body = doc.GetFirstItem("Body")
  REM Find first table in Body item
            Set rtnav = body.CreateNavigator
            If Not rtnav.FindFirstElement(RTELEM_TYPE_TABLE) Then
                  Messagebox "Body item does not contain a table,",, _
                  Exit Sub
            End If
  REM Set up range and navigator for table
            Set rtRangeTable = body.CreateRange
            Call rtRangeTable.SetBegin(rtnav)
            Call rtRangeTable.SetEnd(rtnav)
            Set rtNavTable = rtRangeTable.Navigator
  REM Find cells in table
            Set rtRangeCell = body.CreateRange
            Set rtRangePara = body.CreateRange
            cellCounter = 0
            Call rtNavTable.FindFirstElement(RTELEM_TYPE_TABLECELL)
                  msg = ""                  
                  cellCounter = cellCounter + 1
    REM Set up range and navigator for cell
                  Call rtRangeCell.SetBegin(rtNavTable)
                  Call rtRangeCell.SetEnd(rtNavTable)
                  Set rtNavCell = rtRangeCell.Navigator
    REM Find paragraphs in cell
                  Call rtNavCell.FindFirstElement(RTELEM_TYPE_TEXTPARAGRAPH)
                        Call rtRangePara.SetBegin(rtNavCell)
                        msg = msg & rtRangePara.TextParagraph & Chr(13)
                        Print msg
                  Loop While rtNavCell.FindNextElement(RTELEM_TYPE_TEXTPARAGRAPH)
            '      Messagebox msg,, "Cell " & cellCounter
                  If cellcounter > 8 Then
                        If cp=5 Then
                              xlWs.Range("A1").OffSet(xx,0).value=doc.LineReportDate(0) ' actual line report date
                              cp=1 'new column position
                              xx=xx+1'new row
                        End If
                  End If
            Loop While rtNavTable.FindNextElement(RTELEM_TYPE_TABLECELL)
            Set doc=dc.GetNextDocument(doc)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

something like this:

for i = 0 to numColumns (for each table row)
    counter = 0

        If counter >= 8 Then
            process cell
        End If

        counter = counter + 1
        get next cell

sullisnyc44Author Commented:
ok - I already do that with the 'cellcounter' my issue is that there's no way to really figure out the cell position per se.

The number of rows is a variable but the number of columns is not.

There are 4 columns (the first 2 rows are headers, which is why the cell counter starts >8.

Also the first cell of each row I want to insert a date from the document.

My issue is translating that to excel - the code I have is just not working
sullisnyc44Author Commented:
ahhh I just reread what you posted... I will try it
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

sullisnyc44Author Commented:
nope - that didn't do it either
sullisnyc44Author Commented:
ta da

      If cellcounter>8 Then
                        remainder = cellcounter Mod 4
                        If remainder=1 Then
                              xx=xx+1 ' new row
                        End If                        
                  End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
There's no way you can know whether NotesRichTextNavigator is in the first column or not.
So, in order to get the number of columns use the function that searches for first blank cell in a row (it's best to use it on header row, that contains column names), like this one (this one searches first row):
Function GetColumnsCount( excel As Variant ) As Long
	On Error Goto ErrHandler
	'mbonaci, 23.03.2007
	'we count Excel worksheet columns
	'when we find first empty cell we say the end is found
	Dim abc(26) As String
	Dim col As String, rez As String
	Dim i As Integer, j As Integer, dummy As Variant
	'fill abc array with A-Z letters (Excel column names)
	j = 0
	For i = 65 To 90
		j = j + 1
		abc( j ) = Chr$( i )
	abc( 0 ) = ""
	'first columns A, B, C, D, ...
	'then columns AA, AB, AC, AD, ...  BA, BB, BC. BD, ... ... ...
	For i = 0 To Ubound( abc )
		For j = 1 To Ubound( abc )
			col = Trim$( abc( i ) & abc( j ) )
			GetColumnsCount = GetColumnsCount + 1
			dummy = col & "1"     'Sometimes fails when directly transferring field name so we use variant
			rez = excel.Range( dummy ).Value     'Get the value from MS Excel field
			If Isempty( rez ) And Left$( Cstr( excel.Range( dummy ).NumberFormat ), 1 ) = "#" Then GetExcelField = 0
			If rez = "" Or rez = 0 Then Goto ExitLoops	
			Redim Preserve fieldNames( GetColumnsCount - 1 )
			fieldNames( GetColumnsCount - 1 ) = rez	
	GetColumnsCount = GetColumnsCount - 1
	Exit Function
	Msgbox "Error in line " + Str$(Erl) + ", " + Str$(Err()) + ": " + Error(), 48, "GetColumnsCount"
	Resume leave
End Function

Open in new window

Of course, without these lines:

                        Redim Preserve fieldNames( GetColumnsCount - 1 )
                        fieldNames( GetColumnsCount - 1 ) = rez

I used the same pass to collect column names...
OK, I see your code now, but how do you know that you need to divide with 4?
sullisnyc44Author Commented:
the number of columns is constant =4

the number of rows is the variable
You confused me with you first post, you said that you want to ignore the first 8 cells at the beginning of each row, and that you have only 4 columns.

OK, now I understand, you wanted to ignore first 8 cells in each column.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.