Exporting a Notes Database 1

[xenoc]

I need to export the contents of a notes database to Word Docs,(just the views) Is there any way to do this faster than the individual file/export method. Even when I have tried this and chosen Word it trashes the tables. The put get stuff is crazy, the documentation doesn't even refer to the same file names.

Please help before I burn the office down as an interim solution.

 

[Mortan]

I do exporting documents in MS Excel. I wrote an agent that fuzzy scans the database and found documents field export into txt file. Each field is determined by semilicon. Than using default MS Excel converter I open the txt file in excel.
(If you need this I can send you some source codes)


If you need to export some views into word. You can write an agent that reads specified view and stores all the information needed in some txt file. Then open the txt in word.

If you want to have exported information in rich format then I dont know how to do it.

hope it will help
Mortan

 

[pmonett]

Hi, I have had to do Word/Excel exports also, and I have built two Script libraries based upon examples I have found here and there. Take a look at them below and use them if you find them useful.

You are free to send them to someone else and modify them as you wish. If you do modify them, I would appreciate getting a copy to see what you have done.

Thanks,

Pascal Monett
pmonett@sharok.net

'LSWordExport:

Option Public



Const boldface_on = True
Const boldface_off = False
Const underline_on = True
Const underline_off = False

Const justify_left = 0
Const justify_center = 1
Const justify_right = 2
Const justify_full = 3
Sub NewWordDoc(msword As Variant, worddoc As Variant, IsVisible As Variant)
'msword and worddoc are for passing back control of the instance of word an the new word document respectively
'IsVisible is for determining whether on not to make the instance of Word visible

'Create an instance of Word
Set msword = CreateObject("Word.Application&quot
Set docs = msword.Documents
msword.Visible = IsVisible
'Create a new word document and set it to active
Call docs.Add
Set worddoc = docs(1)
worddoc.Activate
'Open an existing Word Document
'Call msword.Documents.Open("""c:\temp\test.doc""&quot

End Sub
Sub AttachWord(worddoc As Variant, rtitem As NotesRichTextItem, filename As String)
'Saves the Ms Word document as filename$ then attaches it as an object to the rich text item.
'Useful for attaching the Word document to a Notes document

Dim object As NotesEmbeddedObject
Call SaveWord(worddoc, filename)
Set object = rtitem.EmbedObject( EMBED_ATTACHMENT, "", filename, "thewordfile" )
Call rtitem.AddNewLine( 1 )

End Sub
Sub SaveWord(worddoc As Variant, filename As String)
'Saves the Word document as filename$

worddoc.SaveAs filename

End Sub
Sub AddWordTextAndLink(worddoc As Variant, mytext As String, mylink As String)
'Adds the text in mytext$ to the Word document then makes it a link the the address in mylink$ then moves
'down to the next line

Call worddoc.Activewindow.Selection.TypeText(mytext)
Call worddoc.Activewindow.Selection.HomeKey(5, 1)
Call worddoc.Hyperlinks.Add(worddoc.Activewindow.Selection.Range, mylink , "&quot
Call worddoc.Activewindow.Selection.TypeText(Chr$(13) + Chr$(13))

End Sub
Sub AddWordText(worddoc As Variant, myText As String)
'Adds the text in mytext$ to the Word document then moves down to the next line

Call worddoc.Activewindow.Selection.TypeText(mytext)

End Sub

Sub CloseWord(msword As Variant, worddoc As Variant)
'Closes the Word document then closes the instance of Word

worddoc.Close
msword.Application.Quit

End Sub


Sub setwordfont(wordDoc As Variant,fontname As String,fontsize As Integer,boldface As Variant,underline As Variant)

With wordDoc.activewindow.selection
.Font.Name = Chr$(34) & fontname & Chr$(34)
.Font.Size = fontsize
.Font.Bold = boldface
.Font.underline = underline
.Font.Italic = False
%REM
.Font.StrikeThrough = False
.Font.DoubleStrikeThrough = False
.Font.Outline = False
.Font.Emboss = False
.Font.Shadow = False
.Font.Hidden = False
.Font.SmallCaps = False
.Font.AllCaps = False
.Font.ColorIndex = 2 ' Blue - value of wdBlue MS Word Constant
.Font.Engrave = False
.Font.Superscript = False
.Font.Subscript = False
.Font.Spacing = 0
.Font.Scaling = 100
.Font.Position = 0
.Font.Kerning = 0
.Font.Animation = 6 ' Same as constant: wdAnimationShimmer
.TypeText Chr$(13)
%ENDREM
End With
End Sub

Sub addwordline(worddoc As Variant,numlines As Integer)
Dim i As Integer

For i = 1 To numlines
Call worddoc.Activewindow.Selection.TypeText(Chr$(13))
Next

End Sub
Sub setwordparagraph(worddoc As Variant,justificationtype As Integer)

worddoc.activewindow.selection.paragraphformat.alignment = justificationtype

End Sub


Sub setwordpagesetup(worddoc As Variant,leftmargin As Integer,rightmargin As Integer,topmargin As Integer,bottommargin As Integer)

With worddoc.PageSetup
.LeftMargin = leftmargin
.RightMargin = rightmargin
.TopMargin = topmargin
.BottomMargin = bottommargin
End With
End Sub


Sub insertWordPageBreak(worddoc As Variant)

worddoc.activewindow.Selection.InsertBreak

End Sub



Example using above class :

The example shows an agent that create a doc an insert values form an selected notes document. The CreateNewDoc - method requires a parameter. This
parameter represents the name of the template (with path) on which the doc was created. The InsertAtTM - method requires two parameters, the first parameter
is the name of the bookmark and the second parameter the textvalue which would inserted.

'(Options)
Option Public
Use "libWord" 'the script library with the class

Sub Initialize

Dim session As New NotesSession
Dim workspace As New NotesUIWorkspace
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim collection As NotesDocumentCollection
Dim oWord As cWord

Set db = session.CurrentDatabase
Set collection = db.UnprocessedDocuments 'the marked docs in a view

If collection.Count > 1 Then
Messagebox "You have select more than one doc", 16, "Error"
Exit Sub
End If 'If collection.Count > 1

Set doc = collection.GetNthDocument(1)

Set oWord = New cWord 'Initialize the class
' Creates a new doc with the template brief.dot.
Call oWord.CreateNewDoc("c:\office\template\brief.dot&quot

'Insert values from Notes fields to bookmarks in the Word document
Call oWord.InsertAtTM("Bookmark1", doc.Fullname(0))
Call oWord.InsertAtTM("Bookmark2", doc.City(0))
Call oWord.InsertAtTM("Bookmark3", doc.State(0))

Call oWord.ActivateWord
Delete oWord

End Sub

'LSExcelExport:

Option Public



Const boldface_on = True
Const boldface_off = False
Const underline_on = True
Const underline_off = False

Const justify_left = -4131
Const justify_center = -4108
Const justify_right = -4152

Const line_edgeleft = 7
Const line_edgetop = 8
Const line_edgebottom = 9
Const line_edgeright = 10

Const xlContinuous = 1
Const xlDecimalSeperator = 3
Const xlThousandsSeperator = 4

Dim xlExport_Decimal_seperator As String
Dim xlExport_Thousands_seperator As String

Sub NewExcelSheet(msexcel As Variant, xlSheet As Variant, IsVisible As Variant)
'msexcel is for passing back control of the instance of word an the new word document respectively
'IsVisible is for determining whether on not to make the instance of Word visible

'Create an instance of Excel
Set msexcel = CreateObject("Excel.Application&quot
msexcel.DisplayAlerts = False
msexcel.visible = True
msexcel.Workbooks.Add '// Open a new Excel file
'Set xlsheet = msexcel.ActiveWorkbook.ActiveSheet

End Sub
Sub setcellfont(msexcel As Variant, cellrange As Variant, fontname As String,fontsize As Integer,boldface As Variant,underline As Variant)

With msexcel.activeworkbook.activesheet.range(cellrange)
.Font.Name = fontname
.Font.Size = fontsize
.Font.Bold = boldface
.Font.underline = underline
.Font.Italic = False
%REM
.Font.StrikeThrough = False
.Font.DoubleStrikeThrough = False
.Font.Outline = False
.Font.Emboss = False
.Font.Shadow = False
.Font.Hidden = False
.Font.SmallCaps = False
.Font.AllCaps = False
.Font.ColorIndex = 2 ' Blue - value of wdBlue MS Word Constant
.Font.Engrave = False
.Font.Superscript = False
.Font.Subscript = False
.Font.Spacing = 0
.Font.Scaling = 100
.Font.Position = 0
.Font.Kerning = 0
.Font.Animation = 6 ' Same as constant: wdAnimationShimmer
.TypeText Chr$(13)
%ENDREM
End With
End Sub


Sub fillcell(msexcel As Variant, posy As Variant, posx As Variant, cellvalue As Variant)

msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy) = cellvalue
End Sub


Sub setcellwidth(msexcel As Variant, posy As Integer, cellwidth As Variant)
msexcel.activeworkbook.activesheet.Columns(posy).ColumnWidth = cellwidth
End Sub

Sub setstdcellwidth(msexcel As Variant, cellwidth As Variant)
msexcel.activeworkbook.activesheet.standardwidth=cellwidth
End Sub

Sub namecellrange(msexcel As Variant, cellrange As String,rangename As String)
msexcel.activeworkbook.activesheet.Range(cellrange).Value = rangename
End Sub

Sub setNumberFormat(msexcel As Variant, posy As Variant, posx As Variant, mantisse_size As Integer,decimal_size As Integer)
Dim number_format As String
Dim i As Integer
Dim counter As Integer

If xlExport_Decimal_seperator = "" Then getlocalformat(msexcel)
counter = 0
For i = mantisse_size To 2 Step -1
number_format = number_format & "#"
counter = counter + 1
If counter Mod 3 = 0 Then
number_format = number_format & xlExport_Thousands_seperator
End If
Next
number_format = number_format & "0" & xlExport_Decimal_seperator
For i = 1 To decimal_size
number_format = number_format & "0"
Next
msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).numberformat = number_format
End Sub


Sub fillcellformula(msexcel As Variant,posy As Variant,posx As Variant,cellformula As String)
If Left(cellformula,1) <> &quot;=&quot; Then
cellformula = &quot;=&quot; & cellformula
End If
msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).formula = cellformula
End Sub
Sub setcellfontbold(msexcel As Variant,posy As Variant,posx As Variant)
msexcel.activeworkbook.activesheet.cells(posx,posy).font.bold = True
End Sub

Sub setcellfontunderline(msexcel As Variant,posy As Variant,posx As Variant)
msexcel.activeworkbook.activesheet.cells(posx,posy).font.underline = True
End Sub

Sub setcellfontitalic(msexcel As Variant,posy As Variant,posx As Variant)
msexcel.activeworkbook.activesheet.cells(posx,posy).font.italic = True
End Sub

Sub setcellalignment(msexcel As Variant,posy As Variant,posx As Variant,justifytype As Integer)
msexcel.activeworkbook.activesheet.cells(posx,posy).HorizontalAlignment = justifytype
%REM
Range(&quot;A1:A5&quot.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range(&quot;B1:B5&quot.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range(&quot;C1:C5&quot.Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
%ENDREM
End Sub

Function xlLetterCoord(posy As Variant,posx As Variant) As String
Dim colval As Integer
Dim colletter1 As String
Dim colletter2 As String
Dim returnval As String
Dim calc As Integer

calc = Int(posy/26)
If calc > 0 Then
colletter1 = Chr(calc+64)
End If
colval = posy - (calc*26)
colval = colval + 64
colletter2 = Chr(colval)
returnval = colletter1 & colletter2 & Trim(Str(posx))
xlletterCoord = returnval
End Function
Sub setborderrange(msexcel As Variant,cellrange As String,thickness As Integer)

msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeLeft).linestyle = xlContinuous
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeLeft).Weight = thickness
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeLeft).Color = 0
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeTop).linestyle = xlContinuous
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeTop).Weight = thickness
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeTop).Color = 0
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeBottom).linestyle = xlContinuous
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeBottom).Weight = thickness
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeBottom).Color = 0
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeRight).linestyle = xlContinuous
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeRight).Weight = thickness
msexcel.activeworkbook.activesheet.range(cellrange).Borders(line_EdgeRight).Color = 0
End Sub
Sub getlocalformat(msexcel As Variant)
xlExport_Decimal_seperator = msexcel.international(xlDecimalSeperator)
xlExport_Thousands_seperator = workstr & msexcel.international(xlThousandsSeperator)
End Sub
Sub setNumberFormatPercent(msexcel As Variant,posy As Variant,posx As Variant,mantisse_size As Integer,decimal_size As Integer)
Dim number_format As String
Dim i As Integer
Dim counter As Integer

If xlExport_Decimal_seperator = &quot;&quot; Then getlocalformat(msexcel)
counter = 0
For i = mantisse_size To 2 Step -1
number_format = number_format & &quot;#&quot;
counter = counter + 1
If counter Mod 3 = 0 Then
number_format = number_format & xlExport_Thousands_seperator
End If
Next
number_format = number_format & &quot;0&quot; & xlExport_Decimal_seperator
For i = 1 To decimal_size
number_format = number_format & &quot;0&quot;
Next
number_format = number_format & &quot;%&quot;
msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).numberformat = number_format
End Sub