*Quick formatting help for a macro connecting to file

[largeselection]

Hi again,

Basically I would like to connect to a text file that I have saved (which has lots of data so I want to pick out the pieces to import into excel). I can do this just fine on my own, but I want to set it up so that some of my colleagues can accomplish this by just saving their text files into a folder and specifying the path and then the code in the workbook will establish the connection for them. The only things I would need to be user defined would be 1) the path 2) the table name (or text file name) 3) specify which Part IDs to pull.

So I thought I could define a string "Res" as the response from an inputbox. Then define the path as a string based on the value in a cell which I named "FP". Then at some point define a part# based on another response probably. Thought I could just swap out the path that it spits out with my "FP" value and then replace the table names in the Select statement with the "Res" value.

That doesn't seem to be working. Is it even possible to create this connection via macro or do you have to do it via add connections?

It also doesn't appear to matter if I have the "FP" in there or not- it seems to automatically just go to the same directory rather than creating a new connection based on what I put.

So far I have:

Dim Res As String
Dim FP As String
Res = Application.InputBox("Text Name...", "", , , , , 1)
FP = Sheets("Main").Range("B12").Value
Sheets("Table").Select

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=" & FP & ";DefaultDir=" & FP & ";" _
        ), Array( _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransacti" _
        ), Array("ons=0;Threads=3;UID=admin;UserCommitSync=Yes;")), Destination:=Sheets("Table").Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT Res.Part#, Res.Continent, Res....etc etc etc... & "FROM Res.txt Res" & Chr(13) & "" & Chr(10) & "WHERE (Res.Part#=12456)" _
)
.RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "New_Table"
        .Refresh BackgroundQuery:=False
    End With...Other Code

Just to be clear the original, unmodified code looked like:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=C:\DOCUMENTS AND SETTINGS\John D\DESKTOP\Base Folder;DefaultDir=C:\DOCUMENTS AND SETTINGS\John D\DESKTOP\Base Folder;" _
        ), Array( _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransacti" _
        ), Array("ons=0;Threads=3;UID=admin;UserCommitSync=Yes;")), Destination:=Range _
        ("$A$1")).QueryTable
.
.
.
.
.CommandText = Array( _
        "SELECT SavedFile.X, SavedFile.Part#,...FROM SavedFile.txt SavedFile" & Chr(13) & "" & Chr(10) & "WHERE (SavedFile.Part#=12345)

That seems to work when I run it, so I'm hoping it's just something I've formatted incorrectly. Sorry if this is simple, but I've never attempted to make a connection like this via macro.

Thanks for any insight/help you can offer.