VBA References

[Jez]

Hi,

How can I write code to include certain references when the workbook opens?

I use references like Microsoft ActiveX data object version 2.8, but my
customers dont always have the same up to date versions of excel.

From this they get hidden module errors. How can i write code so that if its
missing that it will pick up the same refernce but another version number eg
version 2.7

Thanks,
Jez

 

[Jim Thomlinson]

Just make your code reference the lowest version. You should be referenceing
2.7 or 2.6. Since the references are backwards compatible your clients should
have no difficulty if they have 2.8. This is true for all code taht you write
for distribution. Code to the lowest common denominator, in terms of
references and Excel versions.

 

[Jez]

Thanks for that, but what code do I need to write in vb?

I asume that it would need to be writen in the Sub Workbook_open module but
how do I get it to reference these?

Thanks,
Jez

 

[Dave Patrick]

Nothing.

Tools|References and check the box for 'Microsoft ActiveX Data Objects 2.x
Library'

Use the highest version that will still support your clients.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for that, but what code do I need to write in vb?
|
| I asume that it would need to be writen in the Sub Workbook_open module
but
| how do I get it to reference these?
|
| Thanks,
| Jez

 

[RB Smissaert]

Try something like this:

Sub AddADO()

Dim R

For Each R In ThisWorkbook.VBProject.References
If R.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And R.Major = 2
Then
Exit Sub
End If
Next

On Error GoTo NOTFOUND

'although usually the ADO version will be higher, doing Minor:=0 will
install
'the higher version if available. On the other hand when you specify
Minor:=5
'and only a lower version is available, this can't be installed
'----------------------------------------------------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
Major:=2, Minor:=0
Exit Sub

NOTFOUND:
On Error GoTo 0

End Sub


RBS

 

[Dave Patrick]

That's why you want to back it down to a version that the clients have. You
don't need to write anything.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| If I dont have anything and just have the references that are in my
| references, then when my clients use the report it comes up with hidden
| module error because they dont have the same references as i do.
|
| I want to write some code so that when the workbook opens it ignores any
| references that are "Missing" and choose a lower module.
|
| What can i write to get around this.
|
| Thanks,
| Jez

 

[RB Smissaert]

As you may not have the lowest possible version on your own machine it is
best to save the wb without the reference
and add the reference in code as posted earlier.

RBS

 

[Dave Patrick]

Might be but in a situation where the lowest version on my dev machine (2.0)
is higher than anything on the client I would expect greater problems than
this would exist.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| As you may not have the lowest possible version on your own machine it is
| best to save the wb without the reference
| and add the reference in code as posted earlier.
|
| RBS

 

[RB Smissaert]

Sorry, didn't understand what you were saying there.

RBS

 

[Dave Patrick]

No problemo

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Sorry, didn't understand what you were saying there.
|
| RBS

 

[Jez]

Thanks for this.

I dont mean to be thick but where would I put this code?

Do I put it in a module and call it in the Workbook_Open sub?

Also what happens if there are other references missing when my customers
open the report, how can I get around this.

Is there something I can do so that if any reference are missing it ignores
them?

When I open my report it automatically installs AddIns is there anything I
can do like this? AddIns("Analysis ToolPak").Installed = True

Thanks,
Jez

 

[bart.smissaert]

Do I put it in a module and call it in the Workbook_Open sub?

Yes, that is an option.

Also what happens if there are other references missing when my customers
open the report, how can I get around this

Exactly the same, but of course with different GUID's.
I posted some code only about 2 weeks ago that will show you how to get
these GUID's.

Is there something I can do so that if any reference are missing it ignores
them?

Normally it can't be ignored.

AddIns("Analysis ToolPak").Installed = True

Yes, I think that would do it.

RBS