Below is the code for a test MACRO where I import worksheets and query them. I have the code to pull the information in from any users desktop but I cannot figure out how to get it to work in the query.

Sub Code_tester()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim oWsh As Object
Dim sPathDesktop As String
Set oWsh = CreateObject("WScript.Shell")
sPathDesktop = oWsh.SpecialFolders("Desktop") & "\TEST folder"
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(sPathDesktop & "\PET*.xlsx", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbSrc = Workbooks.Open(fileName:=sPathDesktop & "\" & strFilename)
Set wsSrc = wbSrc.Worksheets(1)
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
wbSrc.Close False
strFilename = Dir()
Loop
wbDst.Worksheets(1).Name = "Combined"
ChDir sPathDesktop
ActiveWorkbook.SaveAs fileName:=sPathDesktop & "\Combined", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets("Combined").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=d:\data\User Me\Desktop\TEST folder\Combined.xls;DefaultDir=d:\data\User Me\Desktop\TEST folder;DriverId=10" _
), Array("46;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")) _
.QueryTable
.CommandText = Array( _
"SELECT `'Sheet1 (2)$'`.`sale #`, `'Sheet1 (2)$'`.`complaint #`, `'Sheet1 (2)$'`.`Complaint narrative`, `'Sheet1 (2)$'`.`Complaint resolution`, `'Sheet1 (2)$'`.`date of resolution`, `'Sheet1 (3)$'`.`sa" _
, _
"le #`, `'Sheet1 (3)$'`.animal, `'Sheet1 (3)$'`.disposition, `'Sheet1 (3)$'`.color, `'Sheet1 (3)$'`.`date of sale`" & Chr(13) & "" & Chr(10) & "FROM `d:\data\User Me\Desktop\TEST folder\Combined.xls`.`'Sheet1 (2)$'` `'Sheet1 (2)$" _
, _
"'`, `d:\data\User Me\Desktop\TEST folder\Combined.xls`.`'Sheet1 (3)$'` `'Sheet1 (3)$'`" & Chr(13) & "" & Chr(10) & "WHERE `'Sheet1 (2)$'`.`sale #` = `'Sheet1 (3)$'`.`sale #`" & Chr(13) & "" & Chr(10) & "ORDER BY `'Sheet1 (2)$'`.`sale #`" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Excel_Files"
.Refresh BackgroundQuery:=False
End With
End Sub

I need it to be able to look in any users desktop\TEST folder to run the query.
Any suggestions?