Change Database that MS Query points to based on cell value Excel

Hi All,
I have an excel spreadsheet which gets data from an SQL database via MS
query, I need to repeat exactly the same query for some 30 databases.
Is it possible to link the database name to a value in a cell using
Range(a1).Value or something similar?

Selection.QueryTable
.Connection = Array(Array( _


"ODBC;DSN=mysqldb;Description=mysqldb;UID=Password;PWD=Password;APP=Microso*ft
Office 2003;WSID=XYZ;DATABASE=Master;Network=DBMSSOCN" _
), Array(";AutoTranslate=No;QuotedId=No;AnsiNPW=No"))


.CommandText = Array( _
"SELECT GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_2,
GL00100.ACTNUMBR_3, GL00100.ACTDESCR, GL00100.ACTALIAS,
Sum(GL10110.PERDBLNC) AS '$'" & Chr(13) & "" & Chr(10) & "FROM
[Range(a1).value & ".dbo.GL00100 GL00100, FROM [Range(a1).value
..dbo.GL10110 GL10110" & Chr(13) & "" & Chr(10) & "WHERE GL00100.ACTINDX
= GL10110.ACTINDX" & Chr(13) & "" & Chr(10)


Thanks

Yep. Instead of

Database=Master;

use

Database=" & Range("a1").value & ";

Jeff

"Ev" <[email protected]> wrote in message
news:OOP$f%[email protected]...
> Hi All,
> I have an excel spreadsheet which gets data from an SQL database via MS
> query, I need to repeat exactly the same query for some 30 databases.
> Is it possible to link the database name to a value in a cell using
> Range(a1).Value or something similar?
>
> Selection.QueryTable
> .Connection = Array(Array( _
>
>
> "ODBC;DSN=mysqldb;Description=mysqldb;UID=Password;PWD=Password;APP=Microso*ft
> Office 2003;WSID=XYZ;DATABASE=Master;Network=DBMSSOCN" _
> ), Array(";AutoTranslate=No;QuotedId=No;AnsiNPW=No"))
>
>
> .CommandText = Array( _
> "SELECT GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_2,
> GL00100.ACTNUMBR_3, GL00100.ACTDESCR, GL00100.ACTALIAS,
> Sum(GL10110.PERDBLNC) AS '$'" & Chr(13) & "" & Chr(10) & "FROM
> [Range(a1).value & ".dbo.GL00100 GL00100, FROM [Range(a1).value
> .dbo.GL10110 GL10110" & Chr(13) & "" & Chr(10) & "WHERE GL00100.ACTINDX
> = GL10110.ACTINDX" & Chr(13) & "" & Chr(10)
>
>
> Thanks
>
>
>

Thanks Jeff

"Jeff Standen" <[email protected]> wrote in message
news:[email protected]...
> Yep. Instead of
>
> Database=Master;
>
> use
>
> Database=" & Range("a1").value & ";
>
> Jeff
>
> "Ev" <[email protected]> wrote in message
> news:OOP$f%[email protected]...
>> Hi All,
>> I have an excel spreadsheet which gets data from an SQL database via MS
>> query, I need to repeat exactly the same query for some 30 databases.
>> Is it possible to link the database name to a value in a cell using
>> Range(a1).Value or something similar?
>>
>> Selection.QueryTable
>> .Connection = Array(Array( _
>>
>>
>> "ODBC;DSN=mysqldb;Description=mysqldb;UID=Password;PWD=Password;APP=Microso*ft
>> Office 2003;WSID=XYZ;DATABASE=Master;Network=DBMSSOCN" _
>> ), Array(";AutoTranslate=No;QuotedId=No;AnsiNPW=No"))
>>
>>
>> .CommandText = Array( _
>> "SELECT GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_2,
>> GL00100.ACTNUMBR_3, GL00100.ACTDESCR, GL00100.ACTALIAS,
>> Sum(GL10110.PERDBLNC) AS '$'" & Chr(13) & "" & Chr(10) & "FROM
>> [Range(a1).value & ".dbo.GL00100 GL00100, FROM [Range(a1).value
>> .dbo.GL10110 GL10110" & Chr(13) & "" & Chr(10) & "WHERE GL00100.ACTINDX
>> = GL10110.ACTINDX" & Chr(13) & "" & Chr(10)
>>
>>
>> Thanks
>>
>>
>>
>
>

No problem.

Jeff

"Ev" <[email protected]> wrote in message
news:[email protected]...
> Thanks Jeff
>
> "Jeff Standen" <[email protected]> wrote in message
> news:[email protected]...
>> Yep. Instead of
>>
>> Database=Master;
>>
>> use
>>
>> Database=" & Range("a1").value & ";
>>
>> Jeff
>>
>> "Ev" <[email protected]> wrote in message
>> news:OOP$f%[email protected]...
>>> Hi All,
>>> I have an excel spreadsheet which gets data from an SQL database via MS
>>> query, I need to repeat exactly the same query for some 30 databases.
>>> Is it possible to link the database name to a value in a cell using
>>> Range(a1).Value or something similar?
>>>
>>> Selection.QueryTable
>>> .Connection = Array(Array( _
>>>
>>>
>>> "ODBC;DSN=mysqldb;Description=mysqldb;UID=Password;PWD=Password;APP=Microso*ft
>>> Office 2003;WSID=XYZ;DATABASE=Master;Network=DBMSSOCN" _
>>> ), Array(";AutoTranslate=No;QuotedId=No;AnsiNPW=No"))
>>>
>>>
>>> .CommandText = Array( _
>>> "SELECT GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_2,
>>> GL00100.ACTNUMBR_3, GL00100.ACTDESCR, GL00100.ACTALIAS,
>>> Sum(GL10110.PERDBLNC) AS '$'" & Chr(13) & "" & Chr(10) & "FROM
>>> [Range(a1).value & ".dbo.GL00100 GL00100, FROM [Range(a1).value
>>> .dbo.GL10110 GL10110" & Chr(13) & "" & Chr(10) & "WHERE GL00100.ACTINDX
>>> = GL10110.ACTINDX" & Chr(13) & "" & Chr(10)
>>>
>>>
>>> Thanks
>>>
>>>
>>>
>>
>>
>
>