SQL connection editing by VBA

[Toomicek]

Hi there,

I am using SQL query to get data from our database to excel with something like this:

select distinct kniha.ucet, ks, right(obd_rr,2) + obd_mm as 'období', NAZEV
from kniha 
inner join rozvrh on rozvrh.ucet = kniha.ucet


where obd_mh = '99' and rozvrh.obd_rh = '2017H'

It is pretty simple query and I want to use it from multiple sources (Companies)

Here is connection string:

Provider=SQLOLEDB.1;
Password=****;
Persist Security Info=True;
User ID=******;
[COLOR=#ff0000]Initial Catalog=COMPANY1;[/COLOR]
Data Source=192.168.******;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=*******;
Use Encryption for Data=False;
Tag with column collation when possible=False

I want to change red value in excel so i do not need to manualy enter value every time I want to change source. Is it possible?

Thank you

Tomas
CZECH REPUBLIC

 

[ranman256]

Re: SQL connection editting by VBA

I have a CONFIG sheet.
i put values in cells.

initial Catalog=sheets("config").range("a3").value

 

[Toomicek]

Re: SQL connection editting by VBA

I have a CONFIG sheet.
i put values in cells.

initial Catalog=sheets("config").range("a3").value

Can I ask you for whole code? I am not that experienced to write it by myself.

Thank you

 

[ranman256]

Re: SQL connection editting by VBA

I did. See above.

 

[Toomicek]

Re: SQL connection editting by VBA

I did. See above.

I ma little bit confused. I am asking if it is possible to put it in whole VBA code like:

Sub myconnection()

CODE

end sub

I am not capable to write everything by myself. I know i should be, but its learning by steps and I am not that far right now.

Thank you

 

[Toomicek]

Re: SQL connection editting by VBA

Bump