Export Data from notes to existing excel file using LotusScript

HCL Domino Domino Forum
1

Hi,I have a keyword document with an excel file as an attachment. User opens a form and enter some data and when he clicks on the a button the data entered in the form is exported to the excel sheet in the keyword docement.

Please resolve my problem asap.

Regards,

Kishore

2

Subject: Export Data from notes to existing excel file using LotusScript

It seems like you need to do the following in sequence:

  1. Locate the keyword document by using GetDocumentByKey

  2. Extract the file attachment from the document to a path on your hard disk

  3. Use automation to populate cells in the detached spreadsheet file with data from the document

  4. Do something with the spreadsheet - mail it? save it?

3

Subject: RE: Export Data from notes to existing excel file using LotusScript

Hi,Thanks for the reply.

I used the following code, can you plz check whether it is right way or not?

Sub Click(Source As Button)

Dim ses As New NotesSession

Dim currDb As NotesDatabase

Dim doc As NotesDocument

Dim ws As New NotesUIWorkspace

Dim uidoc As NotesUIDocument

Dim kView As NotesView

Dim kDoc As NotesDocument

Dim rtitem As NotesRichTextItem

Dim SSItem As NotesRichTextItem

Dim excelApp As Variant

Dim excelWkbks As Variant

Dim excelNewWkbk As Variant

Dim excelSheet As Variant

Dim fileName As String

Set currDb = ses.CurrentDatabase

Set uidoc = ws.CurrentDocument

If Not uidoc.EditMode Then

uidoc.EditMode=True

Call uidoc.expandAllSections

End If

Set doc = uidoc.Document

Set kView = currDb.GetView(“Keywords”)

Set kDoc = kView.GetDocumentByKey(“ISS Spreadsheet”)

Set rtitem = kDoc.GetFirstItem( “KEY_Attachments” )

If ( rtitem.Type = RICHTEXT ) Then

Forall xlFile In rtitem.EmbeddedObjects

   Call xlFile.ExtractFile( "d:\" & xlFile.Source )

End Forall

End If

fileName = “d:\vgteactuatorevaluation13.xls”

Set excelApp = CreateObject(“Excel.Application”)

excelApp.Visible = True

excelApp.Workbooks.Open filename

Set excelWkbks = excelApp.ActiveWorkbook

Set excelSheet = excelWkbks.ActiveSheet

excelSheet.Range(“B4”).Value = doc.WC_Inspector(0)

excelSheet.Range(“B6”).Value = doc.WC_CustName(0)

excelSheet.Range(“B7”).Value = doc.WC_Ref(0)

excelSheet.Range(“B8”).Value = doc.WC_ESN(0)

excelSheet.Range(“B9”).Value = doc.WC_VIN(0)

excelSheet.Range(“B12”).Value = doc.WC_DateIns(0)

excelSheet.Range(“B13”).Value = doc.WC_DateRemv(0)

excelSheet.Range(“B14”).Value = doc.WC_Life(0)

excelSheet.Range(“B15”).Value = doc.WC_LifeUnits(0)

excelSheet.Range(“B16”).Value = doc.WC_HPartNo(0)

excelSheet.Range(“B17”).Value = doc.WC_PartNo(0)

excelSheet.Range(“B19”).Value = doc.WC_SerialNo(0)

excelSheet.Range(“B20”).Value = doc.WC_DateRecd(0)

excelSheet.Range(“B21”).Value = doc.WC_BuildDate(0)

excelSheet.Range(“B22”).Value = doc.WC_PartNo_1(0)

excelSheet.Range(“B23”).Value = doc.WC_SerialNo_1(0)

Call uidoc.Reload()

Call uidoc.Save()

Call uidoc.Close()

End Sub