Problem using Excel 2002 vs Excel 2000

[h20168]

I have been using a reference to the Excel 9.0 object library (Excel9.olb) file to programmatically export a mshflexgrid to a excel format report from my VB 6.0 program in windows 2000. I have seen that my installation folder has Excel9.olb file but is not copied to the directory where the executable is installed, which is ok or actually better.

However, recently, i got windows xp with office 2002 (office xp) and once I compile the program and try to run in a pc where we have windows 2000 with office 2000, my program crashes when I try to use a feature using excel object library. Also, I have noticed that instead of using something similar to Excel9.olb file (about 620 kb), the program is now referencing to the actuall application Excel.exe file which is about 8 MB. On the top of that, this Excel.exe is copied to the installation directory.

I have tried to do some search around in microsoft. Anybody experienced similar problem and what possible solution there might be?

Would appreciate any input.

[h20168]

I did find something in the forum that I did not look before. Sorry about that. It looks like Excel 10.0 Object Library (Excel.exe) is not backward compatible with Excel 2000 or 97 and therefore, was not able to execute Excel.Application in early binding in pc with Excel 2000. So, I instead used CreateObject("Excel.Application") and now it is working fine. However, why do I still need to keep reference to the Excel 10.0 library? It is just too big and also, it is copied to the app path upon installation.

Any comments, suggestions?

Thank you,

[WorkHorse]

The Excel library has info on the whole Excel class--properties, methods, how they are organized, etc. When you set a reference to the library it is exposed, so you get the InelliSense completers and you can declare variables as the objects in the Excel class ("Excel.Application" "cells" "row" etc.) With the reference set, VB will use early binding, which means that when you compile the code it gets the class object info from the library. When you use late binding, you want the application to get the class info from the library on the client at run time. But, when you remove the reference to the library, VB no longer knows what the heck a "cell" or a "row" or an "Excel.Application" is. You can no longer declare variables as an object in the library, because those objects are not exposed and VB has no way of knowing what they are. Instead, you must declare everything as "Object". An "Object" is like a Variant--it can be any kind of object. The application will figure out what those objects are at run time when it checks the client library. So replace all declarations to Excel objects to "As Object". Now you can remove the reference to the Excel library and the code can compile OK. If you want, you can keep a reverence to the library to get the IntelliSense and remove it before compiling.

Here is a nice article on early vs. late binding:

http://www.dicks-clicks.com/excel/olBinding.htm

Whether you use early or late binding, you NOT include the library in the installation. If you use late binding, the application will get the library info at run time. If you use early binding, the info necessary from the library is retrieved when the code compiles and then I no longer needed. The P&D Wizard will include the library file in the installation package and may warn you that removing it may cause your app to not work, but you should remove the reference anyway. Here is a Microsoft article on the subject:

http://support.microsoft.com/default...NoWebContent=1

[h20168]

Thank you for your reply. It was good to look at the Microsoft knowledge base article on the Excel type library being copied to the installation folder. Just to try, I deleted the Excel.exe file after installation from the installation folder and I was able to create excel reports from my VB app. However, one thing to note is that I compile my VB in Windows XP with Office XP, which I believe is Excel 10 Object library. My clients may have Win XP or 2000 and Office XP or 2000 or even 97. Now, by using the late binding and creating version independent code, the program still works. But, it does not open once it is created. When I open excel app first and try opening from within then it works but I can't just double click the saved file and open it. Why? Before I deleted the Excel.exe from the installation folder, double click would open the exported excel file.

On the same note, even though I am using the late binding, I can't compile without reference to the Excel 10 Object library. So, I am leaving the reference in there while compilation.

Thank you again for any comments/feedback.

[WorkHorse]

You need to compile without the reference. Otherwise info from teh Excel 10 class is getting built into you code. You can compile without the refernce. Try looking at the end of the other link I posted where it has a checklist for converting early binding to late binding. You have to change all of your declaratinos to objects. Anything declared as Workbook, Sheet, Cell, Column, etc. must be changed to be declared "As Object". Without the reference, any variables declared as Excel objects should cause a compile error saying something like "User Type Not Defined". All of those need to be changed to "As Object". Also look at the other items in the checklist and make sure all of those changes should be made. Then you can compile without the reference so that you app will be getting all of the correct object libarby info from the client instead of your reference.

[h20168]

The part that is sad is that I do need to reference to the Excel Object Library because I am using a lot of Excel constants to define my parameters and without the reference, the program will not compile. I did change the excel objects like worksheet, cell, range to objects but I can't do the same for the excel constants. Therefore, I am setting reference to the Excel 10.0 object library. It compiles fine and runs fine even in the machine with office 2000 because of late binding. As you have mentioned, I uncheck the Excel.exe to be installed and it is working fine. Excel.exe is not copied to the installation directory. However, still the same problem exists, once I create Excel reports, export and save it in machines with office 2000, it hangs and can't open. If I open Excel 2000 first, then I can open it. No such problems in machine with Office 2002/XP.

I have another question, we are about to get Office 2003. I am going to check on the microsoft knowledge base but do you think this version will resolve the issue above.

Thank you.