Change Connection String in multiple workbooks (vba)

[Chrissy]

Hello,

I have many Excel workbooks that have a connection to a cloud based SQL server database. The IP address has since changed and I need to go into all of the workbooks and change the connection.

My question is: Going forward, is there a way to store the connection information in one spot so I don't have to go into all workbooks to change the connection information? I wasn't sure what the best practice for this was... any advice is welcome.

Thank you!
Chrissy

[Zvoni]

Depends.

Are you the only user accessing this Server from those Workbooks?
If yes, you could store the connection in the Registry, or even more simple: a textfile on your computer (filename/location should not be changed!)

If there are other users accessing this server with this connection, then a textfile in a central spot (file-server?) would be easiest (see above for filename/location)

In any case it would just be a simple read-instruction in Workbook_Open

[Chrissy]

There are multiple users.

Thank you for your reply. I wasn't sure if there was a better way. I tried searching and someone mentioned an add-in, but I don't think that is what I need.

[westconn1]

someone mentioned an add-in,

there may be some addin to look in every opened workbook for the connection string then edit it, but i would go with some external storage of the ip address, as suggested above, if the multiple users are not in a single location (LAN), then the file could be on some fixed server (as long as it's address can't change)

edit
for an initial instance, to change from a static internal string to a external file source,
to update all workbooks (as they are opened) for a machine, it would be fairly easy to put code into the workbooks_open event of the personal workbook to search for the connection string and edit to use an external source, or you could write an addin to do the same so it could be distributed