Excel table

HCL Domino Domino Forum
1

Hi I have a problem, my client want me to build a table with multiple rows of fields and then he would copy an excel table and click in the first cell of the Preliminary table and choose paste and it would paste the values of the copied Excel spreadsheet into this table. I have no idea how to do this, somebody have a suggestion? or maybe another way I could do this, thanks

2

Subject: This is possible if…

If you know the approximate size of the table to be pasted, then you could build a notes table with the required columns and enough rows to support the excel table. You would have to prepopulate each cell with a field to hold the excel values as well. So, if its a LARGE table, then this may not be an option. However, that said, if it were a table of say - 5 columns and 50 rows, simply have them click a button to browse to the excel file and then use code like below to bring each row into your notes table.

This example just populates a few specific cells, but if you put the code in a loop, you can cycle through multiple rows. The filename is stored using a button on the form with this code:

title:=“Select the Filename:”;

val:=@Prompt([LocalBrowse];title;“3”);

@SetField(“SalesFile”;val);

@Command([RunAgent];“(GetExcelData)”)

Sub Initialize

'prompts for file location and then pulls summary values into the nbi

Dim uiws As New notesuiworkspace

Dim uidoc As notesuidocument

Dim xlapp As Variant

Dim xlbook As Variant

Dim xlsheet As Variant

Dim filename As String, sheetname As String

Dim cellval As Variant



Set uidoc = uiws.CurrentDocument



filename = uidoc.FieldGetText("SalesFile")

If filename = "" Then

	Exit Sub

End If

sheetname = "Summary"



Set xlapp = CreateObject("Excel.Application")

xlapp.Visible = False

Set xlbook = xlapp.Workbooks.Open(filename)

Set xlsheet = xlbook.Worksheets(sheetname) 



'get each cell value and store on the summary page

'syntax is row, column

cellval = xlsheet.Cells(52,28).value  

Call uidoc.fieldsettext("SAsmyEquip",Str(cellval))



cellval = xlsheet.Cells(52,29).value  

Call uidoc.fieldsettext("SLaunch",Str(cellval))



cellval = xlsheet.Cells(52,30).value  

Call uidoc.fieldsettext("STotAsmy",Str(cellval))



Call uidoc.refresh



xlapp.Workbooks.Close

xlapp.Quit   'close excel

Set xlApp = Nothing  'free the memory that we'd used

End Sub

3

Subject: Excel table

It will be a hell of a job, since there is no support for variable tables in Notes. Some people have tried to build code that acts a little like variable tables, but it is complex.

When there is no need to have separate cells to copy, you could define a richtext field that can accept the pasting of the selected cells. This field will keep the original layout, but you cannot identify a single cell.