VBA Querying in SQL

[Arsenal176]

Hi
I am sorry , this may be a very stupid question , but I have no VBA knowldge . I have spent the last two weeks developing an excel based application and that is it. I have to query using any unique value in the list aa2:aa58
The values in this list may not be unique . If I just search using 4 values in the select its ok
but I get an invalid type eror if I search will all the values I want
I know this is probably bad code but here is how I do it

Dim a As Variant
Dim b As Variant
Dim c As Variant
etc for all values

a = Range("aa2").Value
b = Range("aa3").Value
c = Range("aa4").Value
d = Range("aa5").Value

etc

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER=SQL Server;SERVER=***;UID=***;APP=Microsoft® Query;WSID=***;DATABASE=mydb;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("AC1"))
.Sql = Array( _
"select * FROM mydb.dbo.table1 table1 WHERE table1.ID in (" & a & ", " & b & ", " & c & ", " & d & ",, " & e & ", " & f & ", " & g & ", " & h & ")" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With

But if I use all the 57 variables as I may want I get the error

Can I use some function to get a unique list or a loop that will do it in irder and bring my results back to the next blank row under the last results
Or could someone advise why the error occurs

Any insights would be much appreciated
Thanks in advance

 

[Chance1234]

what error is it you are getting

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[Arsenal176]

Hi I got an invalid type error ?
And the the ".Sql = Array( _ ...." was highlighted in yellow. Not knowing anything I just guessed it was because the array was too long ?
I have changed the code to be (again guessing with hit and misses!)

Dim a As Variant
etc for all values

a = Range("aa2").Value
etc


Dim query As String

query = "select * FROM cgsft_changes.dbo.table1 WHERE table1.ID in (" & a & ", " & b & ", " & c & ", " & d & ", " & e & ", " & f & ", .......

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER=SQL Server;SERVER=****;UID=*****;APP=Microsoft® Query;DATABASE=***;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("A1"))
.Sql = query
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With




Now I get a runtime error 1004
SQL Syntax Error
for

.Refresh BackgroundQuery:=False


I am sorry I know very little about what I am at

 

[Chance1234]

could it be in the first instant that one of your ranges contains a null value or a date value, etc which is not of the data type you are looking up ? or are there spaces in the values and its agaist a text field, in which case you will need to add quotes to the in statement,

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[Arsenal176]

Hi
In my code before even the Dim for a b c etc
I have
Range("AA2:AA58").Select
Selection.Replace What:="", Replacement:="-123", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

The -123 is just a value that I know will never exist
and the format of the cells is a number
It seems to work for about 10 variable but when I put more in it gives an error
of invalid type

 

[Chance1234]

can you post the full code for the module, and also some examples of your values in AA2:58


Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[SkipVought]

BTW,

Once you have ADDED your querytable, you need only execute code like this...

   With ActiveSheet.QueryTables(1)
        .Connection="ODBC;DRIVER=SQLServer;SERVER=****;UID=*****;APP=Microsoft® Query;DATABASE=***;Trusted_Connection=Yes"
        .Sql = query
        .Refresh BackgroundQuery:=False
    End With

Loose the Array() stuff!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]