Understanding XL Ref in VB6 ..?? (vb6 XL various versons)

Topic

New Reply

I’m more than a little confused by how the XL object can be used by VB6..

I have Office XP developer’s edition installed and I also have XL 2000 (not the developer edition) installed.

When I go to add a reference to XL in VB I only see the 10.0 Object Library as being avail. Do you only get the object when you have the devloper’s edition? The reason that this came up is that I’m writing a app in VB6 to build an XL sheet and am having trouble with the Workbooks.OpenText method on user’s machines that only have XL 2k (it generates and address error in the vb runtime – runs fine on my XL XP machine).

If it turns out I have to use the XL 2K Object Library – how do these libraries get loaded?

It would be nice if I could just use my 10.0 Obj Lib and not have to worry about what the target user has.. what do I need to distribute with my app in order that it will run on a user’s machine so that they don’t have to go out and purchase the version of XL that I have?

Or if you have any other ideas I’m open..

TIA

Reply | Quote

Replies

You get the object libraries for the versions you have installed. If you’re looking for the Excel Object Library for version 9 and you have XL 2000 installed, it should be there, although you may have to browse for it. The Excel 9 object library is named Excel9.olb.

Reply | Quote

Thank you! I was able to find that lib.

Do you happen to know anything about this part of my question:

“It would be nice if I could just use my 10.0 Obj Lib and not have to worry about what the target user has.. what do I need to distribute with my app in order that it will run on a user’s machine so that they don’t have to go out and purchase the version of XL that I have?”

Reply | Quote

I’ve never tried distributing an Excel application, but the packaging and deployment wizard (or whatever it’s called in XP) should be able to package it for you and create a setup that can be installed on the target machine. It is an add-in in 2000 (and I assume in XP) and you can get to it from the VBE interface. The wizard will walk you through creating the setup.

Reply | Quote

Thanks again.

Yes, I know about the packaging wiz and was stuck on the notionthat since its not a true XP app (not using a macro) that I was in trouble. Your post got me to thinking that I could come up with a “hello world” app and distribute that along with my VB app to get the job done.

Whadya think?

Reply | Quote

VB also has the packaging and deployment wizard, and since XP is still based on VB6, it should work for either one.

I don’t know what you mean about not using a macro, though. The wizard doesn’t care what’s in the file, it just packages the app file the way you tell it to. There doesn’t have to be any code involved.

Reply | Quote

I guess I was confusing potential licensing issues with the ‘no macro” statement. After reading your response it looks like I’m tilting at windmills.

Thanks for your patience!

Reply | Quote

Now *I’m* confused. You can’t distribute the Excel executable, but you can distribute an Excel spreadsheet, along with the VB6 runtime, and I thought that was what you wanted to do, along with the libraries that supported it. A later version of Excel will open an earlier version’s worksheets but if you had code in your VB project that referenced a particular library version, you would need that library on the setup CD or you would have to write code to handle the broken references.

Reply | Quote

The issue you’re flirting with is versioning of dependencies and is a whole topic in itself, but for your case, you need to code your app against the version of Excel which the users have. Don’t try and package Excel with your application or you’ll have lawsuits from Redmond coming your way

If you are using methods that are common across xl9 and xl10, or even older versions, then instead of referencing the library, you could use CreateObject(“Excel.Application”) and that will give you an application instance in whatever version the user has. You just have to test your code against xl9 and xl10 to make sure the methods you are using work in both. For testing purposes, you could change your code to CreateObject(“Excel.Application.9”) and CreateObject(“Excel.Application.10”) temporarily, or using conditional compilation.

Reply | Quote

Are you saying that even though I have Excel XP Developer Edition that I can’t package a simple spreadsheet along with my VB app and install them on a user’s machine (legally)? Or it just won’t work? Or Both?

I’ve been trying to make my app compatible all the way back to xl8 and now seems to work ok. I’m still using the early binding approach and switching between references of xl8 and xl10. Am I heading for trouble using early binding instead of late?

Reply | Quote

You can package the spreadsheet, but you can’t include the Excel executable. If you want to use automation, early binding makes it much easier.

Reply | Quote

Yes. That has been my understanding all along and that is my current approach.

However, I’ve been getting an error message from the XL Packaging Wiz: WIth “offWH” in the title bar I get a message box with “Error In Loading DLL”. I’ve re-installed the product but am still getting the same thing.

I don’t suppose you have any idea as to what’s going on? I’ve searched all of msft’s web site and no help there.

If I click the OK button it seems to build a distribution set but at this point I’ve not tested it. I’ll need a test machine to do that and I don’t have one avail yet.

Reply | Quote

Early binding makes programming easier, but it makes your app depend on the referencd binaries being on the machine.

If your users have anything from xl8 to xl10 on their machines, then your going to have to use late binding and CreateObject, and make sure your code works on all 3 versions.

Reply | Quote

Are you missing the fact that I will be distributing the app using the Packaging Wizard? Do you still maintain that your statements are factual? If so please explain why..

Also, based on some testing where I’ve sent only the vb program (with early binding to xl 10) to a user that has some machines with xl 8 and xl 9 and they do in fact work. At first they didin’t due to some features that are only avail in xl xp but when I removed them they worked. So, I am more than a little confused by all of this.

Reply | Quote

The fact that you are using the packaging wizard makes no difference at all. The packaging wizard include files that your project is dependent on, but only up to a limit. After all, your project depends on Windows itself, but Windows doesn’t get packaged with it. This basically means Excel doesn’t get packaged with your project, and you wouldn’t want it to, since you’d be distributing one of Microsoft’s major products for free with your app.

The reason that your project works with xl8 and 9 when packaged early bound against xl10, is because MS have made the Excel libraries binary compatible, and that’s one thing I didn’t think of when I posted previously, apologies for that. This basically means that the xl8,9 and 10 libraries use the same CLSIDs (Classs identifiers) and so they will look the same to your VB project when it’s running on a machine with any of those versions installed, the only difference being that the later versions will have some added classes and methods. So, the only thing that will fail is when you try and use methods available in 10 that aren’t in 8 or 9, like you say.

Hope this makes it a bit clearer.

Reply | Quote

I think we’re on the same page now as to the early binding and compatability issues with versions 8,9 and 10. I’m writing to the “lowest common denominator”, ie version 8.

I don’t think we’re on the same page with the packaging wizard as I must not understand what it really does and does not do for you..

I’ve about finished setting up a laptop with xl 97 and a package named “goback” so that I can set up a “virgin” state on the laptop; install my app and test and restore the machine bak to the virgin state. So that I can really get a handle on all of this..

Reply | Quote

The packaging wizard does the following:-

1) Compiles your app
2) Automatically detects file dependencies of your app (up to a limit, and I’m not sure how that limit is discerned). Part of the wizard shows you the dependencies it’s recognized and allows you to add/remove items.
3) Creates CAB file(s) which contain your app and it’s dependencies as found in the previous step
4) Creates a setup program and other scripts needed for installation

So as you can see, the packaging wizard does not help you with versions of xl, etc. All it sees is that your app depends on a type library called ‘Excel 10’ (or whatever the exact name is), and I’d say it probably doesn’t list it as a dependency to be packaged with your app by default (I imagine MS have made the wizard smart enough not to try and package it’s commercial apps by default, but you never know ).

Reply | Quote

Yes, I Understand that NOW..

I was under the delusion that the “Packaging Wizard” would allow a user to run an Excel App via a dll without having to purchase Excel. It has finally dawned on me that is what all of the posters have been trying to get me to realize.

Now having the light bulb on I really see no use for the “Packaging Wizard” and feel like I’ve wasted my money.

I’m still going to write the app in VB6 using early binding and code it to the LCD of XL 97.. Hopefully that will be safe..

Reply | Quote

I think early binding to xl97 is probably the best approach. That’s usually the methodology I’d employ; early bind to the earliest version expected to be on user’s machines.

Reply | Quote

Early binding should fail on a machine that makes a call to a nonexistent type library (e.g., “Unable to open macro storage”). If you have set up your code so that you can detect and change the referenced library before it is called, that’s slick, and I think a lot of us would like to see it.

(I don’t see why using the packaging wizard would make any difference, unless you package the type libraries for XP, which I’m not sure would be a good idea. Or legal.)

Reply | Quote

No, I haven’t written code that detects and changes the referenced lib.

My app is a general purpose text file loader and sits between a series of programs that generate a variety of tab delimited files. I intend to make a simple “report writer” with it wherein the program that spits out the tab delimited file also spits out another file describing the layout. My app will then read both and present the user with a series of simple options and then will build the xl sheet The user can change the options in my app and then click a button and those changes will then be made on the same sheet. A simpler approach than using the built in wizards, etc. (This if for users with absolutely NO experience using spreadsheets).

The only tricky bit of code I’ve written is when the user clicks the “rebuild” button I check to see if xl is still running and if it is – is the same sheet still there. If so all is well and I continue. If the sheet is gone then I add another and rebuild the sheet. If XL is gone I re-execute it, add a workbood and rebuild the sheet. The oddest part is if the user killed the sheet and left XL running with no workbook I was able to detect that but when I added a new workbook to the application it did not show even though I made it visible. It looks as if XL doesn’t actually paint the window for the worksheet.. I was able to kludge a solution for that one but it was/is very strange behavior.

Reply | Quote

I do have code that detects if referenes are mssing but I don’t think you can change references on the fly. I wasn’t smart enough to write this, I got it from John Green’s Excel 2000 VBA book (page 319) although I did modify it slightly.

Public Function ExtReferencesValid() As Boolean
  Dim objRef As Object, stDescn As String

  ExtReferencesValid = True
  For Each objRef In ThisWorkbook.VBProject.References
'    Debug.Print "Checking references for: " & objRef.Name
    If objRef.IsBroken Then
      'some broken links don't have descriptions, ignore
      On Error Resume Next
      stDescn = ""
      stDescn = objRef.description
      On Error GoTo 0

      'display message to install missing item
      ExtReferencesValid = False
      MsgBox "Missing reference to: " & vbCrLf & "Name: " & _
        stDescn & vbCrLf & "Path: " & objRef.FullPath & vbCrLf & _
        "Please re-install this file. The model will not work without it", _
        vbCritical + vbOKOnly, "Missing Required File"
    End If
  Next objRef
    
End Function

I call this in my workbook_open event.

Deb

Reply | Quote

That’s a really kewl routine! XL’s object model is bewildering sometimes (at least to me)!! The thing is HUGE .. its like a 500 lb marshmallow..

Thanks!

Reply | Quote