Phil: What is in Range("A1")? And what is in the array you are using for
your CommandText? Since that is what you are using to set up your actual
query text I would need to see it to know what is going on. Try a
Debug.Print .CommandText in your code, right after you set the CommandText,
so you can see what the actual resulting text is - I think the key to solving
the issue is in that text.

"Phil" wrote:

Hi All,

I posted a query here the other day, but although the reply's were useful
they weren't quite what I was after, I have since made some further progress,
what I am trying to do is use a macro to take a value from sheet 1, pass it
through the sql and then put the results in sheet 3, my code looks something
like :-

Sub DataCollect()
'
' Macro1 Macro
'
'Dim filename
'Set filename = "ManagementExt_" & Range("month")
'Windows("ManagementExt_4_2002_RJHP_1000012.xls"). Activate
Sheets("Sheet1").Select
Rows("1:1000").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=AERIAL;UID=XXX;pword=XXXAPP=Microsof t®
Query;WSID=SNAME;DATABASE=SAMPLE;Network=DBMSSOCN; Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array("SAMPLE.dbo.test " & Range("A1"))
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Sheets("Sheet1").Select

I know I am very slowly getting there as I am getting the table headers from
the required table but as it's not using my defined variable it's not
bringing back
any results, does anyone have an idea why.

Thanks in advance, Phil