Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2796
  • Last Modified:

programmatically adding reference Microsoft Excel 11.0 or 12.0 Object Library

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
developingprogrammer
Asked:
developingprogrammer
  • 9
  • 6
  • 4
  • +2
15 Solutions
 
NorieCommented:
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
 
developingprogrammerAuthor Commented:
Yup I'm trying to avoid late binding that's why haha = ) is it possible?
0
 
Gustav BrockCIOCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MacroShadowCommented:
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
 
peter57rCommented:
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
 
MacroShadowCommented:
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
 
NorieCommented:
You want to avoid a method, late-binding, which would solve the problem?
0
 
peter57rCommented:
Yes, I know.  It's just more work to do in order to avoid the obvious solution.
0
 
MacroShadowCommented:
@imnorie,

 Well, early binding does have it's advantages...
0
 
MacroShadowCommented:
@peter57r,

Couldn't agree more.
0
 
NorieCommented:
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
 
MacroShadowCommented:
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
 
Gustav BrockCIOCommented:
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
 
MacroShadowCommented:
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
 
developingprogrammerAuthor Commented:
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
 
MacroShadowCommented:
You must have 2003 installed to add a reference.
0
 
developingprogrammerAuthor Commented:
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
 
MacroShadowCommented:
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
 
Gustav BrockCIOCommented:
> 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
 
developingprogrammerAuthor Commented:
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
 
MacroShadowCommented:
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
 
developingprogrammerAuthor Commented:
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
 
Gustav BrockCIOCommented:
You wouldn't need that expensive package for this.

/gustav
0
 
developingprogrammerAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 9
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now