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