Q:

Conneting Oracle database with Excel using Macros

I'm trying to connect to oracle database with excel using macros. 
I was able to do it by writing the userid and password in the code which makes it static. I want to connect to the database using a login popup and make it dynamic.
I have created a sheet and was trying to do the same using the Vlookup functionality, but the code is not executing properly.
Here is the code I have used to connect to the database:
strConnection = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=xxxxxx)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=xxx)(HOST=xxxx)(PORT=xxxx)))" & _
                "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxx)));" & _
                "User Id=myusrid;" & _
                "Mypswd;"

Any help or suggestion is appreciated.

A:

Let's undestand the basics first. Consider this code
s = "This is a sample string with ID:=MyID and password:=MyPassword"

Let's say you want to replace MyID and MyPassword at run time. So all you have to do is store the details in 2 variables and then use it in the main string. For Example
Sub sample()
    Dim s As String
    Dim id As String, pass As String

    id = "Sid"
    pass = "SomePassword"

    s = "This is a sample string with ID:=" & id & " and password:=" & pass

    Debug.Print s
End Sub

When you run this, you will see that the values have been replaced. 

Now let's apply this to your question.
Your string can be written as
strConnection = "Provider=OraOLEDB.Oracle;" & _
                "Data Source=(DESCRIPTION=(CID=xxxxxx)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=xxx)(HOST=xxxx)(PORT=xxxx)))" & _
                "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxx)));" & _
                "User Id=" & ID & ";" & _
                PASS & ";"

Now all you have to do is accept the input from the user and store then in ID and PASS.

