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
  #21  
Old 07-14-2004, 10:33 AM
fherrera fherrera is offline
Freshman
 
Join Date: Jul 2004
Location: Edmonton, Alberta
Posts: 45
Default


Thanks for the prompt reply Mike. I have checked the Tutotial I forgot to mention, it was very informative as well. That, the msdn website, this thread and some experts exchange question where my main sources of information

Currently, the scope of using Excel will be to read the cells from several worksheets, modify these cells later on, and run an already existing macro. I think that's about as simple as it can get (well the running the macro might not be totally simple).

"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."

I'm afraid I don't understand this. I thought the libraries where not backwards compatible?

Also, I guess this means it is possible to find the 8.0 Excel Object Library on the internet and somehow register it on my computer and reference that in my VB.Net project?

Some further clarification about late binding: the program will automatically look for any Excel COM object on the current computer it's running on? If for example Office wasn't installed... i'd get an error, correct?
But, if I include it as a reference and use early binding, the .DLL file or (ocx of whichever it is) will be included in my project distribution so there is not chance of it not being found? (i'm not sure if this is correct, since i'm distributing it w/ the 10.0 libraries and if Office 97 -> 8.0 Library is installed it still doesn't work).

Thank again, I haven't had a chance to read the binding article, but i'll do that soon

Frank
Reply With Quote
  #22  
Old 07-14-2004, 04:57 PM
MaggieMay77 MaggieMay77 is offline
Newcomer
 
Join Date: Jan 2004
Location: Australia
Posts: 4
Default

>> You basically have Early vs. Late Binding correct. (Although you used the ...

Hi there!

I developed for Excel 97 on win 98 using late binding - not necessarily recommended - but the only way I could get the cow to work!!! I got around the lack of Intellisense by declaring my early bound AND late bound objects. When I was developing, I just commented out the late bound ones. And vice versa when testing. Painful, but helpful.

Lets just say I'd never go out of my way to develop for early Excel with .Net again. But sometimes you can't avoid it right? So I'll keep checking this post to help you Frank. I soooo know what you're in for!!!

Cheers Maggie.
Reply With Quote
  #23  
Old 07-14-2004, 07:19 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

Quote:
Originally Posted by fherrera
Currently, the scope of using Excel will be to read the cells from several worksheets, modify these cells later on, and run an already existing macro. I think that's about as simple as it can get (well the running the macro might not be totally simple).
All the more reason to go with VBA or VB 6.0. Why complicate your life? .Net is fantastic... but in my opinion will make a simple project complicated. But if you have the time to invest and learn it, then go for it!

Quote:
"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." I'm afraid I don't understand this. I thought the libraries where not backwards compatible?
Not true. Later versions, say 10.0, have extra IDispatch protocols that allow them to run on earlier systems. 10.0 will have 9.0 and 8.0 protocols on them. This allows you to make a Workbook in 10.0 and give it to your buddy who is on 9.0 and it will still run. I will say that this is not recommended procedure, but if you avoid using advanced commands that did not exist when Excel 8.0 and 9.0 were launched, then you should be fine, at least for a VB6 EXE you would be.

But for VB.Net, everything is crazy. Development on Excel 11.0 is smoothest. 10.0 is ok, but you have to download the PIA's and I still don't think it's as easy. Below 10.0 it's undocumented, and as Maggie May points out, you may have to go to late binding to get it to run smoothly. 'Option Strict' kinda goes out the window once you do that, but to be honest the incessant use of CType() almost everywhere can really drive you half-batty when developing, so I can almost understand if someone turned 'Option Strict Off' (although I don't).


Quote:
Also, I guess this means it is possible to find the 8.0 Excel Object Library on the internet and somehow register it on my computer and reference that in my VB.Net project?
You know, people bring this up as a theoretical possibility, but I've never seen it actually done. In theory you should be able to import the correct .OLB and do what you are suggesting, but in practice I've not seen it done. More typically, you would hunt down a PC with 8.0 on it and compile it there. You would need to install .Net on that machine of course to do it.

Quote:
Some further clarification about late binding: the program will automatically look for any Excel COM object on the current computer it's running on? If for example Office wasn't installed... i'd get an error, correct?
Using Late Binding means:
Code:
Dim oXL as Object = CreateObject("Excel.Application")
And yes, it would search for whatever version of Excel.Application was installed on that machine, be-it 8.0, 9.0, 10.0 or 11.0. This is the sure-fire way to ensure compatibility. On the other hand, there are downsides (it's tough to develop without IntelliSense) and I would almost never recommend this if developing in VB6. However, if you want compatibility down to Excel 8.0 and are doing this in .Net, then, yeah, you will likely need to go with Late Binding.


Quote:
But, if I include it as a reference and use early binding, the .DLL file or (ocx of whichever it is) will be included in my project distribution so there is not chance of it not being found? (i'm not sure if this is correct, since i'm distributing it w/ the 10.0 libraries and if Office 97 -> 8.0 Library is installed it still doesn't work).
Normally Early Binding does not permit for cross-versioning like this. But with MS Office Applications it does work because they have Extra IDispatch's so that Excel 10.0 knows how to communicate with Excel 9.0 and 8.0. With .Net however, you get into COM Interop issues below Excel 10.0 that have nothing to do with Early vs. Late Binding per se. There is just no PIA's available for 9.0 and below and no documentation. Late binding seems to be the way to go when you need to go below 10.0. But only your own experimentation (and head-banging) will confirm or deny these thoughts. I suggest that you make a very small, simple App first and then test deployment on the systems that you are thinking about. You will likely see right away if you will need to use Late Binding or take other measures to get this done. You would not want to develop too far only to find out that you need to use a completely different technique...

But, again, VB6 is a lot easier for controlling Excel. I don't mean to dissuade you from using .Net, for I applaud your efforts, but 98-99% of the caveats in this thread disappear when using VB6...

-- 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; 07-15-2004 at 09:43 AM.
Reply With Quote
  #24  
Old 07-15-2004, 12:16 AM
fherrera fherrera is offline
Freshman
 
Join Date: Jul 2004
Location: Edmonton, Alberta
Posts: 45
Talking

Thank's for the detailed reply! Wow!

I would much prefer to do this all straight in VBA as this is what my background is. (By background I mean i've been pretty much doing it full time for the last 3 months and I figure it'd be easier to jump onto VB.Net than to C# since i've been doing all this VBA)

In any case, it's not my choice to have a .Net gui so I humbly nod and smile and do what i'm told as I try to soak up as much learning about Vb.Net as I can.

Using early binding to develop the code then removing it later on helps with the intellisense (I read that somewhere, in the tutorial I believe . Which was pure serendipity as early binding was the only way I knew how to accomplish this.

I have accomplished the same thing (program works as it did when I used early binding and hopefully now it will do so on other Excel vXX running computers) with late binding and for me it works... I have yet to see if it works on a users computer running Excel 97, I'm crossing my fingers.

The last question I have deals with Try and Catch. Specifically, catching the error that would occur in case the program is run on a machine without excel installed. I don't have a machine to test this on (without Office installed), so I'm going to go on faith and that's why I'm looking for some validation. Plus, the fact that whenever I build and run the current debug version and it crashes, I have a bunch of runaway Excel.exe processes running around

So would something like this be correct: (syntax may be incorrect as I don't have the IDE available at the moment and my memory isn't too great with syntax)

Code:
Try
'code that handles excel automation
Catch
' error occurs when trying to create excel object...which means it wasn't created
' so no garbage collection is needed?
End Try

Once again, I can't thank you enouph for the very informative replies!

Frank
Reply With Quote
  #25  
Old 07-15-2004, 10:23 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:
Using early binding to develop the code then removing it later on helps with the intellisense (I read that somewhere, in the tutorial I believe . Which was pure serendipity as early binding was the only way I knew how to accomplish this.
This is absolutely, 100% the correct approach. I do find, however, as a practical matter, that it is subsequently hard to maintain...

Let's say that you delelop using Early Binding:

Before deployment, you would make a copy and strip all the hard typing such as 'As Excel.Workbook', 'As Excel.Worksheet', etc. and replace them all with 'As Object' and remove the Reference to the 'Microsoft Excel Object Model'. This takes a long time. But you do it, and it works.

But then you need to make a small modification to your code. A small addition, or debugging. No big deal, right? Hmmm.... but which version are you going to modify? You can either modify the original, and have IntelliSense avail. while you make the changes, but this means that when you are done that you will have to go through the entire process of removing all the hard-typed variables all over again, just for one quick, little change. So, more likely, you'll just want to change the Late-Bound version...

But once you start doing that, the Late Bound version no longer resembles the Early-Bound version... Basically, once you change over to Late Binding and the deploy, that's it; you're going to be delevoping as Late Bound from that point onward. So this is why it's worth avoiding... if you can!! You may not be able to avoid it here, esp. if you need to be backwards-compatible to early versions of Excel. But using Early Binding for your initial development at least will be essential; trying to understand the Excel Object Model without it can be very hard.

As for running "Error-Free" code, I would suggest wrapping your entire routine within a Try..Catch..Finally block, something that looks like this:
Code:
Dim oXL As Object Try oXL = CreateObject("Excel.Application") ' Your code goes here. ' Your code goes here. ' Your code goes here. ' Your code goes here. Catch ex As Exception MessageBox.Show ("Doh! Something went wrong...") Finally If Not oXL Is Nothing Then With CType(oXL, Excel.Application) .DisplayAlerts = False .Quit() End With oXL = Nothing End If End Try
This way if you have an error, it will report it, and then the Finally section will kick in last (always) unloading your Excel Application if necessary...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #26  
Old 07-15-2004, 10:38 AM
fherrera fherrera is offline
Freshman
 
Join Date: Jul 2004
Location: Edmonton, Alberta
Posts: 45
Default

Thanks again Mike! Very informative.

Just like to comment that I've been using the Excel model for the last 3 months fairly indepth. The other method I was pondering, was to just use the VBE in Excel to work on the automation code. As long as you use worksheets and don't assume the default and such it should be fine to copy and paste it over to the VB.NEt project. Then you just pad the top with the declarations and the bottom with the garbage collection. It's a rudimentary Intellisense, but you can test the code right away and such. It's what i'm used too

Thanks again!

Frank
Reply With Quote
  #27  
Old 07-15-2004, 11: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

Oh, without a doubt! I do the same, believe me. Once you paste it into the .Net IDE it will yell and complain to you until you get it correctly converted over, so it's hard to mess up this way.

Good job... And good luck!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #28  
Old 07-15-2004, 03:01 PM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

This thread starts to look like a book, but was a very useful read for me

(Ok, one day i will install that .Net software that is collecting dust at the moment in one of my closets )
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 10:30 AM
Access Link to Excel Locks Excel File mshellhamer Database and Reporting 1 11-03-2003 01:45 PM
Excel Merged Cell / PasteSpecial Issue Serif Excel 1 07-29-2003 05:05 PM
Excel Issue sethindeed General 3 06-13-2001 02: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
 
-->