DLL folder and VBA

[Marc]

Hi,

I'm developping an application using a macro (in Excel) and a DLL that I
wrote.

The problem is that when I'm trying to declare my function from the DLL in
my macro, I've got an Runtime Error 53 : File not found 'MyAPI.dll' .

Apparently, VBA does not find the DLL if I don't give the full path in the
declaration or if the DLL is not the in the system folder.
In fact, I would be better if the DLL can be in the XLS folder or in a
subfolder (called DLL\ for example

## WORKS (If the DLL is the system folder)
Declare Function generateData Lib "MyAPI.dll" _
Alias "GenerateData" ( _
ByVal strTargetOutputFile As String, _
ByVal strInputFileName As String, _
ByVal strGeneratedStructName As String) _
As Long

## WORKS (Full path)
Declare Function generateData Lib "D:\_MyTemp_\MyAPI\Debug\MyAPI.dll" _
Alias "GenerateData" ( _
ByVal strTargetOutputFile As String, _
ByVal strInputFileName As String, _
ByVal strGeneratedStructName As String) _
As Long

## If the DLL is in the same folder that the XLS containing the macro

Does anybody have any solution or workaround ?

Thanks in advance,

Marc

 

[Jonathan West]

Hi Marc,

It will work if either of the two cases is true.

1. The DLL is in a folder referenced by the PATH environment variable, or

2. The *first* time you make a call to a function of the DLL, the current
folder is the folder containing the DLL. Once the DLL is loaded, subsequent
calls will work irrespective of the current folder.

 

[Lars-Eric Gisslén]

Marc,

If you do not specify the path Windows search for the DLL in the following
order:

1. The directory from which the application loaded.
2. The current directory.
3. The Windows system directory.
4. The Windows directory.
6. The directories that are listed in the PATH environment variable.

If your DLL is not located in one of the directories above you must specify
the full path to the DLL in the declaration.

However, if you are going to use the the DLL at one office with a server you
can put the DLL on the server, as long as all users can access the server.
When you open the Workbook/document you call WinAPI
LoadLibrary(<\\Server\Path\yourDLL>) and store the handle returned in a
global variable. When you close the Workbook/document you call WinAPI
FreeLibray() with the handle LoadLibrary returned.

 

[Gerrit]

Marc,

If you do not specify the path Windows search for the DLL in the following
order:

1. The directory from which the application loaded.
2. The current directory.
3. The Windows system directory.
4. The Windows directory.
6. The directories that are listed in the PATH environment variable.

If your DLL is not located in one of the directories above you must specify
the full path to the DLL in the declaration.

However, if you are going to use the the DLL at one office with a server you
can put the DLL on the server, as long as all users can access the server.
When you open the Workbook/document you call WinAPI
LoadLibrary(<\\Server\Path\yourDLL>) and store the handle returned in a
global variable. When you close the Workbook/document you call WinAPI
FreeLibray() with the handle LoadLibrary returned.

make the currentdir de dir where the dll, before the first call
with:
setdir and setdrive

 

[jaf]

Start>run> regsvr32 drive\path\filename.dll

 

[Jonathan West]

Start>run> regsvr32 drive\path\filename.dll

Hi jaf,

That would be good if the DLL were an activeX DLL, but since it isn't (as it
is accessed using the Declare syntax) registering it has no effect.

 

[Karl E. Peterson]

Hi Jonathan!

It will work if either of the two cases is true.

1. The DLL is in a folder referenced by the PATH environment variable, or

2. The *first* time you make a call to a function of the DLL, the current
folder is the folder containing the DLL. Once the DLL is loaded, subsequent
calls will work irrespective of the current folder.

HOLY COW! Where was I when you discovered that??? That'd solve a problem I was
playing with just this morning very nicely!

Thanks... Karl