Just paste this code into any module and change the name of the module to basParseEnvVar. Be sure to also include the code that appeared in issue 9 if you want it to support the feature to replace a double delimiter (see comments near the end of the ParseEnvVar routine.
'----------------------------------------------------------------------
'
' Routine Name: ParseEnvVar
' Written By: C&D Programming Corp.
' Create Date: 03/30/99
' Copyright: Copyright 1999 by C&D Programming Corp. Source
' code may not be reproduced except for use in a
' compiled executable. All rights reserved. If
' you would like to reprint any or all of this
' code please email us at info@codeoftheweek.com
'
' Purpose: Allow substitution of any environment strings into
' the data passed as sData.
'
'----------------------------------------------------------------------
Option Explicit
Public Function ParseEnvVar(ByVal sData As String) As String
Dim lPos As Long
Dim lEndPos As Long
Dim sToken As String
Dim sEnvVar As String
Const DELIM = "%"
On Error GoTo Handler
lPos = InStr(sData, DELIM)
While lPos <> 0
lEndPos = InStr(lPos + 1, sData, DELIM)
If lEndPos <> 0 Then
If lEndPos - 1 = lPos Then ' we have a double delimiter so ignore it
lPos = InStr(lEndPos + 1, sData, DELIM) ' force it to skip these and search again
Else
' pull out the token between the delimiters
sToken = Mid(sData, lPos + 1, lEndPos - lPos - 1)
' retrieve the token value from the environment (you could also
' retrieve the value from someplace else let a database, ini file
' or registry).
sEnvVar = Environ$(sToken)
' put together the new string with the token replaced with the value
' determined from the previous line.
sData = Left(sData, lPos - 1) & sEnvVar & Mid$(sData, lEndPos + 1)
' search whole string again, allows nested environment variables
lPos = InStr(1, sData, DELIM)
End If
Else
' no more delimiters were found so skip outta here.
lPos = 0
End If
Wend
' now just replace the %% with %
' to perform the replacement of double percent signs you will need
' issue number 9 - Search/Replace strings
' This is not required to parse the environment strings, it just
' provides the extra feature of being able to change the double
' percent signs to single ones.
sData = ReplaceChars(sData, DELIM & DELIM, DELIM)
ParseEnvVar = sData
Exit Function
Handler:
ParseEnvVar = ""
Err.Raise Err.Number, "ParseEnvVar", Err.Description
End Function