Object reference Libraries

[Snowflake68]

I am having an issue with a missing reference in Access 2013. I will try and outline the problem below.

Access application was developed in Access 2013
I added an object reference - Microsoft Excel 15.0 Object Library which appears to have broken it for some users (it worked for them before I did this)

The module that uses this library works perfectly well and has been tested on my development PC and also other PC's using Access 2013.

However the application has now been passed out to several other users half of which it works for and the other half receive errors pertaining to a missing library for EXCEL.

I have asked one user (that is getting the error) to check to see if the EXCEL.EXE is in the following directory C:\Program Files (x86)\Microsoft Office\Office15 as this is where my reference is pointing. (they are yet to confirm this)

But the real oddity at the moment is that one user that the application works fine on is that they dont have the EXCEL.EXE in that folder. They say that their EXCEL.EXE is located in a different directory which is here C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE This basically throws out my theory of the missing file that the library is referencing (or does it?)

I am very new to all of the object references and only knew to add the Excel one when I copied some code for a module that generates and formats an Excel file.

I am hoping that someone can help shed some light on why the application works for some users and not others.

Would a repair of their Office software create the missing files that are needed for the reference?

From a very distressed damsel

 

[RuralGuy]

If I were you I would try "Late Binding": http://www.granite.ab.ca/access/latebinding.htm

 

[Snowflake68]

If I were you I would try "Late Binding": http://www.granite.ab.ca/access/latebinding.htm

Thanks RuralGuy but this is far beyond my capabilities plus I dont think it has anything to do with versions as everyone is running 2013 and it works for some but not others.

Would I just be able to give them the files that are missing and ask them to put them in the directory that the reference is looking at?

I have suggested that they try repairing their office software but I am stuck really. I still need to establish what the real issue is before trying to change my code to be honest.

Back to investigating :banghead:

 

[RuralGuy]

Good luck with your project.

 

[bentheimmigrant]

Just to clarify, is everyone using the same version of Excel? As far as I can tell, you've specified that everyone has Access 2013, but not whether that's the same as the rest of Office.

 

[Snowflake68]

Just to clarify, is everyone using the same version of Excel? As far as I can tell, you've specified that everyone has Access 2013, but not whether that's the same as the rest of Office.

Thanks bentheimmigrant never thought. I am informed that all of the users are using Office 365 Pro Edition 2013 or 2016 (no issues with 2016 as far as I am aware). So I would have thought that their Access and Excel were of the same version.

I have developed it on Office Professional Plus 2013 so not sure if there is an issue between Office 365 and Office Professional ??

 

[bentheimmigrant]

I have no experience of 365, so all I can suggest is to open up Access on the PCs that aren't working, and check what version of the Excel Reference Library is available in the list.

 

[Snowflake68]

I have no experience of 365, so all I can suggest is to open up Access on the PCs that aren't working, and check what version of the Excel Reference Library is available in the list.

The attached shows that there are two missing libraries but Im only concerned with the EXCEL one (I can remove the Word one as it was just something I was testing)

I am unsure why though that on my application it stats the EXCEL library as 15.0 but on the users application it says 16.0 when I didnt change anything. I guess this must be dynamic to whatever they are using. However the file is missing nonetheless.

My investigations continue :banghead:

Really appreciate your help

 

[bentheimmigrant]

It's looking distinctly like late binding is the way forwards. It's not as tricky as RuralGuy's link made it look.

You need to dim a generic "object" for each excel object (i.e. workbook, worksheets, including excel itself) you need, and then set them with a create object line. It's described in part here:
http://sourcedaddy.com/ms-access/late-binding.html

Each excel component that you previously had dimmed needs to be created as an object in this manner. This should then be version independent.

 

[Snowflake68]

It's looking distinctly like late binding is the way forwards. It's not as tricky as RuralGuy's link made it look.

You need to dim a generic "object" for each excel object (i.e. workbook, worksheets, including excel itself) you need, and then set them with a create object line. It's described in part here:
http://sourcedaddy.com/ms-access/late-binding.html

Each excel component that you previously had dimmed needs to be created as an object in this manner. This should then be version independent.

Thanks for this but what if the issue has nothing to do with the version they are using? I have users all using 2013 but it works for some and not others.

 

[Minty]

When you open a database with a later version of Access the libraries are automatically updated to the later version. The problem you will get is if someone with version 16 opens the file and then copies it to someone with version 15 or 10 or anything less.
Access / Office can't guess backwards what should be used.
This is why you should always use late binding if you need to distribute to a group of users with variable office versions, or make sure you develop in the lowest common denominator version and only that version of the front end is loaded onto individual users machines.

 

[Snowflake68]

When you open a database with a later version of Access the libraries are automatically updated to the later version. The problem you will get is if someone with version 16 opens the file and then copies it to someone with version 15 or 10 or anything less.
Access / Office can't guess backwards what should be used.
This is why you should always use late binding if you need to distribute to a group of users with variable office versions, or make sure you develop in the lowest common denominator version and only that version of the front end is loaded onto individual users machines.

This has given me an idea to try so I will report back with the outcome. Thanks Minty.

 

[The_Doc_Man]

I have users all using 2013 but it works for some and not others.

But you aren't even on a homogeneous Office installation. Though it wasn't always so, these days the major programs have unified their versioning. That is, if Office 2013 is installed, then the .DLL files will be the 15.0 library for all of the main programs like Word and Excel. (No bets on the ancillary libraries like DAO and scripting.) Your screen-shot example showed some 15.0 and some 16.0 versions in the references list.

Minty's suggestion - to develop using the EARLIEST version and distribute the .MDB or .ACCDB files to others so that Access does an automatic upgrade - is spot on. The idea of late binding wouldn't hurt, either.

Just be aware that the problem is in the registry, because the references are in the HKEY_CURRENT_USER hive for each user. (Yes, Registry!) If the machines were shared between two users with different profiles, it is possible that your app would work for one and not the other ON THE SAME MACHINE. Therefore, the problems are stemming from the information in the registry for each user, which is necessarily unique per system, and therefore is the perfect place to point at when talking about this problem.

 

[sxschech]

I didn't actually try it as I went with the late binding approach, but it may be worth experimenting with. Put this in a standard code module and then in the code prior to where you need to use excel (Or you could put it in the load event of the form), do a Call fixreference.

Public Function fixreference()
'Combined FixReference() and GetExcelPathFromVB()
'http://www.excelforum.com/excel-programming-vba-macros/524074-how-to-find-out-the-path-to-excel-exe-thru-vb.html
'http://stackoverflow.com/questions/5593588/add-references-programatically
    Dim appXL As Object
    Dim s As String
    Set appXL = CreateObject("Excel.Application")
    s = appXL.Path & "\excel.exe"
'    Debug.Print s
    appXL.Quit
    Set appXL = Nothing
    If Dir(s) <> "" And Not refExists("Excel") Then
        Access.References.AddFromFile (s)
    End If
End Function

Public Function refExists(naam As String)
'http://stackoverflow.com/questions/5593588/add-references-programatically

Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

 

[Snowflake68]

I didn't actually try it as I went with the late binding approach, but it may be worth experimenting with. Put this in a standard code module and then in the code prior to where you need to use excel (Or you could put it in the load event of the form), do a Call fixreference.

Public Function fixreference()
'Combined FixReference() and GetExcelPathFromVB()
'http://www.excelforum.com/excel-programming-vba-macros/524074-how-to-find-out-the-path-to-excel-exe-thru-vb.html
'http://stackoverflow.com/questions/5593588/add-references-programatically
    Dim appXL As Object
    Dim s As String
    Set appXL = CreateObject("Excel.Application")
    s = appXL.Path & "\excel.exe"
'    Debug.Print s
    appXL.Quit
    Set appXL = Nothing
    If Dir(s) <> "" And Not refExists("Excel") Then
        Access.References.AddFromFile (s)
    End If
End Function

Public Function refExists(naam As String)
'http://stackoverflow.com/questions/5593588/add-references-programatically

Dim ref As Reference
refExists = False
For Each ref In References
    If ref.Name = naam Then
        refExists = True
    End If
Next
End Function

Ive tried using your code by saving it as a new module and then calling the fixreference function from the first form that the application loads, but I get an error saying it cannot find the file specified and then points to this line in the code.

    Set appXL = CreateObject("Excel.Application")

Not sure if I have something wrong but it is difficult to test as I am trying to trick it by just moving my EXCEL.EXE file (from Office 15 directory) to a different folder (Office 12). I guess this isn't the way to test it really.

The issue I have with testing is that I only have Office 13 which I developed the system on and then give to my IT guys to install on other users pc's. The IT guys are on Office 2016 so I think this is where the issue is as they are opening it up on their PC's and testing it and then copying it to users on Office 2013.

 

[static]

You should have a registry key 'Excel.Application' which Windows uses to get the path to the application.

Either that key doesn't exist for some reason or the path is wrong, which is probably more likely since you moved the file.

If the key is missing you could do the same thing by trying to open a worksheet instead.

On Error Resume Next
Access.References.AddFromFile CreateObject(CreateObject("WScript.Shell").RegRead("HKEY_CLASSES_ROOT\.xlsx\")).Parent.Path & "\excel.exe"

I think you would be better off using late binding though, because this could lead to all sorts of issues with different excel versions getting added over time.

 

[Snowflake68]

You should have a registry key 'Excel.Application' which Windows uses to get the path to the application.

Either that key doesn't exist for some reason or the path is wrong, which is probably more likely since you moved the file.

If the key is missing you could do the same thing by trying to open a worksheet instead.

On Error Resume Next
Access.References.AddFromFile CreateObject(CreateObject("WScript.Shell").RegRead("HKEY_CLASSES_ROOT\.xlsx\")).Parent.Path & "\excel.exe"

I think you would be better off using late binding though, because this could lead to all sorts of issues with different excel versions getting added over time.

Thanks for your reply. Im still confused how to go about the Late Binding, Ive read all of the content of the posts which people have kindly provided me with but I am yet to work it all out Maybe its just because its Monday

 

[static]

Im still confused how to go about the Late Binding

Instead of

dim xl as new excel.application

or whatever, you use

dim xl as object
set xl = createobject("excel.application")

That's it.


There are 2 main differences between adding a reference and using createobject.

1) createobject makes writing code a bit harder because IntelliSense doesn't work.

A 'reference' is really just a library. It lists all of the available objects, properties and methods an application exposes for you to interact with via your code.

Using the first method, if you type xl. in a module you will get a drop down list of available options. Using the second method you wont.

2) because VBA doesn't have the reference to refer to, it can't check that your code is valid before you run it.


That's about it.

A reference forces you to be explicit about which library you are using.

createobject just asks Windows what to use. As long as the library for the application doesn't change significantly over the years, your code will continue to work regardless of the version.

 

[Snowflake68]

Instead of

dim xl as new excel.application

or whatever, you use

dim xl as object
set xl = createobject("excel.application")

That's it.


There are 2 main differences between adding a reference and using createobject.

1) createobject makes writing code a bit harder because IntelliSense doesn't work.

A 'reference' is really just a library. It lists all of the available objects, properties and methods an application exposes for you to interact with via your code.

Using the first method, if you type xl. in a module you will get a drop down list of available options. Using the second method you wont.

2) because VBA doesn't have the reference to refer to, it can't check that your code is valid before you run it.


That's about it.

A reference forces you to be explicit about which library you are using.

createobject just asks Windows what to use. As long as the library for the application doesn't change significantly over the years, your code will continue to work regardless of the version.

OMG I cant believe it was so simple in the end but it took you to point it out. I think I have done it by doing exactly what you said to do. I still had to declare the workbook and sheets and then change all my code which referenced these but its working now

I just need to test it thoroughly but I think I have got there massive thanks to you 'static'.

Thanks to all that contributed to this post I am very grateful you all helped me a lot.