Topic: Make your macro know its place.

[havano]

Suppose you have built a macro that uses some INI and DATA files for input or output, located in the same directory. You have hard-coded the path into your DVB file, so that your macro knows where to look for these files.

The end-users decide to make the macro available on a network server drive. But the client PC's use different drive letters for this drive. Also, the IT department changes the directory name for the application. Result: the macro can't find the INI and DATA files.

Here's a solution:

Code: [Select]

Public Sub GetMyLocation()
Dim strDVBname As String, strDVBpath As String
strDVBname = VBE.ActiveVBProject.Name & "."
strDVBpath = VBE.ActiveVBProject.BuildFileName

strDVBpath = left(strDVBpath, InStr(strDVBpath, strDVBname) - 1)
Debug.Print strDVBpath

'    e.g. strDVBpath will be "AnyDrive:\AnyDir\AnySubdir\"
'    so your macro can access data files in this directory
'    no matter where the directory is created.
End Sub
I hope this is of some use for some.

[Dnereb]

How about something like this to do the same:

Public Function GetMyLocation()
GetMyLocation =   Replace(VBE.ActiveVBProject.BuildFileName ,VBE.ActiveVBProject.Name, "")
End Function

[havano]

Beautiful, Dnereb!

One small improvement:

Code: [Select]

Public Function GetMyLocation()
GetMyLocation = Replace(VBE.ActiveVBProject.BuildFileName ,VBE.ActiveVBProject.Name & ".DLL", "")
End Function

[Alexander Rivilis]

How about something like this to do the same:

Public Function GetMyLocation()
GetMyLocation =   Replace(VBE.ActiveVBProject.BuildFileName ,VBE.ActiveVBProject.Name, "")
End Function

I think this code can get wrong result if VBE.ActiveVBProject.BuildFileName has subdirectory equal VBE.ActiveVBProject.Name
Be carefully!

[havano]

Hence the addition of "." in my first example and ".DLL" in my reply to Dnereb's posting. The latter being necessairy anyway to make the function work properly  .

[Alexander Rivilis]

Hence the addition of "." in my first example and ".DLL" in my reply to Dnereb's posting. The latter being necessairy anyway to make the function work properly  .

But ".DLL" can be also in subdirectory path! For example: C:\TEST.DLL\MyOwnDirectory\Test.DLL\TEST.DLL

[havano]

OK, Alexander. Any suggestions?
(A 100% fail-safe method would of course be locating the rightmost backslash in the string, and trim the string from there.)

[Alexander Rivilis]

OK, Alexander. Any suggestions?
(A 100% fail-safe method would of course be locating the rightmost backslash in the string, and trim the string from there.)

You absolutely right! I do not write with VBA (VB, etc) If VBA has function which can substitute only one (!!!) right occurrence of substring in string - this function can be useful. In other case you have to do that function yourself!

[havano]

Code: [Select]

Public Sub KnowMyPlace()
Dim MyDir As String
MyDir = StrReverse(VBE.ActiveVBProject.BuildFileName)
MyDir = StrReverse(Right(MyDir, Len(MyDir) - InStr(MyDir, "\") + 1))
End Sub