Excel VBA to parse SQL Connection Strings

Question

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.

EoinS · Accepted Answer · 2016-05-23 23:59:47Z

2

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.

edited May 23, 2016 at 23:59

answered May 23, 2016 at 23:46

EoinS

5,4921 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

V. Wolf · Accepted Answer · 2016-05-26 07:52:04Z

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!

Collectives™ on Stack Overflow

Excel VBA to parse SQL Connection Strings

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related