.Application.Visible and others stop working

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

Below is code that has been untouched and working for years. All it does is
opens Excel, creates a workbook and pastes the data on the clipboard onto
the worksheet. On one particular computer I am having a problem when it
gets to the line objXL.Application.Visible . I get the following error:

"Error -2147417851: Method 'Application' of object '_Application' failed"

What is funny is that I can get this to work if I step through the code in
the Debug window only when I have the Locals window open as well and explor
the objXL properties right down to objXL.Application.Visible and then the
rest of the code executes fine. If I don't drill down through the
properties in the Locals window, the code gives the error.

Here is the code:

Sub sTestXL()
Dim objXL As Object
Dim objActiveWkb As Object

On Error GoTo HandleErr
Set objXL = CreateObject("Excel.Application")
objXL.Application.Visible = True

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook
objActiveWkb.Application.ActiveSheet.Paste
objActiveWkb.Application.ActiveSheet.Cells.EntireColumn.AutoFit

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Module1.sTestXL"
End Select
End Sub
 
Clearly, Excel is taking a while to start on that PC.
(could be some dodgy Anti Virus program, or just
a damaged install). Does it help if you just use objXL.Visible?
(instead of excel.application.application.visible)
Or do you just get failure of Method 'Visible'?

(david)
 
No, that did not help. I am still gettng the automation error. Even if I
step through the code and wait for a minute or two after the CreateObject
statement it still fails.

It doubt that it is the AV because this has been working flawlessly on even
dog slow computers for so long.
 
I'm writing this from a Win 98/Office 97 pc, so I can't
check this, but is there way to make the excel object
switch to being a user object from being an automation
object (other than making the object visible and then clicking
on it with a mouse?)

Also, rather than doing a complete install, can you try replacing
the default spreadsheet (that is opened when you open excel)
in case that has become corrupted?

And you should try asking this question in an Excel group,
in case they have some suggestions about what is preventing
Excel from loading correctly.

(david)
 
Jordan said:
Below is code that has been untouched and working for years. All it
does is opens Excel, creates a workbook and pastes the data on the
clipboard onto the worksheet. On one particular computer I am having
a problem when it gets to the line objXL.Application.Visible . I get
the following error:

"Error -2147417851: Method 'Application' of object '_Application'
failed"

What is funny is that I can get this to work if I step through the
code in the Debug window only when I have the Locals window open as
well and explor the objXL properties right down to
objXL.Application.Visible and then the rest of the code executes
fine. If I don't drill down through the properties in the Locals
window, the code gives the error.

Here is the code:

Sub sTestXL()
Dim objXL As Object
Dim objActiveWkb As Object

On Error GoTo HandleErr
Set objXL = CreateObject("Excel.Application")
objXL.Application.Visible = True

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook
objActiveWkb.Application.ActiveSheet.Paste
objActiveWkb.Application.ActiveSheet.Cells.EntireColumn.AutoFit

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Module1.sTestXL"
End Select
End Sub
Click to expand...

Does it occur the first time you run the code, or only on consecutive
attempts? Is it just some sample code, or do you not release the
automated objects?

The reason I'm asking, is that they way you instantiate and use the
objects, as I've understood, might do implicit instantiation, and
perhaps create extra instances of Excel, which might also be the
case when not releasing the objects.

Try something like this (and you can put the visible thingie last)

Sub sTestXL()
Dim objXL As Object
Dim objWB As Object
Dim objWS As Object

On Error GoTo HandleErr
Set objXL = CreateObject("Excel.Application")

Set objWB = objXL.Workbooks.Add
Set objWS = objWB.Sheets(1)
objWS.Paste
objWS.Cells.EntireColumn.AutoFit
objXL.Visible = True

Set objWS = nothing
Set objWB = nothing
Set objXL = nothing
....

If this doesn't work, I'm *thinking* it might be due to part of the
Office installation on that perticular machine. I'm sure it wouldn't
hurt to try a "Detect and repair".
 
Back
Top