Referencing a public constant using a variable

[johncassell]

Hi all,

I very rarely use public constants and I'm struggling with this bit: I am trying to start a connection to a database. We have 2 different databases and I am declaring a Public Constant for each data Location. On my spreadsheet I have a cell which the user can enter the database name. So for example, cell A1 would contain 'Location1'

In the code below you will see that I want to use this cell A1 variable and add the word "Data" on the end to reference the correct Public Const but this way I get an error "Data Source Not Found". I know the data source is correct though as if I don't use the variable and simply put Location1Data in the code then it works. Would someone be able to help please?

Please Login or Register  to view this content.

[Kyle123]

You can't build up a variable name like that.

You've got a few options, one of the easiest might be to use a named range.

Put the connection strings in cells on a worksheet and give the cells names (Location1Data etc), you could then use something like:

Please Login or Register  to view this content.

[johncassell]

Hi, sorry I replied to this but then checked back and it obviously didn't work. Thanks for the reply and that works perfect. Thank you