CODEWITHSUNDEEP
windowsexceldll
Rook
Rook

Reputation: 62538

How to use dll's in the same directory as an excel file

This is somewhat related to my other question.

I've been using a dll to acompany an excel spreadsheet. Everything is currently working with the dll and excel is using it just fine. But is it possible to specify that a dll resides in the same directory as the excel file when declaring functions?

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

Unfortunetly this doesn't work, I have to use something like:

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

This works, but is going to cause headaches when distributing to my office mates. Placing the dll in c:\windows\system32 etc. is not really an option either.

Upvotes: 9

Views: 11135

Answers (5)

ewbi
ewbi

Reputation: 1833

Here are three possibilities for dynamically loading/calling into DLLs from VBA, including links to relevant info and some sample code. Can't say I've ever had to use any of the solutions described there, but it seems like a reasonable exploration of the options in light of VBA's need for a static path.

  1. Create a new module at runtime (you could import a .bas file from disk, no need to hard-code the module with string literals), using the VBIDE Extensibility API. Drawback: no compile-time validation; you'll need to use stringly-typed Application.Run calls to invoke it. Requires trusted programmatic access to the VBIDE API (i.e. you allow VBA to execute code that generates code that is then executed... like macro viruses do).
  2. Use the LoadLibrary Win32 API... and now you've got pointers and addresses: this scary code (.zip download) is essentially a huge unmaintainable hack that uses assembly language to enable invoking the API functions by name. Looks like it only works for a subset of supported Win32 API functions though.
  3. Change the DLL search path, but then that also requires dynamic code added at run-time, so might as well go with the above.

Here's another potential solution that suggests programmatically updating the PATH environment variable prior to calling into your DLL. Not a bad idea, if it works, as you could add this to you workbook open event.

Good luck!

Upvotes: 4

Ricardo Affinito
Ricardo Affinito

Reputation: 31

The way I generally take care of this is by adding:

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

To: Private Sub Workbook_Open()

Upvotes: 3

Rusty
Rusty

Reputation: 23

ChDir() should do the trick. It might not work on network folders though.

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 your .dll in the same folder as your workbook.

Upvotes: 2

Ken Paul
Ken Paul

Reputation: 5765

ActiveWorkbook.Path gives you the full path to the folder containing the currently-active workbook. So try this:

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

Upvotes: -2

Assaf Lavie
Assaf Lavie

Reputation: 75963

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

Upvotes: -1

Related Questions