How to set Reference using VBA

[MarkP]

I have a couple of macros for exporting to excel based on work started by Rod
Gill and Jack Dahlgren (Thanks to both).

My problem is that although excel is installed on all my user's PC, the VBE
setting may not be correct.

Manually checking all the time is painful and does not scale.

I would like to execute a a macro that does something like this:
Set MyPrj.VBProject.References("Microsoft Excel 11.0 Object Library") = True

I know the above syntax does not work, is there some way I can set the
Checkbox to True?

Thanks,
MarkP

 

[Assaf Leibovich]

Hi,
Go to the Tools menu -> References option and add your reference through there

 

[Jan De Messemaeker]

Hi,

This is the code

vbe.ActiveVBProject.References.AddFromFile(filename as String)

To get the filename, connect to a project which has subject reference then
f.i.
?vbe.activevbproject.References(6).FullPath

HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Assaf Leibovich]

Hi,
Sorry that I missed the context of VBA request...

The file name you are looking for should be [drive]:\Program Files\Microsoft
Office\OFFICE11\EXCEL.EXE

 

[Rod Gill]

HI,

I've found that the AddFromGUID version to be more reliable and isn't
affected by the folder Office is installed in.

Copy the following to the immediate window, then press Enter,

?vbe.ActiveVBProject.References("Excel").Guid

On my PC I got {00020813-0000-0000-C000-000000000046}

vbe.ActiveVBProject.References.AddFromGuid
"{00020813-0000-0000-C000-000000000046}",1,6
Adds a reference to Excel 2007

vbe.ActiveVBProject.References.AddFromGuid
"{00020813-0000-0000-C000-000000000046}",1,5
Adds a reference to Excel 2003

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Hi,

This is the code

vbe.ActiveVBProject.References.AddFromFile(filename as String)

To get the filename, connect to a project which has subject reference then
f.i.
?vbe.activevbproject.References(6).FullPath

HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf




__________ Information from ESET Smart Security, version of virus
signature database 4852 (20100209) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4852 (20100209) __________

The message was checked by ESET Smart Security.

http://www.eset.com