VBA References
 

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

VBA References
 

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.
--
HTH...

Jim Thomlinson


"Jez" wrote:

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

VBA References
 

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


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Jez" wrote:

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

VBA References
 

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

"Jez" wrote:
| 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

VBA References
 

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

"Dave Patrick" wrote:

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

"Jez" wrote:
| 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

VBA References
 

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


"Jez" wrote in message
...
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

VBA References
 

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

"Jez" wrote:
| 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

VBA References
 

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" wrote in message
...
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

"Jez" wrote:
| 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

VBA References
 

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

"RB Smissaert" wrote:
| 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

VBA References
 

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

RBS

"Dave Patrick" wrote in message
...
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

"RB Smissaert" wrote:
| 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

VBA References
 

No problemo

--

Regards,

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

"RB Smissaert" wrote:
| Sorry, didn't understand what you were saying there.
|
| RBS

VBA References
 

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


"RB Smissaert" wrote:

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


"Jez" wrote in message
...
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

VBA References
 

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