VBA to update connection string based on value of a cell

[emilyallison]

Hello,
I have a connection string that contains an identity catalog that is unique to each location. I want to be able to run a script to update the connection string based on value of a cell in my workbook. I'm not familiar with VBA so I'm struggling to understand where I need to start.

Thank you in advance!

 

[Kaper]

Probably the easy way to do it for a newbie will be by using a named range in a worksheet.
Then your VBA code could load into this cell each connection string in a loop (well as a matter of fact it can be just name, not a cell, but a cell will make debugging easier). Then you can refresh the query, do your tasks with the query output and let the loop load next location parameters into thics cell and so on.

How to replace "hardcoded" connection with named range - see for instance Change the Power Query source based on a cell value