Excel VBA file breaks between computers

[asmith]

I am developing an Excel file that runs a simple SQL query based on a few parameters and then puts the data into a spreadsheet. I have all the VBA working just fine. The problem is when I send it out to be tested by another user on another computer, they get a run-time error. The specific message is "Run-Time Error 430: Class does not support automation or does not support expected interface". The way that I resolve this is by going into the VBA, then doing some minor change, then reversing it, then saving it, and it works again. I'm pretty sure that somewhere along the lines the link to the ADODB library is being broken, but I don't know how to get it so that I don't have to do this in the first place. There are some subtle differences between my development machine and those used by our users which are as follows.

Development:
Windows 7 SP1 Professional x64
Office 2010

Client:
Windows 7 Professional x86
Office 2010

I would not think that there would be that this would cause any issues though. Additionally, I am using ADO 2.8. I have also tried this with version 2.7 and got the same results.

Does anyone have any idea how to fix this?

Thanks!

[Bob Phillips]

Try using late-binding.

[Frosty]

Late-binding will probably resolve it, at least for now.

In addition to late-binding, you can check your project references (Tools > References) in the VBA Editor window, to see what is actually being referenced differently between the two machines.

Depending on where you're at in the development process, late-binding may or may not be the final solution. Just an additional trouble-shooting step.

[asmith]

Thanks! I think late-binding did the trick. It can be hard to tell as sometimes after a revision it works, and sometimes not, but for now it is working. While I hate having to use late-binding, if it makes it more reliable, then it works for me.

Frosty, In the project references list everything looked OK which is why this was really puzzling. Only difference is the ADO libraries were in Program Files/Program Files (x86) for the different architecture types respectively.

Anyway, If it comes back I will re-post.

Thanks again!

[Frosty]

Everything should look OK... but you can get the actual files (for example, C:\Windows\System32\FM20.dll) being referenced, and then check those actual files for the version numbers, etc.

Things are not "more stable" with late-binding... they just allow you to ignore unimportant differences between installations at the expense of slightly more difficult programming. The "important" differences will just become run-time errors, instead of compile-time errors.

[asmith]

Ahh. Good to know. Thanks again!