Tutorial :Dynamic pathfinding in Excel import


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?


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"))  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »