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