Solved

programmatically adding reference Microsoft Excel 11.0 or 12.0 Object Library

Posted on 2013-07-01
24
2,066 Views
Last Modified: 2013-07-21
hey guys, i'm in an environment where some people have excel 2003, some have 2007.

i'm using 2007 and i have a Microsoft Excel 12.0 Object Library reference in my Access application.

could yall give me a function on how to:
1) detect if the user is using Excel 2003 or 2007
2) add the appropriate Microsoft Excel Object Library

thanks guys!! = ))
0
Comment
Question by:developingprogrammer
  • 9
  • 6
  • 4
  • +2
24 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 67 total points
Comment Utility
Instead of using code to set the reference you could use late-binding.

Then you wouldn't need the Excel reference.

You would need to change a few things in your code.

For example, declare Excel objects like workbooks and worksheets as Object rather than Excel.Workbook/Excel.Worksheet.

You would also need to replace any Excel VBA constants, eg xlUp, with their values, or add your own declarations for them.

For example:
Const xlUp = -4162

Open in new window

0
 

Author Comment

by:developingprogrammer
Comment Utility
Yup I'm trying to avoid late binding that's why haha = ) is it possible?
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
If you distribute your app with a reference to the Microsoft Excel Object Library for 2003, Access should use a newer version if that is present on the user's machine.
However, the other way round it wouldn't work.

/gustav
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
Yes you can, but it is A LOT of trouble and I seriously doubt it's worth it.

Firstly:
For Excel 2007

    Click the Microsoft Office Button, and then click Excel Options.
    Click Trust Center.
    Click Trust Center Settings.
    Click Macro Settings.
    Click to select the Trust access to the VBA project object model check box.
    Click OK to close the Excel Options dialog box.

For Excel 2003

    On the Tools menu, point to Macro, and then click Security.
    In the Security dialog box, click the Trusted Sources tab.
    Select the Trust access to Visual Basic Project check box.
    Click OK.

Next add a reference to Microsoft Visual Basic for Applications Extensibility 5.3.

Now copy the following code to a standard module:
Option Explicit

Public Sub AddAppropriateReference()

    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As Reference
    Dim strOfficeVersion As String

    Set VBAEditor = Application.VBE
    Set vbProj = VBAEditor.ActiveVBProject

    ' Check if "Excel" is already added
    For Each chkRef In vbProj.References
        If chkRef.Name = "Excel" Then
            GoTo CleanUp
        End If
    Next

    Select Case GetExcelVersion
        Case "Excel 2003"
            strOfficeVersion = "Office11"
        Case "Excel 2007"
            strOfficeVersion = "Office12"
        Case "Excel 2010"
            strOfficeVersion = "Office14"
    End Select

    vbProj.References.AddFromFile "C:\Program Files\Microsoft Office\" & strOfficeVersion & "\EXCEL.EXE"    ' Change path to installed version
    MsgBox "Added reference to: " & chkRef.Name & vbCrLf & chkRef.Description & vbCrLf & chkRef.FullPath

CleanUp:
    Set vbProj = Nothing
    Set VBAEditor = Nothing

End Sub

Public Function GetExcelVersion() As String

    Dim oExcel As Object
    Dim Ver As String

    Set oExcel = CreateObject("Excel.Application")

    Select Case oExcel.Version
'        Case 5#
'            Ver = "Excel 5"
'        Case 7#
'            Ver = "Excel 95"
'        Case 8#
'            Ver = "Excel 97"
'        Case 9#
'            Ver = "Excel 2000"
'        Case 10#
'            Ver = "Excel 2002"
        Case 11#
            Ver = "Excel 2003"
        Case 12#
            Ver = "Excel 2007"
'        Case 14#
'            Ver = "Excel 2010"
'        Case Else
'            Ver = "Unknown version"
    End Select

    GetExcelVersion = Ver

    Set oExcel = Nothing

End Function

Open in new window


Running AddAppropriateReference will do what you want.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 67 total points
Comment Utility
And MacroShadow's code does require you to know where the Excel executable is stored, which might depend on the Windows version and the foibles of the machine configurer.

(all my Office programs are on my D drive)

As you have already been advised, late binding is the accepted solution for this issue.
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
peter57r

That's easy enough, just read the "path" key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe

Then you could also use the InStr function to return the installed version, without having to use a custom build function.

If the OP is interested I will give some sample code.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
You want to avoid a method, late-binding, which would solve the problem?
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 67 total points
Comment Utility
Yes, I know.  It's just more work to do in order to avoid the obvious solution.
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
@imnorie,

 Well, early binding does have it's advantages...
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
@peter57r,

Couldn't agree more.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 67 total points
Comment Utility
MacroShadow

What if it's not possible to change security settings to allow programmatic access to the VBE?

I've worked in a few places where that option has been locked down for security reasons.

PS Aren't the advantages that come with early-binding, eg Intellisense, only useful when developing?
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
What if it's not possible to change security settings to allow programmatic access to the VBE?
Then your screwed ;)

PS Aren't the advantages that come with early-binding, eg Intellisense, only useful when developing?
No. See this http://support.microsoft.com/kb/245115

Microsoft recommends early binding in almost all cases.

Early binding is the preferred method. It is the best performer because your application binds directly to the address of the function being called and there is no extra overhead in doing a run-time lookup. In terms of overall execution speed, it is at least twice as fast as late binding.

The advantages given to early binding make it the best choice whenever possible.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
Why all this trouble?

I just double-checked. Created a database in A2000 with a reference to Excel 9.0 on a WinXP machine. Save and exit.

Open in A2010 under Win7. Check References. Excel 14.0 is referenced. Not MISSING.

So:
If you distribute your app with a reference to the Microsoft Excel Object Library for 2003, Access will use a newer version if that is present on the user's machine.

/gustav
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
Why all this trouble?

Just for the fun ;)  and for sake of sharing knowledge...

Now, seriously if it really works (I haven't tried it but I do believe you) it is the best solution.
0
 

Author Comment

by:developingprogrammer
Comment Utility
whao guys, super duper cool stuff here. i need to read more in depth to respond to yall individually ya = )

but at the moment - gustav, i'm using Access 2007 and when i go to tools references i don't see Microsoft Excel Object Library for 2003, i only have Microsoft Excel Object Library for 2007. how can i reference to Microsoft Excel Object Library for 2003?

i think your solution would be the easiest for a newbie like me to implement! = )
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
You must have 2003 installed to add a reference.
0
 

Author Comment

by:developingprogrammer
Comment Utility
i see, thanks MacroShadow! and

1) are the coding differences using 2003 and 2007 excel reference?
2) how do i detect whether the reference is 2003 or 2007 reference? so that i can insert different code based on the 2003 and 2007 reference.

thanks guys!! = ))
0
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
Comment Utility
Just follow the instructions I posted before http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28173225.html#a39292360 and you're good to go.

Assuming that Office is installed at the default location.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
> You must have 2003 installed to add a reference

Yes, or you can go to a machine with Excel 2003 installed (and no Excel 2007) and open your app.
When you go to Tools, References, the reference to Excel 2007 object library will be marked MISSING. Remove that reference and add the Excel 2003 object library. Compile, save and exit. This is the file to distribute.
To confirm, open the file on your machine and check References. It will have changed the Excel reference to 2010 (14.0).

/gustav
0
 

Author Comment

by:developingprogrammer
Comment Utility
guys, thanks for all your help and wonderful solutions!! just reading them has taught me a lot! = ))

MacroShadow, you mentioned previously in your post

peter57r

That's easy enough, just read the "path" key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe

Then you could also use the InStr function to return the installed version, without having to use a custom build function.

If the OP is interested I will give some sample code.

if you don't mind, could you kindly give me the sample code for this? i'm probably going to go your route = )
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 266 total points
Comment Utility
Apply all instructions from here then use this code:

Option Explicit

Public Sub AddAppropriateReference()

    Dim VBAEditor As VBIDE.VBE
    Dim vbProj As VBIDE.VBProject
    Dim chkRef As Reference
    Dim strTemp As String

    Set VBAEditor = Application.VBE
    Set vbProj = VBAEditor.ActiveVBProject

    ' Check if "Excel" is already added
    For Each chkRef In vbProj.References
        If chkRef.Name = "Excel" Then
            GoTo CleanUp
        End If
    Next

    strTemp = RegKeyRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe\path")

    vbProj.References.AddFromFile strTemp & "\EXCEL.EXE"
    
    MsgBox "Added reference to: " & chkRef.Name & vbCrLf & chkRef.Description & vbCrLf & chkRef.FullPath

CleanUp:
    Set vbProj = Nothing
    Set VBAEditor = Nothing

End Sub

Function RegKeyRead(i_RegKey As String) As String

' Reads the value for the registry key i_RegKey
' If the key cannot be found, the return value is ""

    Dim myWS As Object

    On Error Resume Next

    'access Windows scripting
    Set myWS = CreateObject("WScript.Shell")

    'read key from registry
    RegKeyRead = myWS.RegRead(i_RegKey)

End Function

Open in new window

0
 

Author Comment

by:developingprogrammer
Comment Utility
guys thanks so much for your help!! i've bought the FMS Total Access Ultimate Suite and going to try and implement this using their Admin product. will let yall know how it goes should i run into any issues, but hopefully it's all good!! thanks for all your help and advice guys!! = ))
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You wouldn't need that expensive package for this.

/gustav
0
 

Author Comment

by:developingprogrammer
Comment Utility
Yup bitsqueezer did share with me a few other free products but I think right now I'm really new so I need the crutches and the support at the moment, so it's like getting a product instead of implementing it myself. They did all the implementing already. I also got vbWatchDog for global error handling and their email product as well. Just trying to really get this project off and running. I think if I have more experience and time I could learn to manually write all these, and I would love to just that now I don't have that resource at the moment, and y'all are already doing so much to help me!! = ))
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now