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.

 

[Andrzejek]

Which line of code does it error on?

Have fun.

---- Andy

 

[10Miler]

Line 50, the Set Workbook line.

10 Dim MyExcel As Object
20 Dim MyWorkbook As Object
30 Dim MyWorksheet As Excel.Worksheet
40 Set MyExcel = New Excel.Application
50 Set MyWorkbook = MyExcel.Workbooks.Add
60 Set MyWorksheet = MyWorkbook.ActiveSheet

 

[10Miler]

Sorry, the Set MyWorkbook line!

 

[Andrzejek]

I tried your code:

10       Dim MyExcel As Object
20       Dim MyWorkbook As Object
30       Dim MyWorksheet As Excel.Worksheet
40       Set MyExcel = New Excel.Application
50       Set MyWorkbook = MyExcel.Workbooks.Add
60       Set MyWorksheet = MyWorkbook.ActiveSheet

70       MyExcel.Visible = True

After adding the reference to (Excel 2003):

MicroSoft Excel 11.0 Object Library

And it worked just fine. I don't have 2007 to test on, but check your reference to your Excel in VB, it may want to point to new EXCEL.EXE

Have fun.

---- Andy

 

[10Miler]

Andrjezek,
It does point to the Microsoft Excel 11.0 Object Library. Is that what is causing the error?

FYI, I have the code in line 70 (from your post) later in my code.

 

[10Miler]

It appears that I need the Microsoft Excel 12 Object Library.

http://support.microsoft.com/kb/219151

 

[dilettante]

This is why Microsoft suggests that you use early binding with Office programs.

 

[dilettante]

Er... not use early binding. Sorry.

 

[10Miler]

Ok, with my example how would I use late binding?

 

[10Miler]

After searching I found out that I should change this line

Set MyExcel = New Excel.Application

to this

Set MyExcel = CreateObject("Excel.Application")

Is that all I need to do?

 

[dilettante]

Not quite. You need to drop the reference to any Excel Object Library and declare all of the objects As Object as well.

 

[10Miler]

Ok, I have declared all of the objects as Object and dropped the reference to the Excel Object Library. I still get the same error. Do I need to set the objects with CreateObject? I am only using CreateObject with the MyExcel object.

 

[RoyVidar]

Perhaps we should see some of the rest of the code. This type of error can easily be caused by implicit referencing of automated properties, methods and objects.

There's some info on that, here

http://support.microsoft.com/default.aspx?kbid=178510

One reference in the original code that might be implicit, is the Set MyWorksheet = MyWorkbook.ActiveSheet, which could be replaced with either

Set MyWorksheet = MyWorkbook.Sheets("Sheet1")
or
Set MyWorksheet = MyWorkbook.Sheets(1)

Roy-Vidar

 

[10Miler]

Ok, I am changing ActiveSheet to Sheets(1).

 

[10Miler]

Still not working.

 

[RoyVidar]

Let me quote from the top of my post:
"Perhaps we should see some of the rest of the code. This type of error can easily be caused by implicit referencing of automated properties, methods and objects."

I am thinking there is more code, as the code you have posted does not do anything (except some instantiation).

Roy-Vidar

 

[10Miler]

Ok, here you go.

Private Sub ThisReportInExcel
10     	On Error GoTo ThisReportInExcel_Error
        ' Declare our Excel objects.
        Dim MyCMD As New ADODB.Command
        Dim param As ADODB.Parameter
        Dim Myrs As ADODB.Recordset
        Dim MyExcel As Object
        Dim MyWorkbook As Object
        Dim MyWorksheet As Object
        Dim i As Integer
20      Me.MousePointer = vbHourglass
30      MyCMD.ActiveConnection = Conn
40      MyCMD.CommandText = "myStoredProc"
50      MyCMD.CommandType = adCmdStoredProc
60      MyCMD.CommandTimeout = 0
70      Set param = MyCMD.CreateParameter("@ThisID", adInteger, adParamInput, , ThisID)
80      MyCMD.Parameters.Append param
90      Set Myrs = MyCMD.Execute
        ' See if there are records to even show a report on.
100     If Myrs.EOF And Myrs.BOF Then
110          MsgBox "There are no records found ", vbInformation
120          Me.MousePointer = vbNormal
130          Exit Sub
140     End If
150     Me.MousePointer = vbHourglass
160     Set MyExcel = CreateObject("Excel.Application")
170     Set MyWorkbook = MyExcel.Workbooks.Add
180     Set MyWorksheet = MyWorkbook.Sheets(1)
        Dim lnSheets  As Integer, lnCounter As Integer
190     With MyExcel
200        lnSheets = .Sheets.Count
210        For lnCounter = 2 To lnSheets
220           .Sheets(1).DELETE
230        Next
240        .Rows("1:1").Select
250        .Selection.DELETE '&& will delete header row
260     With .Sheets(1)
270  	   .Select
280        .Name = "This Report"
290     End With
300     MousePointer = vbHourglass
310     .Range("A1").Value = "Sample Report"
320     i = 1
330     .Range("A1:V1").MergeCells = True
340     .Range("A:V").Font.Name = "Arial"
350     .Range("A1:V1").Font.Size = 12
360     .Range("A1:V1").HorizontalAlignment = -4131
370     .Range("A1:V1").Font.Bold = True
380     With .Range("A" + CStr(1) + ":V" + CStr(1)).Borders(9)
390          .LineStyle = 1
400          .Weight = 4
410          .ColorIndex = -4105
420     End With
        Dim strFYInvo As String
        Dim curFYInvo As String
430     MousePointer = vbHourglass
440     .Range("A2").Value = Date
450     i = i + 2
460     .Range("A" + CStr(i)).Value = "Field1"
470     .Range("B" + CStr(i)).Value = "Field2"
480     .Range("C" + CStr(i)).Value = "Field3"
490     .Range("D" + CStr(i)).Value = "Field4"
500     .Range("E" + CStr(i)).Value = "Field5"
510     .Range("F" + CStr(i)).Value = "Field6"
520     .Range("G" + CStr(i)).Value = "Field7"
530     .Range("H" + CStr(i)).Value = "Field8"
540     .Range("I" + CStr(i)).Value = "Field9"
550     .Range("J" + CStr(i)).Value = "Field10"
560     .Range("K" + CStr(i)).Value = "Field11"
570     .Range("L" + CStr(i)).Value = "Field12"
580     .Range("M" + CStr(i)).Value = "Field13"
590     .Range("N" + CStr(i)).Value = "Field14"
600     .Range("O" + CStr(i)).Value = "Field15"
610     .Range("P" + CStr(i)).Value = "Field16"
620     .Range("Q" + CStr(i)).Value = "Field17"
630     .Range("R" + CStr(i)).Value = "Field18"
640     .Range("S" + CStr(i)).Value = "Field19"
650     .Range("T" + CStr(i)).Value = "Field20"
660     .Range("U" + CStr(i)).Value = "Field21"
670     .Range("V" + CStr(i)).Value = "Field22"
675     .Range("W" + CStr(i)).Value = "Field23"
680     With .Range("A" + CStr(i) + ":V" + CStr(i)).Borders(9)
690          .LineStyle = 1
700          .Weight = 4
710          .ColorIndex = -4105
720     End With
730     .Range("A:V").Font.Name = "Arial"
740     .Range("A2:V3").Font.Size = 8
750     .Range("B3:V3").HorizontalAlignment = -4108
760     .Range("A3:V3").Font.Bold = True
        Dim cost As Variant
770     Do While Not Myrs.EOF
780	     Me.MousePointer = vbHourglass
790          curFYInvo = Myrs!program + Myrs!FY + Myrs!SomeNo + CStr(Myrs!Field1)
800          cost = Myrs!cost
810          If strAnotherVar <> curOthervar Then
820  	     	i = i + 1
830         	.Range("A" + CStr(i)).Value = Myrs!Field2
840	        .Range("B" + CStr(i)).Value = Myrs!Field3
850     	.Range("C" + CStr(i)).Value = Myrs!Field4
860     	.Range("D" + CStr(i)).Value = Myrs!Field5
870             .Range("E" + CStr(i)).Value = Myrs!Field6
880             .Range("F" + CStr(i)).Value = someVariable
890             .Range("G" + CStr(i)).Value = Myrs!Field7
900             .Range("H" + CStr(i)).Value = Myrs!Field8
910             .Range("I" + CStr(i)).Value = Myrs!Field9
920             .Range("J" + CStr(i)).Value = Myrs!Field10
930             .Range("K" + CStr(i)).Value = Myrs!Field11
940             .Range("L" + CStr(i)).Value = Myrs!Field12
950             .Range("M" + CStr(i)).Value = Myrs!Field13
960             .Range("N" + CStr(i)).Value = Myrs!Field14
970             .Range("O" + CStr(i)).Value = Myrs!Field15
980             .Range("P" + CStr(i)).Value = Myrs!Field16
990             .Range("Q" + CStr(i)).Value = Myrs!Field17
1000            .Range("R" + CStr(i)).Value = Myrs!Field18
1010            .Range("S" + CStr(i)).Value = Myrs!Field19
1020            .Range("T" + CStr(i)).Value = Myrs!Field20
1030            .Range("U" + CStr(i)).Value = Myrs!Field21
1040            .Range("V" + CStr(i)).Value = Myrs!Field22
1050            .Range("A" + CStr(i) + ":V" + CStr(i)).Font.Size = 8
1060            .Range("A" + CStr(i) + ":H" + CStr(i)).HorizontalAlignment = -4108
1070         End If
1080         strAnotherVar = curOthervar
1090         Myrs.MoveNext
1100    Loop
1110    Me.MousePointer = vbHourglass
1120    Myrs.Close
1130    Set Myrs = Nothing
1140    Set MyCMD = Nothing
1150    .Rows.Rows.Cells.Select
1160    .Rows.EntireColumn.AutoFit
1170    .Range("A1").Select
       
1180    End With
1190    MyWorkbook.Activate
1200    MyExcel.Visible = True
        ' cleanup object and release excel:
1210    Set MyWorksheet = Nothing
1220    Set MyWorkbook = Nothing
        ' If MyExcel.Visible = False Then MyExcel.Quit
1240    Set MyExcel = Nothing
1250    Me.MousePointer = vbNormal
1260    On Error GoTo 0
1270    Exit Sub
ThisReportInExcel_Error:
1280      Me.MousePointer = vbNormal
1290      MsgBox "Error on line: " & Erl & vbCrLf & vbCrLf & "Error number: " & Err.Number & vbCrLf & vbCrLf & "Error description: " & Err.Description & 

vbCrLf & vbCrLf & "Error location: ThisReportInExcel of Form THIS_Form"

 

[RoyVidar]

I can't find anything in the Excel specific code that should cause that error by itself.

There might be things in the code, that probably need to be amended prior to swift running, but in the mean time, OTOTH;

One possibility, is that you've had errors while trying, and have an extra instance of Excel in memory, making the code stop at totally random or unexpected lines.

Usually, killing that instance in the task manager, should give a "clean run/try".

If that's not it, then I don't know. Could it be something having the same name but different scope (module level or global) with different properties/methods?

As a last suggestion, there might perhaps be something wrong with the Office install.

Now - disclaimer - I've run this with VBA (Office 2007), and the Excel specific automation code worked without giving any such errors

Don't know if it is related, but the part where you delete sheets

[tt]180 Set MyWorksheet = MyWorkbook.Sheets(1)
Dim lnSheets As Integer, lnCounter As Integer
200 lnSheets = .Sheets.Count
210 For lnCounter = 2 To lnSheets
220 .Sheets(1).DELETE
230 Next[/tt]

First you assign the first sheet of the workbook to the variable MyWorksheet, then, you delete the sheet it is referring to (.Sheets(1).Delete). But, you don't use that variable at all, so you could safely just remove the declaration, instantiation and release of the MyWorksheet object variable.

Perhaps rather:

[tt]180 Set MyWorksheet = MyWorkbook.Sheets(1)
Dim lnSheets As Integer, lnCounter As Integer
200 lnSheets = .Sheets.Count
210 For lnCounter = 2 To lnSheets
220 .Sheets(lnCounter).DELETE
230 Next[/tt]


Roy-Vidar

 

[10Miler]

Roy, I don't think any of those things are causing the problem. This app has worked well for years. Then I get Office 2007 on my computer and I get this error. It would seem to be a version dependency issue, but I'm not sure.