Dynamic pathfinding in Excel import

Question

I have a piece of VB code that dynamically finds my current file path in Excel. Now I wish to use it when importing text from a text file, but can't figure out just how to code it.

Here is my code for getting the current path:

Function GetCurDir()
Dim str As String
Dim pos As Integer
str = ActiveWorkbook.FullName
pos = InStrRev(str, "\")
str = Mid(str, 1, pos)
GetCurDir = str
End Function

This is how I use it when writing to a file:

MyFile = GetCurDir & "\Data\MaterialBalance\Ngasdata.dat"

This is where I would also like to use it:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\NGasSim\Data\MaterialBalance\NGASPROD.DAT", Destination:=Range( _
    "$G$4"))

As you see, this is a static path.. Could anyone help me with the right syntax?

Answer 1

Just create another variable to build your connection string:

MyFile = GetCurDir & "\Data\MaterialBalance\Ngasdata.dat"

MyConnection = "TEXT;" & MyFile 

With ActiveSheet.QueryTables.Add(Connection:= _
    MyConnection, Destination:=Range( _
    "$G$4"))