Excel 97 issue
Excel 97 issue
Excel 97 issue
Excel 97 issue
Excel 97 issue
Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue
Excel 97 issue Excel 97 issue
Excel 97 issue
Go Back  Xtreme Visual Basic Talk > > > Excel 97 issue


Reply
 
Thread Tools Display Modes
  #1  
Old 01-08-2004, 08:01 AM
tesko tesko is offline
Newcomer
 
Join Date: Jan 2004
Location: Ontario, Canada
Posts: 4
Default Excel 97 issue


Hello,

I've been trying so many different methods for automating Excel 97 with .NET and I keep getting the same error.

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Reception.exe
Additional information: The server threw an exception.

This only happens with Excel 97, not Word 97. Here is the latest code I've tried (taken from lesson 2 of this forum).

Dim oExcel As Excel.Application
Dim oWorkbooks As Excel.Workbooks
Dim oSheets As Excel.Sheets
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng1, oRng2 As Excel.Range

oExcel = New Excel.Application
oExcel.Visible = True

I've also tried using Excel.ApplicationClass instead, and I've tried using
the CreateObject function. Excel DOES become visible, but the exception is still thrown.

Any other suggestions would be appreciated.
Reply With Quote
  #2  
Old 01-08-2004, 08:21 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Your code is 100% fine. That's the bad news.

That's bad, because it doesn't give us any room to manouver.

Excel '97 is a quirky platform to deal with even in VB 6.0 or using VBA. There are just a number of bugs/quirks in XL 8.0 that can make it difficult to use. I try to avoid it like the plague, but those that are experienced with it get used to it and sometimes enjoy knowing all it's complexities...

In this case though, I know of no-one who has both .Net and Excel '97. So I don't know how to test it. Your code is so clean that I that I think it is safe to assume that the problem is with Excel '97 and not with your code. It is a bit surprising that a simple call to 'oExcel.Visible = True' is an issue, but given the quirkiness of the XL 8.0 platform and the fact that COM Interop in .Net is somewhat of a mess to begin with... and well, you might have an intractible problem here.

You need to try to find someone else with Excel '97 and VB.Net so that they can test this for you... The problem is that few will have this combination.

Hmmm... I'm sorry I can't do better, but your code looks fine.

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 01-08-2004, 08:28 AM
tesko tesko is offline
Newcomer
 
Join Date: Jan 2004
Location: Ontario, Canada
Posts: 4
Default

Yeah, that's what I thought. The only reason I have 97 installed is that the company I'm developing for still has Office 97 on all the systems. I even tried suppressing the error message with "On Error Resume Next". The error goes away, but no code after the error is executed.

Anyway, I'll wait for a few more replys from different forums and will probably eventually tell them that I can't finish development until office is upgraded (scheduled in a couple months).
Reply With Quote
  #4  
Old 01-08-2004, 08:39 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

You could develop in VB 6.0, of course, and not have this issue. But if the goal is to do it in .Net and therefore be able to migrate & improve the program in the future, then, yeah, you are stuck.

Mind you that COM Interop in .Net is a really quirky thing unto itself. It is MUCH easier to do Automation in VB 6.0 than in .Net. If you haven't given it a look, you might find the FAQ that I put together of some value... if only to prove what a pain that Automation in .Net can be...!

If you do find a .Net solution to this on another Forum, please come back to let me know! I'd be very interested to learn of it...

Thanks!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 01-09-2004, 08:38 AM
tesko tesko is offline
Newcomer
 
Join Date: Jan 2004
Location: Ontario, Canada
Posts: 4
Default

Okay, this is what I've done to get around it.

It's not the best solution, but here goes. I've determined that I only receive exceptions on certain functions/properties. The 2 that I've found so far are:

oExcel.Visible and oWorkbooks.Add

The operation is performed, yet the exception is still thrown. So, to get around it, I put

"On Error Resume Next" before calling and "On Error GoTo 0" Afterwards to disable error suppression.

This has worked fine for my purposes.
Reply With Quote
  #6  
Old 01-09-2004, 08:49 AM
reboot's Avatar
rebootExcel 97 issue reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

I wasn't even aware that On Error still worked in vb.Net. Why aren't you using Try Catch?
Reply With Quote
  #7  
Old 01-09-2004, 08:52 AM
tesko tesko is offline
Newcomer
 
Join Date: Jan 2004
Location: Ontario, Canada
Posts: 4
Default

First time using VB.NET, wasn't aware of it.

Oh, and oWS.PrintPreview() also cause an exception, AFTER the printpreview has been performed.
Reply With Quote
  #8  
Old 01-09-2004, 09:02 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by reboot
I wasn't even aware that On Error still worked in vb.Net. Why aren't you using Try Catch?

"I wasn't even aware that OnErr0r still worked in vb.Net..." When I first read that, I read that wrong! LOL

Yeah, the old-schoool 'On Error Resume Next' etc. are all backwards-compatible.

But tesko, you should learn the Try..Catch..Finally statement. In your case, instead of:
Code:
On Error Resume Next oExcel.Visible = True On Error Resume 0
You would want to use:
Code:
Try oExcel.Visible = True Catch End Try
They are effectively the same thing, but you'll find the Try..End Try block more robust, espectially if you need to make use of the 'Finally' statement.

To learn more you can read here: http://msdn.microsoft.com/library/de...tchFinally.asp

Sorry that XL'97 in .Net is such a mess... Automation in .Net is quirky enough...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 01-09-2004 at 09:39 AM.
Reply With Quote
  #9  
Old 01-11-2004, 07:47 PM
MaggieMay77 MaggieMay77 is offline
Newcomer
 
Join Date: Jan 2004
Location: Australia
Posts: 4
Wink

I thought I was the only one with a client still on Exce 97!!

I'm a newbie to .NET... so bear with me...

I had a similar problem - I ended up having to use late binding. I've heard it slows things down a little - but at least it gets the job done . I've previously used the Excel Object Library 10 and 5 and they haven't worked in all the Excel versions. However, doing it this way works - I've tested it on Excel 2002 (xp) and Excel 97 (but nothing in-between yet).

****************************
Dim oExcel As Object
Dim oWorkbooks As Object
Dim oSheets As Object

oExcel = CreateObject("Excel.Application")

oWorkbooks = oExcel .Workbooks.Add
oSheets = oWorkbooks .Worksheets(1)

oExcel .Visible = True

'you get the picture
****************************

Of course you'd want to surround this in a try catch... one of the things I learnt the hard way was that when I was trying to save the spreadsheet, I'd get the nasty System.Runtime.InteropServices.COMException - as it turned out when I'd run the app in debug, the spreadsheet wouldn't save because it was still running in the background as "open for editing"... so in your try catch you may like to close the spreadsheet and quit Excel in the event that it bombs out!?

Hope this helps - you can get in touch with me if you need me to test on '97.

Maggie

PS: Believe it or not I got this from the .NET help (yes - my late Xmas miracle)
Reply With Quote
  #10  
Old 01-11-2004, 08:43 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Maggie. Welcome to the Forum

So .Net help says to use Late Binding in order to Automate XL 8.0? Cool. Glad to know that the help files can actually be *helpful* from time to time. I think that the .Net help files are sometimes "overdone" but overall I think are very good...

Late Binding will slow things up so slightly that you will never notice. Automation of Excel runs so slowly anyway that it's astounding. Because it is running out-of-process (that is, externally from Excel's memory address space) I find that Automated VB6 code runs some 30x slower than VBA Code running behind Excel directly.

I've not tested it with .Net, but with the COM Interop running in between, it can't be any better! So, I really would not worry about the late binding thing at all! If this helps XL 8.0 to be Automated via .Net, so be it!


Your InteropServices.COMException error sounds like it could be from the improper creation & release of your variables. This is tricky stuff when Automating in .Net, you really must break down everything so that you have only one new object created per line. Another way to look at it is that you would want only one period (".") per line of code. For example, in your code, there are two lines that are a problem:
Code:
Dim oExcel As Object Dim oWorkbooks As Object Dim oSheets As Object oExcel = CreateObject("Excel.Application") oWorkbooks = oExcel.Workbooks.Add ' <-- ** Problem ** oSheets = oWorkbooks.Worksheets(1) ' <-- ** Problem ** oExcel .Visible = True
The second of the two lines only has one period in it (technically) but that's only because it is using the .Item() property in default mode. Rewritten, these two lines will read:
Code:
oWorkbooks = oExcel.Workbooks.Add oSheets = oWorkbooks.Worksheets.Item(1)
Now both lines are a problem.

It's a pain, but you must rewrite your code as follows:
Code:
Dim oExcel As Object Dim oWorkbooks As Object Dim oSheets As Object Dim oWB As Object ' <-- ** Added ** Dim oWS As Object ' <-- ** Added ** oExcel = CreateObject("Excel.Application") oWorkbooks = oExcel.Workbooks ' <-- ** Changed ** oWB = oWorkbooks.Add ' <-- ** Added ** oSheets = oWB.Worksheets ' <-- ** Changed ** oWS = oSheets.Item(1) ' <-- ** Added ** oExcel.Visible = True
And then when done you must close up with:

Code:
NAR (oWS) oWB.Close NAR (oWB) NAR (oSheets) NAR (oWorkbooks) oExcel.Quit() NAR (oExcel)
Where the NAR Sub-routine is defined as
Code:
Private Sub NAR(ByVal o As Object) ' This sub cleanly releases the COM Automation Object. ' Source: http://support.microsoft.com/?kbid=317109 Try System.Runtime.InteropServices.Marshal.ReleaseComObject (o) Catch Finally o = Nothing End Try End Sub
For more info on this (unfortunately critical) nonsense, you can read more about it in the FAQ: http://www.xtremevbtalk.com/t129690.html

Thank you very much for the help on Excel '97, I don't have it myself and I think few with .Net are using it -- so you and Tesko are a rare resource!

Thanks ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 01-11-2004 at 10:09 PM.
Reply With Quote
  #11  
Old 01-12-2004, 05:17 AM
MaggieMay77 MaggieMay77 is offline
Newcomer
 
Join Date: Jan 2004
Location: Australia
Posts: 4
Thumbs up

Thanks Mike

I've just been cruising through the Automation tutorials - and wish I had found it sooner - would have saved myself hours of ripping my hair out!!

Thanks - this is a fab resource.
Maggie
Reply With Quote
  #12  
Old 01-30-2004, 01:22 PM
PeteFrizzell PeteFrizzell is offline
Newcomer
 
Join Date: Jan 2004
Posts: 2
Default Excel 97 does work with Object library 9.0

I have run into the problems described above and, after a fair bit of testing, have foundthe following:

- Connecting to Excel 97 using Excel Com Object library 10.0 wouldn't work. The try catch approach described by other response here might work - but I was getting some hanging processes, i.e. the marshal release object call wasn't killing the Excel process. This made the solution unworkable for me as I'd potential have large numbers of people uploading Excel spreadsheets throug the application.
- Connecting to Excel 97 using Excel Com Object library 9.0 worked fine. Obviously this could potentially cause a problem with more recent versions of Excel - but this wasn't a problem for me.

Cheers
Pete
Reply With Quote
  #13  
Old 01-30-2004, 01:27 PM
herilane's Avatar
herilaneExcel 97 issue herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Hello and welcome Pete

Excel's object libraries are backwards compatible. Use the lowest available version, and it will work fine with that version as well as all the later ones. So if you can use 8.0, your app is pretty much guaranteed to work with everything from xl97 onwards. That does mean you won't have access to the (few) new functions that have been introduced in the later versions, though.

As you've discovered, sometimes it works the other way too (a later object library works with an earlier version) but lower is safer.
Reply With Quote
  #14  
Old 01-30-2004, 01:37 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Pete,

Posts #9 and #10 above indicate that to Automate Excel '97 (XL 8.0) you should use late binding. (See Post #10 for an example.)

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 01-30-2004, 01:52 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hmm... you know what, Pete, I'm going to ammend this advice.

If you can successfully get it running by Binding to XL 8.0 then that is best. If you only have XL 9.0 available, but that also works, then fine.

In general, use Early Binding, if you can.

But if you "hit a wall", it is possible that you may need to use Late Binding instead... but stick to Early Binding for now, it looks like it should be fine...

Thanks for your info on this, any info we get on Excel '97 and .Net we find very helpful, for there are not many on this combination of platforms...

So, Thanks! ,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #16  
Old 01-30-2004, 02:26 PM
PeteFrizzell PeteFrizzell is offline
Newcomer
 
Join Date: Jan 2004
Posts: 2
Default

Thanks for the quick replies Mike_R and herilane,

I briefly looked at the late binding example - but hadn't got it working consistently (I'm pretty sure this was due to me not handling the objects properly rather than a problem with the approach).

However, as the early binding solution with 9.0 is working I'm going to stick with that for now (although it's good to know that I have an alternative approach should I run into more problems down the line).

Cheers
Pete
Reply With Quote
  #17  
Old 01-30-2004, 02:43 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Pete,

Even if the ultimate goal were to use Late Binding, this is typically done by using Early Binding right through all of the Development Process. Only at the very end would one remove the Reference to the Microsoft Excel Object Library and change all your declarations to be 'As Object'.

So, yes, absolutely stick with Early Binding all the way if you can and only switch over to Late Binding if you find that you have to... But I think you should be fine.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #18  
Old 04-05-2004, 04:25 PM
Barky Barky is offline
Newcomer
 
Join Date: Apr 2004
Posts: 1
Thumbs up You all are fabulous !!!

I know, that this thread is quite old in the meantime, but it was my only rescue after days of investigations and hours of thinking .
Up to now I don't have anything new to add, but I wanted at least to tell you, that your discussion still has very glad readers, noticing that they are not alone on this planet with their problem and(!) who get the relief, that their way of working was the correct one and simply the nice MS-Product managed to mix them up once more

So now I will work on this matter and hope, that I at least get a very Basic functionality running with this old combination. However my company is shortly(?!) before the great switch to Office XP, so I am afraid, I have to begin new anyway on the day, they manage to do this .

Be sure - I really will remember this Forum, and perhaps one day I can also help with the things I learned
Reply With Quote
  #19  
Old 07-14-2004, 08:46 AM
fherrera fherrera is offline
Freshman
 
Join Date: Jul 2004
Location: Edmonton, Alberta
Posts: 45
Default Late binding, eh..wha??

Hi everyone, i'm a long time reader second time poster

Hi, sorry to post to this old thread, but my questions are relating to it.

I, like most, that post consider themselves a newbie w/ .Net

The question is, i'm not exactly sure what late binding/early binding is. I have read some things about them and it sounds like early binding is when you declare what your object is in vb.net, but to do this you have to add the reference to the COM object. ie. the Excel 10.0 object library (office xp). Something like:

dim myxlapp as Excel.Application
and everything else is declared as Excel.something

Whereas early you don't define any references? and simply declare

dim myxlapp as object
set myxlApp=createobject("Excel.Application")

which i believe uses whatever is currently installed on the machine for an object library.

I'm creating a GUI for an excel spreadsheet and I'm not sure what versions of office the users will be running, but I figure if I make it run for 97 then i should be good for anythign newer. Unfortunately, I only have Office xp. My question then is, should I use late binding or can I download the Excel 97 object library.. 8.0 or 9.0 I'm not sure which and use that with early binding? I've got it working now but it only seems to work on my machine, using the Excel 10.0 library. I had attempted to compile it using the 5.0 library (i'm not sure what version of office that correlates too) but it didn't compile with early binding. At the time I didn't know about late binding, the book i'm reading just uses early binding.

The hard part is that it works for me but it doesn't work for the guy i'm making it for, and it's not an easy task to get him to test it. "Try this... How about now... How about now?" Not a great guess and test process

In any case, i'm loving this forum and thanks to everyone

Sorry for the long post.

Frank
Reply With Quote
  #20  
Old 07-14-2004, 09:08 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

You basically have Early vs. Late Binding correct. (Although you used the word "Early" for both cases, I think I know what you mean.) You should have a read of Herilane's Early vs. Late Binding Tutorial. It's written from the point of view of a VB 6.0 coder (not .Net) but the principles are exactly the same. Give it a read!

As for backwards compatibility, this will be tricky. Generally you would want to "Bind Low" and "Run High". What I mean by that is that you would want to add the Excel 8.0 Object Library to your project and thereafter you should have no trouble running on 8.0 or higher. (Do not go down to 5.0, it's way too low. I don't even like 8.0, to be honest.) You can, however, Bind High and Run Low, that is Bind in Excel 10. and then run on 8.0. If you don't use any of the newer commands only avail in 10.0, then you should have no problems. "Should" is the operative word here, for this is not recomended operating procedure, but I do find that this tends to work fine. Late Binding is a last resort. It is the way to make incompatible systems "compatible", but it makes development much more difficult, as everything is declared 'As Object' and so you have absolutely no IntelliSense to guide you while you are making your code.

Things get more difficult when you try to be compatible down to Excel '97 (Excel 8.0). It's buggy and quirky and I tend to not find it worth the effort. But for basic commands it works just fine. If you have buttons and controls on the Worksheet, however, I find Excel 8.0 to be a bit tricky. Not catestrophic, but sometimes an issue. There are a few Excel '97 Experts around here that can help you out if you get stuck.

Things get really hairy, however, in .Net. I personally think that developing for platforms below Excel 2003 (11.0) is v.tough. I have Excel 10.0, like you, and have some issues. Below 10.0 there isn't even any PIA's to download and no documentation... so it's a brave new world down there. People are doing it, but I don't have the guts for it. And since I cannot, personally, write routines as yet that are only 11.0+ compatible, well, I prefer to develop in VB 6.0. It just works. I do love the .Net language, however, but I don't find it easy when working with Excel.

If you persue .Net, be prepared for many deployment headaches on these older Legacy COM systems. Execution speed can be a serious issue as well, depending on what you are doing. If you can, I would do it in VB 6.0. Or even VBA.

-- Mike

(PS: See the Excel Automation via VB.Net Tutorial for more info...)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning control (Focus) to Word from Excel new2vba Word, PowerPoint, Outlook, and Other Office Products 6 02-19-2009 09:30 AM
Access Link to Excel Locks Excel File mshellhamer Database and Reporting 1 11-03-2003 12:45 PM
Excel Merged Cell / PasteSpecial Issue Serif Excel 1 07-29-2003 04:05 PM
Excel Issue sethindeed General 3 06-13-2001 01:01 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel 97 issue
Excel 97 issue
Excel 97 issue Excel 97 issue
Excel 97 issue
Excel 97 issue
Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue Excel 97 issue
Excel 97 issue
Excel 97 issue
 
Excel 97 issue
Excel 97 issue
 
-->