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

E

Ev

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
 
J

Jeff Standen

Yep. Instead of

Database=Master;

use

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

Jeff
 
E

Ev

Thanks Jeff

Jeff Standen said:
Yep. Instead of

Database=Master;

use

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

Jeff

Ev said:
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
Click to expand...
Click to expand...
 
J

Jeff Standen

No problem.

Jeff

Ev said:
Thanks Jeff

Jeff Standen said:
Yep. Instead of

Database=Master;

use

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

Jeff

Ev said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database query. 6
a better way to query? 2
Not able to declare variable in MS Query. 2
Refernce to cell in SQL statemen 1
Link to database 2
Variable passed to database query 3
Variable passed to database query 1
Refresh Query based on variable 1

Top