Solved

programmatically adding reference Microsoft Excel 11.0 or 12.0 Object Library

Posted on 2013-07-01
24
2,210 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
ID: 39292178
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
ID: 39292188
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
ID: 39292354
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39292360
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
ID: 39292454
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 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39292463
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
ID: 39292477
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
ID: 39292480
Yes, I know.  It's just more work to do in order to avoid the obvious solution.
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39292481
@imnorie,

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

Expert Comment

by:MacroShadow
ID: 39292487
@peter57r,

Couldn't agree more.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 67 total points
ID: 39292507
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 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39292558
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 39292599
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 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39292605
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
ID: 39295417
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 27

Assisted Solution

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

Author Comment

by:developingprogrammer
ID: 39295626
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 27

Assisted Solution

by:MacroShadow
MacroShadow earned 266 total points
ID: 39295639
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
ID: 39295902
> 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
ID: 39305453
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 27

Accepted Solution

by:
MacroShadow earned 266 total points
ID: 39305529
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
ID: 39343730
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
ID: 39343733
You wouldn't need that expensive package for this.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39343739
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

777 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