Referencing a public constant using a variable

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?

Public Const Location1Data As String = "Provider=vfpoledb;Data Source=C:\MyFolder\Database.DBC; Collating Sequence=machine;"
Public Const Location2Data As String = "Provider=vfpoledb;Data Source=C:\MyFolder2\Database.DBC; Collating Sequence=machine;"
 
Sub MySub

cnnConnect.Open Range("A1").value & "Data"
End Sub

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:

Sub MySub
    cnnConnect.Open Range(Range("A1").value & "Data").Value
End Sub

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