Macro's

[Guest]

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=Microsoft®
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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

To clarify: the debug.print will not change your results, but when you look
in the VB Editor at the Immediate window it should show you exactly what your
..CommandText value is, so you can verify that it is coming out correctly.

 

[Guest]

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

 

[Guest]

Sorry about this, as you can tell this is something that I dont use often, I
tried just running it and got nothing at the bottom of the screen, I did
however ask you view the immediate window and all it shows is my databasename
and name of my stored procedure a number of times with no sign of the
variable, not sure if this is of any more help.

Thanks Phil

 

[Guest]

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

 

[Guest]

I suspect the issue is in the line:
..CommandText = Array("SAMPLE.dbo.test " & Range("A1"))

From what you have said, this command gives as the command text a
one-element array where the element given is equal to "SAMPLE.dbo.test 8011"
I don't know how VBA will interpret an array for a .CommandText value and I
am surprised it is not giving an error. I can't really tell why you are
using the array function here. The bottom line is this parameter should be
the exact command you would pass to run the procedure via SQL, set up as a
string variable. If you can get it to be equal to the SQL command it should
work.

 

[Guest]

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)

 

[Guest]

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