Store variable in worksheet and use that variable in VBA code

Hello,

Hoping somebody can help.

I have a bit of VBA code in my workbook which opens up an ODBC connection to a database.

With ActiveSheet.ListObjects.Add(SourceType:=0, source:= _
"ODBC;DSN=MYDATABASE;UserID=HAR;Password=HAR;", Destination:=Range("$A$1")).QueryTable......

I have it all working but I want the "MYDATABASE", "USERID" and "Password" to be variables which use data on another sheet tab.

for example.. on a connections sheet tab store them in B1, B2, B3


I thought storing the variables would be something like this

Dim UserID As String
UserID = "sheets.("connections").range.("B1")"

I'm novice in terms of understanding some of the code... I'm learning how code works as I piece bits together.

So I'd like help to provide how I store the variable and then how to include it in the VBA code where I need it.

Help would be much appreciated

Almost:

Please Login or Register  to view this content.

Perfect.. although I had got the full stops wrong after sheets and after range

UserID = sheets.("connections").range.("B1").value
Password = sheets.("connections").range.("B2").value

UserID = sheets("connections").range("B1").value
Password = sheets("connections").range("B2").value

Thanks for your help. Very useful