Notes document action working with Microsoft Excel file

HCL Domino Domino Forum
1

I have already used LotusScript to read (and write) unstructured text files but I haven’t found a way to do that with .xls files.

Is it possible to read an .xls file from a Notes document (action) ?

I tried with LotusScript but didn’t succeed. I’m not sure JavaScript can do any better and using Java may be a bit to complicated.

I was wondering if anybody had a tip or a trick (or both ;-]).

Thanks

David

2

Subject: Notes document action working with Microsoft Excel file

Search the forum (especially the R5 forum), there are hundreds of posts about excel, search for OLE as well.

3

Subject: Notes document action working with Microsoft Excel file

I recently wrote an agent to create an Excel spreadsheet, not what you need but these excerpts might help show you how to interact with the object. You can infer any declarations I left out. I found much of this by doing a Google search on “lotuscript excel” and I know there are examples out there about accessing an existing spreadsheet.

Dim xl As Variant, xlWbk As Variant, xlSheet As Variant, xlsFileName As String

Set xl = CreateObject(“Excel.application”)

Set xlWbk = xl.Workbooks.Add



'Remove Sheets 2 and 3 that are created by default

xlWbk.Worksheets(3).Delete

xlWbk.Worksheets(2).Delete



Set xlSheet = xlWbk.Worksheets(1)

Call xlSheet.Activate

xlSheet.Name = "Name"



xlsFileName = "c:\xldata\rep440.xls"

…

xlSheet.Cells(1, 8) = "Date: " & Cstr(Today())

xlSheet.Cells(i, 12).NumberFormat = “$#,##0_);($#,##0)”

xlSheet.Cells(i, 12) = Clng(temprev)

xlSheet.Cells(i,10).NumberFormat = “@” 'text

	xlSheet.Cells(i, 10) = Mid$(buff$, 166, 2)

…

xl.Cells.select

xl.selection.Font.Name = "Arial"

xl.selection.Font.Size = 10



xl.Rows("1:1").select                     'Bold first row, color 

xl.Selection.Font.Bold = True

xl.selection.Font.size = 14

xl.selection.Font.ColorIndex = 50



xl.Rows("2:2").select                   'Color second row Red

xl.selection.Font.ColorIndex = 3



xl.Range("A3:R4").select		          'Bold column headings

xl.Selection.Font.Bold = True

xl.selection.Font.Underline = True

xl.selection.Font.ColorIndex = 11



xl.Range("G4:G" & Cstr(i)).Select          'Center data

xl.selection.horizontalalignment = 3     



Call xl.Range("A5:AG" & Cstr(i)).sort(xl.Range("A5"),,xl.Range("B5"),,,xl.Range("H5"),,,,,,,,)  'Sort by cols A, B, H



Call xl.Cells(4,1).subtotal(1,,subtotcols,,,)  'Subtotal - subtotcols is array of column numbers, 1 is column to get subtotal



Call xl.Cells(4,1).subtotal(17,,subtotcols,False,,)  'Subtotal rev/fee fields by opptynum, replace previous subtotal = false



xl.Range("A4:R" & Cstr(i)).select     'Autofit columns

xl.selection.columnwidth = 100

xl.selection.columns.Autofit

…

Call xlWbk.SaveAs(xlsFileName)

	Call xlWbk.Close

	Call xl.Quit

	xl = ""