*Creating New Sheet with COM Addin*

[SP495]

Hi,

First post, so go easy and all that !

I am developing a COM addin that is essentially a port of an .xlam. The .xlam created two new sheets in the active workbook and all was fine and dandy.

This is not the case with the COM addin. Below is the connection code (for testing purposes). Adding a sheet to the active workbook of the 'connecting Excel application instance' fails but is fine if we create a new workbook - we can then add sheets to our hearts content. I am using VB6 btw:

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)

Set xl = Application

'****************************************************
    'DOES NOT WORK, throws a '1004 error - method ~ of object ~ failed'
    Dim AWB As Excel.Workbook
    Set AWB = xl.ActiveWorkbook
    AWB.Sheets.Add After:=AWB.Sheets.Count
    oXL.ActiveSheet.Name = "Blah Blah"
'****************************************************
' DOES WORK

'    Dim AWB As Excel.Workbook
'    Set AWB = xl.Workbooks.Add
'
'With xl.ActiveWorkbook
'
'    MsgBox "About to add a sheet to the new workbook"
'    .Sheets.Add
'    .ActiveSheet.Name = "Blah blah blah"
'
'End With

Anyideas would be useful. The idea is that settings are stored in the new sheets and hidden and then the workbook can be sent ot other with the addin and used etc...

Thanks in advance,

Steve.

 

[anvg]

Maybe, Excel on a connection time has not any book. You could check it using xl.Workbooks.Count

If xl.Workbook.Count > 0 Then
   Set AWB = xl.ActveWorkbook
Else
   Set AWB = xl.Workbooks.Add
End If

 

[SP495]

Maybe, Excel on a connection time has not any book. You could check it using xl.Workbooks.Count

If xl.Workbook.Count > 0 Then
   Set AWB = xl.ActveWorkbook
Else
   Set AWB = xl.Workbooks.Add
End If

Thanks for this but I should have mentioned a couple of things before I guess:

a. The same code was elsewhere previously (ie not in connect) and I was able to test for the number of sheets in the wb and it returned ok.
b. I am using VBA to connect to the addin (as I was getting two connects when having the addin load at startup and then when creating an object of one the classes) - this may well be another question but nevermind !

So, for the purposes of this issue, either ignore the fact that the code is in the connect event or take it for granted that there is an active workbook..

Have been pulling my hair out with this for the last few hours !!!

 

[anvg]

You can try to use after

'A class variable
Private xl As Excel.Application

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
  'set Application reference only
  Set xl = Application
End Sub

in second AddinInstace's interface method

Private Sub AddinInstance_OnStartupComplete(ByRef custom() As Variant) 'I was not able to find parameter's description for this method, I'm sorry.
   'It is worked.
   Dim ABook As Workbook
   Set ABook = xl.ActiveWorkbook
   '...
End Sub

 

[SP495]

You can try to use after

'A class variable
Private xl As Excel.Application

Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
  'set Application reference only
  Set xl = Application
End Sub

in second AddinInstace's interface method

Private Sub AddinInstance_OnStartupComplete(ByRef custom() As Variant) 'I was not able to find parameter's description for this method, I'm sorry.
   'It is worked.
   Dim ABook As Workbook
   Set ABook = xl.ActiveWorkbook
   '...
End Sub

Thanks for the response. To clarify - there is no problem with getting a reference to the active workbook - the active workbook of the addin initialising instance of Excel. The issue is with adding a new sheet. I cnnot get it to work in a COM addin at all (like I said before, forget that the code is in the connect event for the purpose of this discussion)...

I'm pretty sure I have tried everything, so was hoping that someone knew of if it is a known issue or something that needs to be set or changed or...

See what I mean ?

 

[SP495]

Alternatively, are there any other options for saving some details with the workbook in question so that they travel with it and a user with a copy of the addin can use them ?

[Realise that I'm clutching at straws here..]

 

[anvg]

I am sorry, I have hot Visual Basic 6, but I tested this problem in Visual Studio .Net (with ExcelDNA extention). It is like VB6. The code is

  Private tmpApp As Excel.Application
    Public Overrides Sub OnConnection(Application As Object, ConnectMode As Extensibility.ext_ConnectMode, AddInInst As Object, ByRef custom As System.Array)
        If Not Application Is Nothing Then
            tmpApp = CType(Application, Excel.Application)
            MsgBox(tmpApp.Workbooks.Count)
        End If
        MyBase.OnConnection(Application, ConnectMode, AddInInst, custom)
    End Sub
    Public Overrides Sub OnStartupComplete(ByRef custom As System.Array)
        Dim AWB As Excel.Workbook, ASheet As Excel._Worksheet
        MyBase.OnStartupComplete(custom)
        If Not tmpApp Is Nothing Then
            AWB = tmpApp.ActiveWorkbook
            ASheet = CType(AWB.Worksheets.Add, Excel._Worksheet)
            ASheet.Name = "blabla"
        End If
    End Sub

The MsgBox's message in the OnConnection method displaied "0".
The OnStartupComplete is called after Excel finished its startup process. The ActiveWorkbook can add worksheet and change its name.

 

[SP495]

A somewhat delayed response but for anyone else experiencing similar issues it turns out that ne being explicit and stating that the added object is a worksheet is what the problem was. Sometimes it worked and sometimes it didn't, so this was the solution...I shall have to put this tip up on my site.

Cheers. Steve