Hi,
Has anyone has a script that update xcelfile records from notes without creating a new xcel file.
What I’m trying to do is to write an agent that will on daily basis update the Xcel spreadsheet on a specified folder and if I’ve got a new created document on Notes, the agent should add that new record on the existing spreadsheet.
Subject: Update Xcel file records from Notes
Below code snippet will help you:
Dim WS as New NotesUIWorkspace
Dim Filters$, vFile,SourceFile$
Dim XLobj, XLsheet, XLusedrange, FinalRowNum&, Rowto
Filters$ = “Microsoft Office Excel Files |.xls;.xl”
vFile = WS.OpenFileDialog(False, “Browse For Excel File”,Filters$, “”, “”)
'Don’t contine if user cancels the dialog.
If Isempty(vFile) Then Exit Sub
SourceFile = vFile(0)
If Len(Dir(SourceFile)) = 0 Then
Msgbox "The Source file doesn't exist. Please correct."
Exit Sub
End If
Print |Getting Handle to the Excel File…|
Set XLobj = GetObject(SourceFile)
'activate Excel Object
Call XLobj.Activate()
'Get handle to the first Worksheet.
Set XLsheet = gXLobj.WorkSheets(1)
'Activate First worksheet.
Call XLsheet.Activate
'Create the Excel Range object for the Rows and Columns that contain data.
Print |Creating Excel Range Object with rows and columns those contain data…|
Set XLusedrange = XLsheet.UsedRange
'Get the final Row Number,which contains data
FinalRowNum& = XLusedrange.Rows.count
'Row to update
UpdateRowNum& = FinalRowNum& + 1
XLsheet.cells(UpdateRowNum&, ).value = doc.x(0)
.
.
HTH
Adi