? about ExcelWorkSheetExtender class

HCL Domino Domino Forum
1

I don’t know if any of you have seen or used this great script by Brandt Fundak:

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/da395751267ed34a85257383006a416c?OpenDocument&Highlight=0,ExcelWorkSheetExtender

My excel export scripts are not as nice and neat as this class so I was hoping to apply it to a new app.

However I was getting stuck adding a new sheet to the workbook and was wondering if someone could help me out.

I’m getting ‘SET required on class instance assignment’ on this line:

Me.m_targetXLSheet = Me.m_xlsheet

what is the proper syntax for the AddSheet sub?

I’d really appreciate any help on this…

2

Subject: ? about ExcelWorkSheetExtender class

The error message says it all – that line needs to begin with the “Set” keyword since it is setting an object variable. The reason you don’t get the error at compile time is because the variable is declared as a Variant, so the LS engine doesn’t know it’s an object until run time.

3

Subject: RE: ? about ExcelWorkSheetExtender class

Thanks Stan!

When I do :

Set xlsheet=xlapp.AddSheet(1,sheetName)

I get ‘illegal reference to AddSheet’

4

Subject: RE: ? about ExcelWorkSheetExtender class

AddSheet doesn’t return anything to set xlSheet to. It modifies your ExcelWorksheetExtender object by adding a sheet to it. Once you add a sheet, you are working on the new sheet so everything you do after that is to your new sheet.

5

Subject: RE: ? about ExcelWorkSheetExtender class

Thanks for the response!

so this should work right?

Dim xl As ExcelWorkSheetExtender

Set xl = New ExcelWorkSheetExtender(True, True)

Call xl.AddSheet(SHEET_POS_AFTER, "MyNewSheet")

except I get the ‘set’ error from my original post.

6

Subject: RE: ? about ExcelWorkSheetExtender class

If I am reading the code right, you shouldn’t need to call AddSheet because you are automatically working with the first sheet when you create your object. That said, did you fix the bug in the original code that Stan pointed out?

7

Subject: ? about ExcelWorkSheetExtender class

I haven’t tried this class (I sure will though next time I have a need), but does your code look something like:

Dim xl as ExcelWorkSheetExtender

Set xl = New ExcelWorkSheetExtender(True, True)

Call xl.AddSheet(SHEET_POS_AFTER, “MyNewSheet”)

8

Subject: RE: ? about ExcelWorkSheetExtender class

Thanks so much for the response but I’m still getting the same error…

doesn’t make sense to you either, right? I’m like losing my mind over this…