CODEWITHSUNDEEP
sqlexcelvbaconnection-stringoledb
Tim F
Tim F

Reputation: 23

Excel VBA to parse SQL Connection Strings

I have an Excel VBA that connects to a SQL DB using the connection strings, but I would like to know if it's possible to parse into the connection string from a cell? For instance, if I have SQLSVR1\Instance1 in cell B2 and Databas1 in B3 can i get that to be put into the connection strings like the following:

objMyConn.ConnectionString = "Provider=sqloledb; Data Source=**"B2"**; Initial Catalog=**"B3"**; Integrated Security=SSPI;"

Is that possible?

Thanks in advance.

Upvotes: 2

Views: 429

Answers (2)

V. Wolf
V. Wolf

Reputation: 123

yes it is possible as EoinS mentioned. But please be aware to determine your worksheets. For the case you would like to define the connection strings on another worksheet. Try something like this:

Dim wb as workbook, ws as worksheet

Set wb = Thisworkbook
Set ws = wb.Worksheets("TheNameofYourWorksheetsWithYourConnectionsDefined")

''Get Cnxn details
sDsn = wsc.Cells(wsc.Range("YourLabeloftheCell").Row + 1,   wsc.Range("YourLabeloftheCell").Column).Value
sUser = wsc.Cells(wsc.Range("YourLabeloftheCell").Row + 2, wsc.Range("YourLabeloftheCell").Column).Value
sPwd = wsc.Cells(wsc.Range("YourLabeloftheCell").Row + 3, wsc.Range("YourLabeloftheCell").Column).Value

sSql= GetRangeString(Worksheets("SQL").Range("A17:A22"))
check = DbSelectQueryToSheet(sDsn, "", sUser, sPwd, sSql, Worksheets("TargetWorksheet").Range("A1"), True)

Hope, this helps!

Upvotes: 0

EoinS
EoinS

Reputation: 5482

Totally possible:

objMyConn.ConnectionString =  "Provider=sqloledb; Data Source=" & range("B2").value & "; Initial Catalog=" & range("B3").value &"; Integrated Security=SSPI;"

This basically concatenates your string with the values in those cells.

Range.Value documents.

Upvotes: 2

Related Questions