ExcelBanter - Adding reference in code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Adding reference in code (https://www.excelbanter.com/excel-programming/281433-adding-reference-code.html)

Adding reference in code

Hello all,
Is there a way to programatically add a reference to a dll or .xla file from
an Excel workbook? (i.e. ToolsReferences from the VB Editor) .

Thanks,
Andrew

Adding reference in code

To add an XLA file:

AddIns("ODBC Add-in").Installed = True

ODBC Add-in is added; setting to False removes it.

If a Win32 DLL, then something like:

Declare Function CopyFile Lib "kernel32" Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName
As String, ByVal bFailIfExists As Long) As Long

The DLL is KERNEL32.DLL, the function is CopyFile (the
arguments must be known).

If an ActiveX DLL (or EXE)

Set Myobj=CreateObject(name.class)

e.g.

Set objWrd=CreateObject("Word.Application")
Set objAPL=CreateObject("APLW.WSEngine")

-----Original Message-----
Hello all,
Is there a way to programatically add a reference to a
dll or .xla file from
an Excel workbook? (i.e. ToolsReferences from the VB
Editor) .

Thanks,
Andrew

.

Adding reference in code

Thanks for the prompt reply. I'm looking to programatically add a VB
Reference, not an Add-In. Is it possible? In the second example you
provided, you are demonstrating late-binding. I'm looking for a way to add
a reference to an .xla file so that the public functions in that .xla file
are available for use in the current workbook.

Thanks,
Andrew

"AA" wrote in message
...
To add an XLA file:

AddIns("ODBC Add-in").Installed = True

ODBC Add-in is added; setting to False removes it.

If a Win32 DLL, then something like:

Declare Function CopyFile Lib "kernel32" Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName
As String, ByVal bFailIfExists As Long) As Long

The DLL is KERNEL32.DLL, the function is CopyFile (the
arguments must be known).

If an ActiveX DLL (or EXE)

Set Myobj=CreateObject(name.class)

e.g.

Set objWrd=CreateObject("Word.Application")
Set objAPL=CreateObject("APLW.WSEngine")

-----Original Message-----
Hello all,
Is there a way to programatically add a reference to a
dll or .xla file from
an Excel workbook? (i.e. ToolsReferences from the VB
Editor) .

Thanks,
Andrew

.

Adding reference in code

If you have added an addin, either with

AddIns("ODBC Add-in").Installed = True

or with Tools|AddIns etc,

the functions in that XLA become available. You can use
them in the worksheet

=fnname(arg1,,arg2)

or in macros

ActiveCell.FormulaR1C1 = "=fname(arg1,,arg2)"

IF you need the value in the macro, read it from the
activecell or other cell where you have written it.

-----Original Message-----
Thanks for the prompt reply. I'm looking to
programatically add a VB
Reference, not an Add-In. Is it possible? In the second
example you
provided, you are demonstrating late-binding. I'm
looking for a way to add
a reference to an .xla file so that the public functions
in that .xla file
are available for use in the current workbook.

Thanks,
Andrew

"AA" wrote in
message
...
To add an XLA file:

AddIns("ODBC Add-in").Installed = True

ODBC Add-in is added; setting to False removes it.

If a Win32 DLL, then something like:

Declare Function CopyFile Lib "kernel32"
Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName
As String, ByVal bFailIfExists As Long) As Long

The DLL is KERNEL32.DLL, the function is CopyFile (the
arguments must be known).

If an ActiveX DLL (or EXE)

Set Myobj=CreateObject(name.class)

e.g.

Set objWrd=CreateObject("Word.Application")
Set objAPL=CreateObject("APLW.WSEngine")

-----Original Message-----
Hello all,
Is there a way to programatically add a reference to a
dll or .xla file from
an Excel workbook? (i.e. ToolsReferences from the VB
Editor) .

Thanks,
Andrew

.

.

Adding reference in code

You're right. I'm sorry, I don't think I was clear about what I was looking
for. I needed to make the external formulas available on the document
level...not the Excel application level. For example, if I create workbook
wb1.xls, I want to have a reference saved with the workbook to lib1.xla so
that I don't have to worry about whether or not another user has the add-in
installed. Thanks again.

"AA" wrote in message
...
If you have added an addin, either with

AddIns("ODBC Add-in").Installed = True

or with Tools|AddIns etc,

the functions in that XLA become available. You can use
them in the worksheet

=fnname(arg1,,arg2)

or in macros

ActiveCell.FormulaR1C1 = "=fname(arg1,,arg2)"

IF you need the value in the macro, read it from the
activecell or other cell where you have written it.

-----Original Message-----
Thanks for the prompt reply. I'm looking to
programatically add a VB
Reference, not an Add-In. Is it possible? In the second
example you
provided, you are demonstrating late-binding. I'm
looking for a way to add
a reference to an .xla file so that the public functions
in that .xla file
are available for use in the current workbook.

Thanks,
Andrew

"AA" wrote in
message
...
To add an XLA file:

AddIns("ODBC Add-in").Installed = True

ODBC Add-in is added; setting to False removes it.

If a Win32 DLL, then something like:

Declare Function CopyFile Lib "kernel32"
Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName
As String, ByVal bFailIfExists As Long) As Long

The DLL is KERNEL32.DLL, the function is CopyFile (the
arguments must be known).

If an ActiveX DLL (or EXE)

Set Myobj=CreateObject(name.class)

e.g.

Set objWrd=CreateObject("Word.Application")
Set objAPL=CreateObject("APLW.WSEngine")

-----Original Message-----
Hello all,
Is there a way to programatically add a reference to a
dll or .xla file from
an Excel workbook? (i.e. ToolsReferences from the VB
Editor) .

Thanks,
Andrew

.

.

Adding reference in code

Andrew,

Try something like

ThisWorkbook.VBProject.References.AddFromFile _
Filename:="C:\filename.xla"

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Andrew O'Brien" <trinitywallstreet.org@aobrien wrote in message
...
Hello all,
Is there a way to programatically add a reference to a dll or .xla file
from
an Excel workbook? (i.e. ToolsReferences from the VB Editor) .

Thanks,
Andrew