Export to Excel questions

HCL Domino Domino Forum
1
  1. Is there any way to create more than 3 worksheets, when opening an Excel workbook with script. My code starts out as follows:

For i = 1 To 3

Set excelsheet = excelnewwkbk.Worksheets(i)

.

.

…which works fine, but if I change the 1 to 3 to 1 to 4, I get an error.

  1. The view from which I’m exporting lists multiple values as new entries. I want this to show in the Excel sheet as is in the view, but it is only writing the first value of the field, for any given document in the view. I am aware that NotesViewEntry.ColumnValues is the culprit, and that it is known to only show the first value per document in this situation. Is there any good workaround, though, for getting those multiple values to write to Excel?

Thanks in advance.

2

Subject: This is what I use for code

.Worksheets.Add ,.Worksheets(.Worksheets.Count), , -4167This adds a worksheet to the end (Sheet4)You can use this multiple times to add multiple sheets

hth

John

3

Subject: Export to Excel questions

Thanks, both of you, for your help. I was able to use your suggestions and successfully complete #1 above. I figured out a script workaround for #2 too, though it was a bit more complicated than I’d hoped (I had to get a handle on the individual fields that are supposed to show as multiple entries, and loop through them before exporting).

I’m relatively new to Notes-to-Excel script exporting. Is it also possible with script to a) Rename the worksheets (right now they just say "Sheet1, Sheet2, etc.), b) control the border style around the cells, and/or c) control the page setup setting which allows one to repeat the row of column headers at the top of every page? Thanks. - Marty

4

Subject: RE: Export to Excel questions

Hey marty, heres a trick to help u with OLE.

Just record a macro in excel, then view the code…then copy paste to ur ls (u’ll need to edit it slightly but ull know what to do im sure) also when u constants being used, like ‘xlsThick’ in the macro editor right click on the on it and select defintiion, so u can get a handle of the nuermic value of it. Becasue in LS ull need to specify the numeric equivalent and not the constant used by Excel.

Hope this helps with all ur future OLE.

ST

5

Subject: Export to Excel questions

for 1:

I think its cause by default excel has 2 worksheets only. have you tried inserting additional sheets first?

x.Sheets("Sheet1").Select

x.Sheets.Add

ST