#1   Report Post  
Bob Mignault
 
Posts: n/a
Default Error 458

I have 2 computers. Computer XP uses Windows XP and Computer 98 uses Windows
98.

I dabble in programming (Microsoft Visual Basic, Learning Edition).

I have installed some of my programs on both computers. Most of these
programs use Excel files. Until recently, everything worked fine on both
computers.

Recently, Computer 98 ran into a problem with the Freecell program, so I
re-installed Windows 98, and this cured the Freecell problem. However, I
believe that in some way, this may have caused a problem with the Excel
program on this computer, with the result that the Excel-based programs on
Computer 98 no longer work. I keep getting the message: "Run-time error 458:
Variable uses an Automation type not supported in Visual Basic".

Both computers use the identical Excel program (Version 10). I have
re-installed the Office 2000 program, including the Excel program, but this
does not cure the problem.

Would very much appreciate any help to overcome this problem.

Regards,

Bob Mignault


  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

Bob Mignault wrote:
I keep getting the message: "Run-time error 458:
Variable uses an Automation type not supported in Visual Basic".


On what VB statement does this occur?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Bob Mignault
 
Posts: n/a
Default

Hi Bill,

In the Form_Load event,
After Dim xlApp As Object
Dim xlBook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")

The statement: Set xlBook = xlApp.Workbooks.Open("C:\Program Files\ProjectA\
FileA.xls")
causes the error.

In another event,
After Windows("FileA.xls").Activate

The statement: ActiveWorkbook.Save
causes the error.

Regards,
Bob Mignault


"Bill Manville" wrote in message
...
Bob Mignault wrote:
I keep getting the message: "Run-time error 458:
Variable uses an Automation type not supported in Visual Basic".


On what VB statement does this occur?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #4   Report Post  
Bill Manville
 
Posts: n/a
Default


I guess this is a VB application, not VBA within Excel?
I assume you have referenced the Excel object library

After Windows("FileA.xls").Activate

I assume you mean xlApp.Windows("FileA.xls").Activate
and xlApp.ActiveWorkbook.Save

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5   Report Post  
Bob Mignault
 
Posts: n/a
Default

Hi again Bill,

Yes it is a VB application, not VBA within Excel.

My problem all started with trying to open an Excel file. See "Open an Excel
File" on Jan 8/05 at microsoft.public.vb.syntax.

Following Jezebel's suggestion, I removed the reference to the Excel
library, and the following Form_Load event coding worked:

Private Sub Form_Load()
Dim xlApp As Object
Dim xlBook as Object

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open("C:\Program Files\FileA.xls")
xlApp.Visible = True
xlApp.ShowWindowsInTaskbar = True
End Sub

(Note: Just before End Sub, if I add: xlApp.WindowState = xlMinimized
I get a run-time error 1004: Unable to set WindowState property of the
Application class.)

I thought my problems were solved, until I tried the following command
button event:

Private Sub cmdOK_Click()
Dim xlApp As Object
Dim xlBook as Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Windows("FileA.xls").Activate
....
End Sub

The last statement before End Sub causes Run-time error 9: Subscript out of
range.

Bill, I would appreciate your further comments.

Regards,

Bob Mignault




"Bill Manville" wrote in message
...

I guess this is a VB application, not VBA within Excel?
I assume you have referenced the Excel object library

After Windows("FileA.xls").Activate

I assume you mean xlApp.Windows("FileA.xls").Activate
and xlApp.ActiveWorkbook.Save

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup





  #6   Report Post  
Bill Manville
 
Posts: n/a
Default

Bob Mignault wrote:
(Note: Just before End Sub, if I add: xlApp.WindowState = xlMinimized
I get a run-time error 1004: Unable to set WindowState property of the
Application class.)


Do you have a reference to the Excel object library?

If not, xlMinimized will not be defined and, if you don't have Option
Explicit at the top of your module (assuming that applies in VB as it
does in VBA) you will have an implicitly defined variable whose value is
Empty. This will give error 1004.

I thought my problems were solved, until I tried the following command
button event:

Private Sub cmdOK_Click()
Dim xlApp As Object
Dim xlBook as Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Windows("FileA.xls").Activate
....
End Sub


If that is the extent of your code then I am not surprised that the error
occurs since you have created a new instance of Excel which doesn't have
FileA.xls open in it. If you want to reference the previously created
instance of Excel you should make xlApp a module-level variable (declared
before all the Subs in the module) and remove the Dim statements from
each of the Sub procedures.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #7   Report Post  
Bob Mignault
 
Posts: n/a
Default


"Bill Manville" wrote in message
...
Bob Mignault wrote:
(Note: Just before End Sub, if I add: xlApp.WindowState = xlMinimized
I get a run-time error 1004: Unable to set WindowState property of the
Application class.)


Do you have a reference to the Excel object library?

If not, xlMinimized will not be defined and, if you don't have Option
Explicit at the top of your module (assuming that applies in VB as it
does in VBA) you will have an implicitly defined variable whose value is
Empty. This will give error 1004.

I thought my problems were solved, until I tried the following command
button event:

Private Sub cmdOK_Click()
Dim xlApp As Object
Dim xlBook as Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Windows("FileA.xls").Activate
....
End Sub


If that is the extent of your code then I am not surprised that the error
occurs since you have created a new instance of Excel which doesn't have
FileA.xls open in it. If you want to reference the previously created
instance of Excel you should make xlApp a module-level variable (declared
before all the Subs in the module) and remove the Dim statements from
each of the Sub procedures.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



Bill,

I changed the coding as you suggested above, and everything now works fine.

I don't know why my original code works on one computer, and not on the
other. but I can live live with that!

Many thanks for your help and patience.

Regards,

Bob Mignault


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 07:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"