Using cell reference in a connection string

Question

I am using the below connection string in my excel VBA. But I need to set it up in a way that the database and server is picked from an cell like A$1$. So I can change the database details when ever I need.

Function Connect(Server As String, _
                 Database As String) As Boolean

    Set CN = New ADODB.Connection
    On Error Resume Next

    With CN
        ' Create connecting string
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
                            "Integrated Security=SSPI;" & _
                            "Server=" & Server & ";" & _
                            "Database=" & Database & ";"
        ' Open connection
        .Open
    End With
    ' Check connection state
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If

End Function

Thanks in advance

mechanical_meat · Accepted Answer · 2014-10-17 23:37:55Z

3

Something like:

Sub check_database_connectivity()
    Dim server_name As String
    Dim database_name As String

    server_name = ActiveSheet.Range("A1").Value
    database_name = ActiveSheet.Range("A2").Value

    If Connect(server_name, database_name) = True Then 
        'do something
    End If
End Sub

answered Oct 17, 2014 at 23:37

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using cell reference in a connection string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related