Excel Referencing File Locations

[LazyLarry]

I have a spreadsheet of which I have built VB code to import a couple of text files and then use MSquery to combine these into one table of data.
My question is that currently it references itself in a specific location ie.
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\FileLocation\File.xlsm;DefaultDir=C:\FileLocation"

How do I change this so that it is dynamic ie if I move it from C:\Filelocation to C:\NewFileLocation that it will still work.

Thanks for your help.

 

[LazyLarry]

I've managed to answer this myself by doing the following:

Dim strFile as String
strFile = ThisWorkbook.Fullname
Dim stfPath as String
strPath = ThisWorkbook.Path

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"ODBC;DSN=Excel Files;DBQ=" & strFile & ";DefaultDir=" & strPath ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"), _
Destination:=Range("$A$1")).QueryTable