Update multiple SQL connection strings in an Excel macro

Question

I have an Excel document with several (50+) embedded SQL queries that get refreshed and update counts and query results. I need to change the server and the database that these queries run from and I was hoping there was a simpler way to do this than manually typing everything in. I have searched through some of the other questions here but I was unable to find something that matched what I am looking to do.

Most of my connection strings look like this:

DRIVER=SQL Server;SERVER=ServerName;UID=ThisisMe;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=CEPC098JJN

What I need to add also, is the database which I believe looks like this:

Initial Catalog=DatabaseName

Any guidance on next steps on how to update all these connection strings is greatly appreciated.

My personal opinion would be to create a file outside of the application and read from the file to get the connection string.In that way you wont in the future have to deal with this again. — Miguel
– Miguel, Commented Apr 6, 2017 at 0:29
@Jeeped Kinda, but a bit more crude, since this is vba, you can technically, think of the file as a config file and even have like dbname-values and in that way you can simple retrieve the file, find the name and use that as your connection string. — Miguel
– Miguel, Commented Apr 6, 2017 at 9:14

asg · Accepted Answer · 2017-04-06 01:30:29Z

Perhaps you should use a text file to store the values as suggested by Miguel. In the application, load values into the dictionary, so they will be convenient and easy to use. For example:

Sub test()
Dim d, key
    Set d = FileLoad("c:\soft\1.txt")
    For Each key In d.Keys
        Debug.Print ("key = " & key & " Value = " & d(key))
    Next key
End Sub


Function FileLoad(path As String) As Variant
Dim str, arr, dict
Open path For Input As #1
Set dict = CreateObject("Scripting.Dictionary")
Do Until EOF(1)
    Line Input #1, str
    arr = Split(str, ":")
    dict.Add arr(0), arr(1)
Loop
Close #1
Set FileLoad = dict
End Function

Text file like: Initial Catalog:DatabaseName

And replace this values in strings...

addohm · Accepted Answer · 2017-04-06 01:01:40Z

0

use Find and Replace and select Entire Project.

Something like

Find: UID=ThisisMe;
Replace: UID=ThisisMe;Initial Catalog=DatabaseName;

answered Apr 6, 2017 at 1:01

addohm

2,5053 gold badges20 silver badges44 bronze badges

Collectives™ on Stack Overflow

Update multiple SQL connection strings in an Excel macro

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related