Method '~' of object '~' failed error

[10Miler]

I get this message when trying to open an excel file in my VB6 application. It just started happening since I got a new PC with Excel 2007 on it. The excel file is being built dynamically by the app.

 

[dilettante]

This was tested with Excel 2003 and works without incident. Perhaps it fails with Excel 2007?

Option Explicit

Private Const xlWBATWorksheet = -4167 'Single Worksheet.

Private ExcelApp As Object
Private WorkBook As Object
Private WorkSheet As Object

Private Sub Command1_Click()
    Set ExcelApp = CreateObject("Excel.Application")
    Set WorkBook = ExcelApp.Workbooks.Add(xlWBATWorksheet)
    Set WorkSheet = WorkBook.ActiveSheet
    WorkSheet.Cells(1, 1).Value = "Hello"
    ExcelApp.Visible = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not ExcelApp Is Nothing Then
        Set WorkSheet = Nothing
        WorkBook.Saved = True 'Spoof so we can close out without saving.
        Set WorkBook = Nothing
        ExcelApp.Quit
        Set ExcelApp = Nothing
    End If
End Sub

Just a single Form, one command button. Click to open Excel, create a new one-sheet workbook, enter "Hello" in the upper-left cell, make it visible. Close Form to discard the workbook and close Excel.

 

[10Miler]

dilettante,
Yes it fails with Excel 2007. I will try that code in my app.

 

[10Miler]

dilettante,
I just copied that code into my app and it produces the same error. What now?

 

[dilettante]

You've got me. Pretty much all of that small example was taken right from the Excel 2007 documentation on MSDN. Unless somebody else with 2007 tries it we won't know where the problem is.

Have you seen anyone else report anything similar? I haven't turned anything up.

Automating Office applications is fraught with hazards at best. You might have an orphaned instance still running for example that throws your efforts off (kill it via Task Manager?).

You might also look at GetObject and CreateObject behavior of Office automation servers.

I would not be surprised to find some anti-virus or Group Policy interference involved either.

 

[strongm]

Works OK against my Excel 2007 ...

 

[10Miler]

dilettante,
A guy I work (with Excel 2007) with gets the same error when trying to run the .exe. But there is another person that has 2007 and doesn't have a problem.

strongm,
Are you compiling and running on the same machine? I am compiling on a machine with Excel 2003, and running on a machine with Excel 2007.

Most of my users have Excel 2003, so I could just roll back to Excel 2003 but I would rather come up with an version independent solution if possible. Moving to .NET with this app is in the long term plan, and I cannot wait!

 

[strongm]

>Are you compiling and running on the same machine? I am compiling on a machine with Excel 2003, and running on a machine with Excel 2007

No. Just Excel 2007. But that means we can eliminate Excel 7 by itself as the problem.

 

[10Miler]

FYI, the code bombs at this line:

Set WorkBook = ExcelApp.Workbooks.Add(xlWBATWorksheet)

That is consistent with my original code. It bombs at the Workbook.Add part. I will look at the GetObject and CreateObject link. Thanks!

 

[10Miler]

Ok, I got 2007 installed on my dev machine. Made sure the reference was pointing to MS Excel 12 Object Library. Recompiled and it still bombs! AHHH!!!

 

[10Miler]

Well, I put the ExcelApp.Visible before the Workbooks.Add code and it opens Excel, then throws the error on the Add line. The workbook sheet does open though.

 

[dilettante]

I'd have to guess you are still early binding. To test the code I provided a while back you'd create a new project and not set any references to Excel.

 

[10Miler]

I removed all references to Excel, and it doesn't work. I am using late binding, but this is in my existing project. I guess I will need to make up a dummy project.

 

[boogergoober]

I just upgraded my department to Office 2007.

When they run my app they get the same error.

The error is when i attempt to add a workbook..

Code: Set xlWrkBk = xlApp.Workbooks.Add("\\SRV011AFAFS001\user\Templates\BUB.xlt")

I have ref to excel12 object.


works fine on 2003

 

[10Miler]

I figured it out! There is a setting in Excel to disable add-ins. You have to add the Developer tab to the ribbon in MS Excel 2007. Then you go to Excel Options, then Add-ins. Check the box to disable add-ins.

WOOHOO!