Set path for dll in vba code

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-05-12
I am successfully calling an MFC dll from my vba code in my EXCEL spreadsheet, using this declaration:

Private Declare Function DllImport_MyDll _
                        Lib "MyDll.dll" _
                        Alias "MyDllFunction" _
                        (ByVal str1 As String, _
                        ByVal str2 As String) As Integer

and this call

nReturn = DllImport_MyDll(strSomeString, _

Piece of cake.  This code works correctly.   I believe the above code implies that "MyDll" will reside in the System32 folder.

However, what I'd like to do alter the 2nd parameter slightly.  Instead of passing in "MyDll.dll", I'd like to pass in a string variable that includes both the dll name "MyDll.dll" as well as the path to the dll. We'll call this variable "strPathAndNameOfDll".  So here's what the modified dll import signature would like like:

Private Declare Function DllImport_MyDll _
                        Lib strPathAndNameOfDll _
                        Alias "MyDllFunction" _
                        (ByVal str1 As String, _
                        ByVal str2 As String) As Integer

Note that my new parameter strPathAndNameOfDll is now listed as the Lib.

So, the problem I'm having is that I don't know where to declare my variable strPathAndNameOfDll.  If I declare it immediately above the DllImport statement, I cannot assign a String data type to it.  I can only declare it as a "type-less" constant, like this:

Private Const strPathAndNameOfDll = "C:\PathToMyDll\\EnvelopePlot.dll"

But when I do this, and then put strPathAndNameOfDll as the Lib in the DllImport statement, I get an error message that says: Compile Error: expected string constant.

Other notes in case you're wondering why I'm trying to do this:

a) I do NOT want to simply put my dll in System32.  The path to my dll needs to be determined at run-time, and is user-specific, DEPENDING ON HOW/WHERE A USER CHOOSES TO SET UP THE FOLDER CONTAINING MY APPLICATION AND DLL.  

So, at runtime, I can successfully determine the path to the dll.  However, I'm having trouble figuring out whether to declare the string variable that holds this path, so that I can pass the string variable into the DllImport statement as the Lib.

Any help would be greatly appreciated.

Question by:cjm20
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36969004
you cannot dynamically import from a "path variable".

note this article that describes where DllImport will look when looking for a dll:

Author Closing Comment

ID: 36981656
Thanks.  I decided to simply put my dll in System32 (or the operating system equivalent) and be done with it.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question