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
      docnum=1
      xx=1
      cp=0 ' reset the column position       
      
      While Not doc Is Nothing
            reportdate=Format(doc.ReportDate(0),"mm/dd/yyyy")
            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,",, _
                  "Error"
                  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)
            
            Do
                  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)
                  
                  Do
                        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
                        Stop                        
                        xlWs.Range("A1").OffSet(xx,cp).value=Fulltrim(msg)                        
                        cp=cp+1
                        
                        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)
      Wend
sullisnyc44Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sullisnyc44Connect With a Mentor Author Commented:
ta da

      If cellcounter>8 Then
                        
                        remainder = cellcounter Mod 4
                        
                        If remainder=1 Then
                              xx=xx+1 ' new row
                              xlWs.Range("A1").OffSet(xx,0).value=reportdate
                              i=1
                        End If                        
                        
                        xlWs.Range("A1").OffSet(xx,i).value=Fulltrim(msg)                        
                        i=i+1
                  End If
                  
                  
0
 
mbonaciCommented:
something like this:

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

        If counter >= 8 Then
            process cell
        End If

        counter = counter + 1
        get next cell

    Loop
Next
0
 
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
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
sullisnyc44Author Commented:
ahhh I just reread what you posted... I will try it
0
 
sullisnyc44Author Commented:
nope - that didn't do it either
0
 
mbonaciCommented:
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 )
	Next
	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	
		Next
	Next
ExitLoops:
	
	GetColumnsCount = GetColumnsCount - 1
	
leave:
	Exit Function
ErrHandler:
	Msgbox "Error in line " + Str$(Erl) + ", " + Str$(Err()) + ": " + Error(), 48, "GetColumnsCount"
	Resume leave
End Function

Open in new window

0
 
mbonaciCommented:
Of course, without these lines:

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

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

the number of rows is the variable
0
 
mbonaciCommented:
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.
0
All Courses

From novice to tech pro — start learning today.