Windows 8 Object Library for Excel

[Acme]

Hello Access Fans,

I know this has been discussed before, and I searched the forum last night and was unable to resolve my issue. Hopefully there is an answer out there.

I have an access application (developed in access 2013) that includes function calls to an excel application using the Microsoft Excel 2014 Object Library. Works fine, no issues. I bought another laptop that runs Windows 8, and moved my access app to it. However, windows 8 has only the Microsoft Excel 2015 Object Library, and go figure, several of the function calls no longer work. I don't know if I need to reinstall or if these functions just were excluded from the object library but there was nothing in Microsofts sites that indicated that the functions were removed. So, I spent an hour trying to figure out if I could put the Excel 2014 Object Lib on my new laptop, but as far as I can tell it is not possible. Any ideas? Example of the functions that are not forward compatible (binom.dist). I did try various respelling including binomdist, binom_dist, etc. It would save me hours of coding to solve this.

Gratefully yours,

 

[boblarson]

You should move to LATE BINDING. Don't set a reference and use code like

Dim objXL As Object

Set objXL = CreateObject("Excel.Application")



There is more to it but I have an export example on my website which shows it in use without setting a reference. If you don't set a reference, then all that is required is that the machine have Excel of some sort installed. There are some things you still need to be aware of, like using xls vs. xlsx format, etc. But essentially this should help.

http://www.btabdevelopment.com/ts/default.aspx?PageId=48

 

[Acme]

Thanks. I was creating the excel application already, and it works fine for other functions, it just doesn't work for certain functions, and I don't know why. The functions work in the excel app. I redimed the excel app as an object and that didn't resolve the situation. I didn't go as far as making the worksheet visible, dimming a worksheet and putting the function in a cell range, but something tells me that if Access says it can't get the property, then it doesn't matter how I do it. Any other ideas?

 

[boblarson]

Did you remove the reference to Excel in the VBA References? You need to do so if you go with the code I gave.

Post the code you are using which ends up causing the error. And note on which line it fails.

 

[Acme]

Mean to say thanks again. I was doing late binding. But note to self, don't put parens around xl.workbookfunctions