在连接字符串中使用单元格引用

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.

我在excel VBA中使用以下连接字符串。但是我需要以一种从A $ 1 $这样的单元格中挑选数据库和服务器的方式进行设置。所以我可以在需要时更改数据库详细信息。

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

提前致谢

1 个解决方案

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