VB code to set VBA References

[Sajit]

I want to create a set of VB statements that will set the object references
required for my VBA code, such as, for the following,

Visual basic for applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Project 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.7 Library

Have them compiled to .exe and then call it at the start of my VBA code.

This is to create the references without the user having to do it themselves.

I suppose it would be necessary to check whether the refernces are already
set, if so then skip this step.

Can someone please show me a few lines of, how to do this.

Thanks,

 

[NickHK]

This is part of the answer:
http://www.cpearson.com/excel/vbe.htm

Presumably you have VB6 or similar to create your .exe file
However, you could use late-binding and avoid the reference issue.

NickHK

 

[par_60056]

I want to create a set of VB statements that will set the object references
required for my VBA code, such as, for the following,

Visual basic for applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Project 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.7 Library

Have them compiled to .exe and then call it at the start of my VBA code.

This is to create the references without the user having to do it themselves.

I suppose it would be necessary to check whether the refernces are already
set, if so then skip this step.

You could use the workbook_open event.

Private Sub Workbook_Open()
ActiveWorkbook.VBProject.References.AddFromGuid "{GUID of
References}", 1, 0

End Sub

You can find the right GUIDs by searching the registry if you don't
know them.

Peter Richardson

 

[Sajit]

I did not know what the GUID meant so I had to look up the help. I see from
the help,

Every type library has an associated GUID which is stored in the Registry.
When you set a reference to a type library, Microsoft Access uses the type
library's GUID to identify the type library.

I am wondering whether the GUID for a certain reference, will it be the
same, on 2 different PCs. I am asking this because, since I am trying to make
a common method for all the PCs connected on the server.

 

[Sajit]

Thanks,

There is a load of stuff to understand including some other thing related to
propagating VBA code to multiple Excel files.
--
Sajit
Abu Dhabi

This is part of the answer:
http://www.cpearson.com/excel/vbe.htm

Presumably you have VB6 or similar to create your .exe file
However, you could use late-binding and avoid the reference issue.

NickHK

 

[barnabel]

As far as I know they are supposed to be the same on every machine for a
given library.

Peter Richardson

 

[Sajit]

I have gone through the material in the link.

There is a method to remove a module and then add a module. I need to
replace the code that is there is in the subs in between as well.

How does that happen?

--
Sajit
Abu Dhabi

This is part of the answer:
http://www.cpearson.com/excel/vbe.htm

Presumably you have VB6 or similar to create your .exe file
However, you could use late-binding and avoid the reference issue.

NickHK