Using VBA to change a connection string definition in existing VBA or a better solution altogether?

[demingm13]

Thanks in advance for anyone who reads this and takes the time to respond.

First, I will preface that I am new to VBA and I didn't create the code I am trying to change. With that being said...

We have some spreadsheets that use VBA to create a connection string which is used to connect to a database in SQL Server, and then pull back data in a table using a SQL statement (also embedded in the VBA). This has been working for us just fine so far.

However, we are moving the database which is referenced in the connection string, to a new server. This wouldn't be a problem if we only had a few spreadsheets to update, but unfortunately we have 100's if not 1000's of these to change. Don't even get me started on that topic...

The code to create the connection string looks like this:

Const stCon = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXXXXX;Data Source=XXXXXX;"

The only thing that is changing is the Data Source, the database name is staying the same so no need to change anything else. As far as I know, the macro that contains this VBA is always called the same thing and is identical in each spreadsheet. It looks like people just did a save as from a base spreadsheet and created multiple versions over time.

I'm looking at tackling this in two ways. The first approach is more of a bandaid quick fix, and that is to create some VBA to change the Data Source in the macro in question in the above connection string statement. The second approach, which would be more future proof, is to figure out a way to have the connection string built so that it isn't hardcoded in every single spreadsheet.

Mind you, I don't know how to do either of these, and that is why I came here. Any suggestions on where to get started would be greatly appreciated. If you need more information to give me suggestions, please ask!

 

[Worf]

Welcome to the Board
The following example shows how to implement a variable data source, in this case from a worksheet cell.

Sub Main()
Gdata CStr(ThisWorkbook.Sheets("shops").[c85]), "Sheet1", "a1:d20"
End Sub

Sub Gdata(f, sht$, Rng$)
Dim con As Object, rs As Object, sql$, X
sql = "SELECT * FROM [" & sht$ & "$" & Rng$ & "];"
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
f & ";" & "Extended Properties=""Excel 12.0;HDR=No"";"
rs.Open sql, con, 0, 1, 1
X = rs.getrows
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
MsgBox UBound(X, 1) & " " & UBound(X, 2)
End Sub