Tip

Formatting Excel data in rich text tables

The code below picks up data from a selected Excel spreadsheet and writes it into a Notes document. In the Notes document, it will generate a table dynamically and put the data into the table.

  
 In the DECLARATIONS

Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim One As String 
Dim row As Integer
Dim xlFilename As String
Dim xlsApp, xlsWorkBook , xlSheet, 
xlSheets As Variant

Dim rtnav As NotesRichTextNavigator
Dim body As NotesRichTextItem
Dim styles(1 To 9) As 
NotesRichTextParagraphStyle

Dim tmpFic As Variant
Dim sFic As String

Function Header
 For iColumn1% = 1 To 9 Step 1
  Call body.BeginInsert(rtnav)
  If iColumn1% = 1 Then
   temp = "Explanation"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 2 Then
   temp = "Org"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 3 Then
   temp = "Ct"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 4 Then
   temp = "Dept" 
   Call body.AppendText(temp)
  End If
  If iColumn1% = 5 Then
   temp = "Dtl"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 6 Then
   temp = "Sub"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 7 Then
   temp = "Debit"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 8 Then
   temp = "Credit"  
   Call body.AppendText(temp)
  End If
  If iColumn1% = 9 Then
   temp = "JV_Description"  
   Call body.AppendText(temp)
  End If
  Call body.EndInsert
  Call rtnav.FindNextElement
(RTELEM_TYPE_TABLECELL)
 Next
End Function

Function FixedInfo
 row = 2 '
 Do While True
  With xlsheet
Rowloop:   
   row = row + 1
   If row = 3 Then
    doc.org = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 5 Then
    doc.Period_Year = .Cells( row, 2 ).Value  
    doc.Trans_Date = .Cells( row, 13 ).Value  
    Goto Rowloop
   End If
   If row = 6 Then
    doc.Trans_Type = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 7 Then
    doc.JVNo = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 8 Then
    doc.Description = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 11 Then
    doc.Validate_on_Entry = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 12 Then
    doc.Source_Document = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If  row = 13 Then
    doc.Analysis_Code = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 14 Then
    doc.Security_Class = .Cells( row, 2 ).Value  
    Goto Rowloop
   End If
   If row = 16 Then
    doc.StopPeriod_Year = .Cells( row, 2 ).Value  
    Exit Do
   End If
  End With
 Loop
 Dim dateTime As New NotesDateTime( "" )
 dateTime.LSLocalTime = Now
 doc.CreatedBy = "Created from Excel"
 doc.CreattionDate = dateTime.DateOnly
End Function

Function OuvreXLS() As Variant
 Dim ws As New NotesUIWorkspace
 OuvreXLS = ws.OpenFileDialog(False, 
"Please select the Excel 
File", "Excel Files | *.xls", "c:") End Function


In the CLick Event

Sub Click(Source As Button) 
 Dim sess As New NotesSession
 Dim sCol As String
 Set db = sess.CurrentDatabase
' Picking up the required File 
 tmpFic = OuvreXLS()
 If tmpFic(0) = "" Then
  Exit Sub
 End If
 sFic = tmpFic(0)
 
' Opening Excel 
 Set xlsApp = CreateObject("Excel.Application") 
' Open in the XL File 
 xlsApp.Workbooks.Open sFic    
'Accessing the Sheets and getting a Count 
 Set xlsWorkBook = xlsApp.ActiveWorkbook 
 temp = xlsWorkBook.Sheets.Count 
 Set xlSheets = xlsWorkBook.WorkSheets 
' Looping thru the Sheets and generating 
the required Documents 
 For t% = 1 To temp
  Set xlSheet = xlSheets(t%)
  xlSheet.Activate  
  xlsApp.Visible = False      
  xlSheet.Cells.SpecialCells(11).Activate
  xlsLigne = xlsApp.ActiveWindow.ActiveCell.Row 
  xlsColonne = 
xlsApp.ActiveWindow.ActiveCell.Column
  
  For icoln% = 1 To xlsColonne
   sCol = xlSheet.Cells(1, icoln%).Value    
   If sCol = "XXX  VOUCHER" Then
    vFlag = "True"
    Exit For
   Else
    vFlag = "False"
   End If
  Next
  If vFlag = "True" Then  
' Checking for the Name which must be 
JV with any two numbers  
   Set doc = New NotesDocument(db)  
' Loop the Rows and Col. 
   doc.Form = "FixedSim" 
   Call FixedInfo
   rowCount% = xlsLigne 
   rowCount% = rowCount% - 22
   columnCount% = 9
   Set body = New NotesRichTextItem(doc, "Body")
' Get the Column Width and Style filled in 
   For i% = 1 To 9 Step 1
    If i% = 1 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* 2.5   
    End If
    If i% = 2 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* .25   
    End If
    If i% = 3 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* .20  
    End If
    If i% = 4 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* .35   
    End If
    If i% = 5 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* .25   
    End If
    If i% = 6 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* .25
    End If
    If i% = 7 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).Alignment = ALIGN_RIGHT
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* 1.1   
    End If
    If i% = 8 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).Alignment = ALIGN_RIGHT
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* 1.1   
    End If
    If i% = 9 Then
     Set styles(i%) = 
sess.CreateRichTextParagraphStyle
     styles(i%).LeftMargin = 0
     styles(i%).FirstLineLeftMargin = 0
     styles(i%).RightMargin = RULER_ONE_INCH 
* 2.5   
    End If 
   Next
' END OF Get the Column Width and Style filled in 
' Create the table with above info and get a Navigator 
   Call body.AppendTable(rowCount%, 
columnCount%,,, styles) 
   Set rtnav = body.CreateNavigator
   Call rtnav.FindFirstElement
(RTELEM_TYPE_TABLECELL) 
   row = 19
   Do While True
    For iRow% = 1 To rowCount%  
     With xlsheet
      row = row + 1
      If .Cells( row, 10 ).Value 
= "Total" Then
       For iColumn% = 1 To 
columnCount% Step 1
        Call 
body.BeginInsert(rtnav)
        If iColumn% = 1 
Then
         temp 
= .Cells( row, 10 ).Value
         Call 
body.AppendText(temp)
        End If
        If iColumn% = 7 
Then
         temp 
= .Cells( row, 13 ).Value
         Call 
body.AppendText(temp)
        End If
        If iColumn% = 8 
Then
         temp 
= .Cells( row, 14 ).Value
         Call 
body.AppendText(temp)
        End If
        Call 
body.EndInsert
        Call 
rtnav.FindNextElement
(RTELEM_TYPE_TABLECELL)      
       Next
       Exit Do
      Else
      End If
      For iColumn% = 1 To 
columnCount% Step 1
       If iRow% = 1 And 
iColumn% = 1 Then
        Call Header
        iRow% = 2
        iColumn% = 1
       End If
       Call body.BeginInsert
(rtnav)
       If iColumn% = 1 Then
        temp = .Cells( 
row, 1 ).Value 
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 2 Then
        temp = .Cells( 
row, 8 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 3 Then
        temp = .Cells( 
row, 9 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 4 Then
        temp = .Cells( 
row, 10 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 5 Then
        temp = .Cells( 
row, 11 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 6 Then
        temp = .Cells( 
row, 12 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 7 Then
        temp = .Cells( 
row, 13 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 8 Then
        temp = .Cells( 
row, 14 ).Value
        Call 
body.AppendText(temp)
       End If
       If iColumn% = 9 Then
        temp = .Cells( 
row, 15 ).Value
        Call 
body.AppendText(temp)
       End If
       Call body.EndInsert
       Call 
rtnav.FindNextElement
(RTELEM_TYPE_TABLECELL)
      Next  
      Goto nextRow
     End With
nextRow:   
    Next  
   Loop 
   Call doc.Save(True, False)   
' Get the Data from the Active Sheet 
and Create a Notes Document  
  Else
   Msgbox "This is not a Journal Voucher 
Excel Sheet. 
Please try again with a JOURNAL 
VOUCHER Excel File" 
  End If  
 Next 
' disconnecting the Excel and releasing 
the memory 
 xlsWorkbook.Close False 
 xlsApp.Quit 
 Set  xlsApp = Nothing
End Sub

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip exchange by member Narendra Deshmukh. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.

This was first published in October 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.