lodem

1. Is this possible in VB ?
(there is no point in giving a user the option to read data from an exel file if Excel is not installed on his system)

2. I have added a reference to Excel 10.0 What if a user has version 9.0 installed ? Will that result in errors ?

rpoy

Quote:

---

Originally Posted by lodem
1. Is this possible in VB ?
(there is no point in giving a user the option to read data from an exel file if Excel is not installed on his system)

2. I have added a reference to Excel 10.0 What if a user has version 9.0 installed ? Will that result in errors ?

---

1. As far as determining if a program is available on a system you would be better off checking this with your installation program like Installshield. Another way is to go through the registry to find it.
Maybe someone else has some other ideas...


2. I have run into this many many times before! What I did to solve this issue is use late binding.

So...
Instead of using this...
Dim xlsApp As Excel.Application
Dim xlsWkbk As Excel.Workbook
Dim xlsSheet As Excel.Worksheet

Do this...
Dim xlsApp As Object
Dim xlsWkbk As Object
Dim xlsSheet As Object

Usually I will use early binding to take advantage of the quick dropdowns but then change my variables when it time to compile. If you don't do this you WILL get errors especially if your development machine has Excel 2000 and your target machine has Excel 97.

You could also reference the different versions of the excel ojbects in VB, but if you only have Excel 2000 you need to get those Excel*.olb ( i think).

Hope that helps...

Mike Rosenblum

rpoy,

That is an excellent answer. A very solid way to handle it.

So I was wondering if you could help me here. I'm not a VB guy, I'm pretty much VBA-only and so I'm real bad at Automation, but I'm learning.... Here's my Q:

When you are done with IntelliSense you change all your Dims to "As Object" and then, I guess, you remove the Reference to the "Excel 10.0 Library" (or 9.0, or 8.0 depending). Right?

My question, then, is this: how does running it later know how to use the code at all? (There's no Library!) Or does using Set xlApp = CreateObject("Excel.Application") load the library sort-of automatically?

-- Mike

lodem

Quote:

---

Originally Posted by Mike_R
rpoy,

That is an excellent answer. A very solid way to handle it.

So I was wondering if you could help me here. I'm not a VB guy, I'm pretty much VBA-only and so I'm real bad at Automation, but I'm learning.... Here's my Q:

When you are done with IntelliSense you change all your Dims to "As Object" and then, I guess, you remove the Reference to the "Excel 10.0 Library" (or 9.0, or 8.0 depending). Right?

My question, then, is this: how does running it later know how to use the code at all? (There's no Library!) Or does using Set xlApp = CreateObject("Excel.Application") load the library sort-of automatically?

-- Mike

---

i'll check, and let you know

rpoy

Hi Mike!

As far as removing the reference goes, I usually just leave it so that I can modify the code quickly later. I haven’t got the entire IntelliSense memorized so I use it constantly. As a result I just leave the reference. It may make the program a bit bigger, but the programs that I write are usually so small compared to Word or Excel that I don't think it matters. Besides disk space it cheap right?

The answer to your question: As I understand it, when you define a variable as an "object" in VB it sets up a big space in memory to accept any object. Once you create an object like:
Set xlApp = CreateObject("Excel.Application")
Part of the magic of VB is that it goes out and searches for the library that best matches your object that you want. The downside is that the searches take up time. Thus using late binding makes your programs run slower. Oh and you don't get the nice IntelliSense which is also slower on the development side (at least for me)!

Mike Rosenblum

Quote:

---

Originally Posted by rpoy
Hi Mike!...

... Once you create an object like:
Set xlApp = CreateObject("Excel.Application")
Part of the magic of VB is that it goes out and searches for the library that best matches your object that you want.

---

Ahh! The magic, yes... That is the part that I never quite understood... I thank you much. I suspected this, but I wasn't sure.

Quote:

---

Originally Posted by rpoy
The downside is that the searches take up time. Thus using late binding makes your programs run slower. Oh and you don't get the nice IntelliSense which is also slower on the development side (at least for me)!

---

The truth is that even with your explanation, I still don't "get" late binding. I would still rather use early binding, but load an 8.0 Library so that it is valid on 8.0, 9.0 and 10.0. Admittedly, I might have to find an older machine before doing the final compile, but I think that never losing the "Bind" and the IntelliSense is my preference.

Or does this not work? I would assume that a program bound to 8.0 will run on a 9.0 or 10.0 machine... or is this not a valid assumption?

-- Mike

rpoy

[/QUOTEPOST] The truth is that even with your explanation, I still don't "get" late binding. I would still rather use early binding, but load an 8.0 Library so that it is valid on 8.0, 9.0 and 10.0. Admittedly, I might have to find an older machine before doing the final compile, but I think that never losing the "Bind" and the IntelliSense is my preference.

Or does this not work? I would assume that a program bound to 8.0 will run on a 9.0 or 10.0 machine... or is this not a valid assumption?

-- Mike[/QUOTEPOST]

Mike!

Sorry - I am a bit confusing! It seems that Im in a constant state of confusion!

I can't give you a specific example, but Something in the back of my brain is screaming about using old library references. I think I would try to avoid it if possible. At the most I would only go back one version.

I think this all gets back to your development machine and upgrading. If you are going to just use the oldest library, then your development machine would have Win95 and Office 95!! YIKES! You would have to be very careful when you upgrade anything for fear of switching out a library that might effect a program that you have written in the past.

I think The Best Way is to have the development machine be identical to the target machine so that you can use early binding on everything.

BUT, In the long run it takes lots of time and can be a HUGE pain to keep track of. Especially when patches come out weekly. The easy way to make sure that your program will run on a target system, is to make use of late binding for the external references that will NOT be installed with your program. I guess it all depends on your boss and/or how much the customer wants to spend.

mark007

Further to this - to explain the magic. Create object looks in the registry under HKEY_CLASSES_ROOT for the class that you have specified. It then uses the curVer to determine the version and then uses the class ID to find the relevent files to use by looking at the InprocServer32 key under the CLSID. The CLSID is a unique 128bit number.

On a side note to this:
It is similar to file extensions really. Windows looks up the extension under HKEY_CLASSES_ROOT. This then provides the filename. It then looks up the filename and finds alsorts of interesting things such as context menu handlers and icon handlers which are possible to do through VB but very difficult! I've got a book on it and may have a go at some point

I've never had a problem with this approach.

The big problem with using the intellisense with a reference and but then late binding and removing the reference is that you have to remember to define all the constants yourselves and it's easy to miss one!

Mike Rosenblum

Wow, that is awesome. You really understand the innards here.

Philosophically, though, I fail to see any advantage to Late Binding. It still looks to me that Early Binding to the lowest-Library number that you are willing to tolerate would be the best way to go, no?

Why drop the IntelliSense, even if only at the very end, and, as you mention, have to replace all the constant values? What advantages or potential advantages make it worth this hassle?

The only one I can think of is that you could be working on a 10.0 machine and may not have an 8.0 or 9.0 available to compile in... but I think I'd rather hunt one down for the final compile than to start changing the code from Early Binding to Late.

Is there some other reason to do Late Binding?

-- Mike

Legend

I thought the Excel type libraries are backwards-compatable-with-added-functionallity.

Ie - if the target machine is using an earlier version, then as long as you avoid functions that the previous type library wouldn't have, then there aren't too many problems.

AddressOf is probably the only function I can think of right-now that could cause problems, but what other issues are there? :confused

Mike Rosenblum

Legend, you are 100% correct. Good pickup.

I have two machines, one on XL10, where I do most of my work, and another on XL9. As long as I don't use some new XL10 command, anything I use (and referencing the XL10 library) will run in XL9. So you are right.

To test what happens, I made a new Workbook in XL 10 with the Micorsoft Excel 10.0 Object Library and then moved it to the XL9 machine. Checking the Refererences there it automatically changed the References to be the Microsoft Excel 9.0 Object Library, automatically.

So, yep, you are right, no need to "hunt down an older machine" as I had put it... Although it could still be hard to know if you accidentally used a new command until you load it on an older machine. I occationally do this, using a new XL10 command and then it complains when on my XL9 machine. Though it's rare because there weren't many changes between XL9 and XL10. I think there were more between XL8 and XL9.

So now I REALLY wonder what the value of Late Binding is? Anyone out there with any good arguements in favor of Late Binding?

-- Mike

mark007

I only use it for scripting where early binding is obviously not possible.

The only other advantage of late binding is that you can generate a trappable error if no version of excel at all is installed on the PC rather than just getting a runtime error that crashes the prog which an end user may not understand as being because they don't have excel installed. You can instead output a friendly or unfriendly error message indicating that if they had bothered to read any of the documentation you put many hours into writing then they would know that Excel is required. They are therefore not worthy of your software which will now uninstall itself! Or something like that

So in summary it's got to be early all the way for me!