Modifier une chaîne de connexion intégrée dans la macro Microsoft excel

je pense que vous êtes si près d'obtenir ce que vous voulez.

j'ai été capable de changer de ODBCConnection. Désolé que je ne pouvais pas le programme d'installation Oldbconnection pour tester, vous pouvez modifier les occurrences de ODBCConnectionOldbconnection dans votre cas.

essayez d'ajouter ces 2 sous-titres avec modification, et ajoutez ce que vous devez remplacer dans le CommandText et Chaîne De Connexion. Note que j'ai mis .Refresh mettre à jour la connexion, vous ne pouvez pas besoin jusqu'à l'actualisation des données est nécessaire.

vous pouvez changer d'autres champs en utilisant la même idée de casser des choses puis les rejoindre plus tard:

Private Sub ChangeConnectionString(sInitialCatalog As String, sDataSource As String)
    Dim sCon As String, oTmp As Variant, i As Long
    With ThisWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection
        sCon = .Connection
        oTmp = Split(sCon, ";")
        For i = 0 To UBound(oTmp) - 1
            ' Look for Initial Catalog
            If InStr(1, oTmp(i), "Initial Catalog", vbTextCompare) = 1 Then
                oTmp(i) = "Initial Catalog=" & sInitialCatalog
            ' Look for Data Source
            ElseIf InStr(1, oTmp(i), "Data Source", vbTextCompare) = 1 Then
                oTmp(i) = "Data Source=" & sDataSource
            End If
        Next
        sCon = Join(oTmp, ";")
        .Connection = sCon
        .Refresh
    End With
End Sub

Private Sub ChangeCommanText(sCMD As String)
    With ThisWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection
        .CommandText = sCMD
        .Refresh
    End With
End Sub

vous pouvez utiliser une fonction qui prend la connexion Oldb et les paramètres à mettre à jour comme entrées, et renvoie la nouvelle chaîne de connexion. C'est similaire à la réponse de Jzz mais permet une certaine flexibilité sans avoir à éditer la chaîne de connexion dans le code VBA à chaque fois que vous voulez le changer - au pire, vous devrez ajouter de nouveaux paramètres aux fonctions.

Function NewConnectionString(conTarget As OLEDBConnection, strCatalog As String, strDataSource As String) As String

    NewConnectionString = conTarget.Connection
    NewConnectionString = ReplaceParameter("Initial Catalog", strCatalog)
    NewConnectionString = ReplaceParameter("Data Source", strDataSource)

End Function

Function ReplaceParameter(strConnection As String, strParamName As String, strParamValue As String) As String

    'Find the start and end points of the parameter
    Dim intParamStart As Integer
    Dim intParamEnd As Integer
    intParamStart = InStr(1, strConnection, strParamName & "=")
    intParamEnd = InStr(intParamStart + 1, strConnection, ";")


    'Replace the parameter value
    Dim strConStart As String
    Dim strConEnd As String
    strConStart = Left(strConnection, intParamStart + Len(strParamName & "=") - 1)
    strConEnd = Right(strConnection, Len(strConnection) - intParamEnd + 1)

    ReplaceParameter = strConStart & strParamValue & strConEnd

End Function

notez que j'ai modifié ceci à partir du code existant que j'ai utilisé pour une application particulière, donc c'est partiellement testé et pourrait avoir besoin de quelques retouches avant qu'il ne réponde totalement à vos besoins.

Notez aussi qu'il aura besoin d'une sorte de code d'appel, ce qui serait (en supposant que le nouveau catalogue et la nouvelle source de données sont stockés dans des cellules de feuille de travail):

Sub UpdateConnection(strConnection As String, rngNewCatalog As Range, rngNewSource As Range)

    Dim conTarget As OLEDBConnection
    Set conTarget = ThisWorkbook.Connections.OLEDBConnection(strConnection)

    conTarget.Connection = NewConnectionString(conTarget, rngNewCatalog.Value, rngNewSource.Value)
    conTarget.Refresh

End Sub

Ceci devrait faire l'affaire:

Sub jzz()

Dim conn As Variant
Dim connectString As String

For Each conn In ActiveWorkbook.Connections
    connectString = conn.ODBCConnection.Connection
    connectString = Replace(connectString, "Catalog=ADCData_Doric", "Catalog=Whatever")
    connectString = Replace(connectString, "Data Source=doric-server5", "Data Source=Whatever")

    conn.ODBCConnection.Connection = connectString
Next conn


End Sub

il boucle chaque connexion dans votre classeur et change la chaîne de connexion (dans les 2 déclarations remplacer).

donc pour modifier votre exemple:

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").ODBCConnection.Connection = "new connection string"

je voudrais donner ma petite contribuer ici à ce vieux sujet. Si vous avez beaucoup de liens dans votre fichier Excel, et vous voulez changer le nom DB et DB serveur pour le tout, vous pouvez utiliser le code suivant:

• Il parcourt toutes les connexions et les extraits de la chaîne de connexion
• chaque chaîne de connexion est divisée en un tableau de chaînes
• Il parcourt le tableau à la recherche pour le droit des valeurs de connexion pour modifier, les autres ne sont pas touchés
• l'it recompose le tableau dans la chaîne et commit le changement

de Cette façon, vous n'avez pas besoin de remplacer et d'en connaître la valeur précédente, et le reste de la chaîne restera intacte. En outre, nous pouvons faire référence à un nom de cellule, de sorte que vous pouvez avoir des noms dans votre fichier Excel

j'espère que ça peut aider

Sub RelinkConnections()

Dim currConnValues() As String

For Each currConnection In ThisWorkbook.Connections
    currConnValues = Split(currConnection.OLEDBConnection.Connection, ";")
    For i = 0 To UBound(currConnValues)
        If (InStr(currConnValues(i), "Initial Catalog") <> 0) Then
            currConnValues(i) = "Initial Catalog=" + Range("DBName").value
        ElseIf (InStr(currConnValues(i), "Data Source") <> 0) Then
            currConnValues(i) = "Data Source=" + Range("DBServer").value
        End If
    Next
    currConnection.OLEDBConnection.Connection = Join(currConnValues, ";")
    currConnection.Refresh
Next

End Sub

je suppose qu'il est nécessaire pour vous de garder la même connexion-nom? Sinon, il serait plus simple de l'ignorer et de créer une nouvelle Connexion.

Vous pouvez renommer la connexion, et en créer un nouveau en utilisant le nom:

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Name = "temp"
'or, more drastic:
'ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Delete

ActiveWorkbook.Connections.Add "Job_Cost_Code_Transaction_Summary", _
    "a description", "new connection string", "command text" '+ ,command type

Ensuite, Delete cette connexion et de rétablir l'ancienne connexion/nom. (Je ne peux pas tester moi-même actuellement, alors prudemment.)

alternativement, vous pouvez changer les connexions actuelles SourceConnectionFile:

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.SourceConnectionFile = "..file location.."

cela renvoie typiquement à un .odc fichier (Office Data Connection) enregistré sur votre système qui contient les détails de la connexion. Vous pouvez créer ce fichier à partir du Panneau de contrôle de la fenêtre.

vous n'avez pas précisé, mais un .odc fichier est peut-être ce que votre connexion actuelle.

encore une fois, je ne suis pas en mesure de tester ces suggestions, donc vous devriez enquêter davantage et prendre quelques précautions - afin que vous ne risque de perdre les détails de la connexion actuelle.