How to use dll in the same directory as excel file

Question

This is somewhat related to my other question .

I am using dll to work with Excel spreadsheet. Everything currently works with dll, and excel uses it just fine. But is it possible to indicate that the dll is in the same directory as the excel file when declaring functions?

Declare Sub FortranCall Lib "Fcall.dll" (r1 As Long, ByVal num As String) 

Unfortunately this will not work, I have to use something like:

 Declare Sub FortranCall Lib "C:\temp\Fcall.dll" (r1 As Long, ByVal num As String) 

It works, but it will cause headaches when distributing my office assistants. Placing dll in c: \ windows \ system32 etc. Also not an option.

Answer 1

Here are three options for dynamically loading / calling into a DLL from VBA, including links to related information and some sample code. I can’t say that I have ever had to use any of the solutions described there, but it seems like a reasonable study of the options in light of the need for VBA for a static path.

• Create a new module at runtime (you can import the .bas file from disk, you do not need to hard-code the module with string literals) using the VBIDE extensibility API. Disadvantage: no compile time check; you will need to use strong typed calls to Application.Run to call it. Trusted programmatic access to the VBIDE API is required (i.e., you allow VBA to execute code that generates code that is then executed ... for example, macro viruses).
• Use the LoadLibrary Win32 API ... and now you have pointers and addresses: this scary code (.zip) is essentially a huge unattainable hack that uses assembly language to enable calling API functions by name. It seems to work only for a subset of the supported Win32 API functions.
• Change the DLL search path, but then it will also require adding dynamic code at runtime, so it could be higher as well.

Here's another potential solution that offers programmatically updating the PATH environment variable before calling your DLL. Nice idea if it works, since you could add this book open event.

Good luck

Answer 2

I usually take care of this by adding:

 Dim CurrentPath As String CurrentPath = CurDir() ChDir (ThisWorkbook.Path) 

To: Private Sub Workbook_Open ()

Answer 3

ChDir () should do the trick. However, it may not work in network folders.

 Declare Sub FortranCall Lib "Fcall.dll" (r1 As Long, ByVal num As String) ... Dim CurrentPath As String CurrentPath = CurDir() ChDir (ThisWorkbook.Path) Call FortranCall(r, n) ChDir (CurrentPath) ' Change back to original directory 

Now keep the .dll in the same folder as your book.

Answer 4

You can put the DLL in some directory and add it to the EnVar path.

Answer 5

ActiveWorkbook.Path provides the full path to the folder containing the current workbook. So try the following:

 Declare Sub FortranCall Lib ActiveWorkbook.Path & "\Fcall.dll" (r1 As Long, ByVal num As String)