Hi,

Thanks for all your help with this, with a lot of trial and error I have
managed to sort out how to do this, I think I might be well of getting a book
as well as I can see me having to do this often.

Thanks again for the help it was much appreciated

Phil

"K Dales" wrote:

No (see my post in the other thread): this is all very tricky to try to learn
and get straight! You are working with (at least) 3 systems: your database,
Windows ODBC, and Excel to try to perform a pretty complex task! So nothing
daft about it. I had to learn the hard way (and am still learning) but that
is why I try to lend a hand when I feel I can ;o)

"Phil" wrote:

Yes you are right, your going to tell me I am doing something daft here
aren't you :-)

"K Dales" wrote:

Am I getting this straight: is your A1 value (8011) supposed to be used as
the parameter @clientid?

"Phil" wrote:

Hi

Thanks for the quick reply, I will try and give you what else you need, all
that is in A1 is
the value 8011, and the array is a sp that look like this

ALTER procedure test

@clientid int = null

as


select top 10 * from tblextended
where clientid = @clientid

I have tried adding the debug.pring option, but when I tried running it, it
did the same as before and just gave me colum headings, hope this is of help.

Thanks Phil

"K Dales" wrote:

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