OLE Object array! how to do?

HCL Domino Domino Forum
1

I need to set an array of excell worksheet and i don’t know haw many tey are before running LS agent.

i have try in this way:

Forall dc In intDc				

	If dc <> "" Then

             redim preserve excelSheetArea( 0 to settAreaNum) as variant

             set excelSheetArea(settAreaNum)= excelNewWkbk.Worksheets(settAreaNum)



         ...omissis	



             settAreaNum = settAreaNum +1

End If

End Forall

it works only on the first loop on the second time doing the statemente SET, the agent return

OLE Automation error…

is there a different way to do it?

Thank you

2

Subject: OLE Object array! how to do?

You running the loop as many times as the number of elements in collection intDc. You sure that the number of elements in this collection is same as the number of sheets in excel?Also, Ubound(intDc) can get you number of elements to do a Redim only once in code.

3

Subject: RE: OLE Object array! how to do?

I have found solution by myself… the problem is not LS array variable but the Excell oel object has default only 3 worksheet, if you want more you must add it before set

in this way…

		If  settAreaNum < 3 Then

			Set excelSheetArea=   excelNewWkbk.Worksheets(3)

		Else 

			Set excelSheetArea=   excelNewWkbk.Worksheets.add
4

Subject: RE: OLE Object array! how to do?

I have one query…!

how can we set the new Xlsheet position in a workbook with the following statement.

Set excelSheetArea= excelNewWkbk.Worksheets.add

I tried

excelNewWkbk.Worksheets.add (after) as in excel macros

but it is not working…

Please help me for the same.

Thanx.

5

Subject: Positioning Excel Spreadsheets via LS

Mohit,

I have posted to both the 4.6 and 6/7 forums extensively on this topic (as the techniques have not changed in the 8 years I have been developing Lotus databases). A search of this forum for “excel and add” would have lead you to this post:

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/5a8d6ac7df3d8947852571cd0068fd89?OpenDocument&Highlight=0,excel,add

which in turn would have lead you to this post:

http://www-10.lotus.com/ldd/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/9dd101c8a69d20ec852569af0065ad88?OpenDocument&Highlight=0,excel,brandt

The problem is that the first parameter of the add method always refers to placing a sheet before the referenced sheet. To add after the sheet, you need to pass a null value to the before parameter. So it should look like this:

excelNewWkbk.Worksheets.add(null, after)

where after is the sheet object you wish to add the new sheet after. so let’s say you have an object called xlSheet you want to place the new sheet after, the code would look like this:

excelMewWkbk.Worksheets.add(null,xlSheet)

does that make sense?

brandt

6

Subject: RE: Positioning Excel Spreadsheets via LS

What is this function ‘search’ you refer to? Can you send me exact instructions for how to implement this function?

!!!URGENT!!!

:wink: