Microsoft Excel 매크로에 포함된 연결 문자열 수정

Microsoft Excel 매크로에 포함된 연결 문자열 수정

시 가 있습니다.CommandText다음과 같이 Excel 스프레드시트에서 매개 변수를 전달할 연결:

Sub RefreshData()
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary")
  .OLEDBConnection.CommandText = "Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate='" & Worksheets("Cost to Complete").Range("MonthEndDate").Value & "', @job ='" & Worksheets("Cost to Complete").Range("Job").Value & "'"
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Refresh
End Sub

새로 고침을 통해 연결 명령을 수정할 뿐만 아니라 다른 데이터베이스에서도 사용할 수 있도록 연결을 수정하고 싶습니다.

매크로가 명령 매개 변수를 스프레드시트의 값으로 바꾸듯이 데이터베이스 서버 이름과 데이터베이스 이름도 스프레드시트의 값으로 바꾸었으면 합니다.

완전한 구현은 필요하지 않으며, 시트의 값으로 연결을 수정하는 코드로 충분할 것입니다. 거기서 작동시킬 수 있을 것입니다.

저는 다음과 같은 일을 하려고 했습니다.

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

하지만 그것은 효과가 없습니다.감사해요.

제 질문에 대한 답은 아래와 같습니다.

다른 답변들은 대부분 정답이고 현재 연결을 수정하는 데 초점을 맞추고 있지만, 연결에 연결 문자열을 설정하는 방법을 알고 싶습니다.

벌레가 여기까지 내려왔습니다.내 스크린샷을 보면 연결 문자열이 다음과 같았음을 알 수 있습니다.

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False

는 그 에 저는그끈맞했습다니고려추을▁that▁with다했니▁set▁string▁to습▁i▁was를 설정하려고 했습니다.ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "connection string"

연결에 전체 문자열을 할당하려고 할 때 오류가 발생했습니다.해당 속성으로 현재 연결 문자열을 MsgBox로 전송할 수 있었지만 오류가 발생하지 않고 연결 문자열을 다시 설정하지 못했습니다.

그 이후로 연결 문자열이 다음과 같아야 한다는 것을 알게 되었습니다.OLEDB;문자열 앞에 추가됩니다.

그래서 이것은 이제 작동합니다!!!

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False"

매우 미묘하지만 그것은 벌레였습니다!

저는 당신이 원하는 것을 이룰 수 있는 시간이 가깝다고 생각합니다.

ODBC 연결을 위해 변경할 수 있었습니다.테스트할 OLEDB 연결을 설정하지 못해 죄송합니다. 귀하의 경우 ODBC 연결과 OLEDB 연결을 변경할 수 있습니다.

수정 사항과 함께 이 2개의 하위 항목을 추가하고 명령에서 대체할 내용을 입력합니다.텍스트 및 연결 문자열입니다.메모해 두었습니다..Refresh실제 데이터 새로 고침이 필요할 때까지 연결을 업데이트할 필요가 없습니다.

다른 필드도 같은 개념으로 변경하여 나중에 참여할 수 있습니다.

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

OLEDB 연결과 업데이트할 파라미터를 입력으로 사용하고 새 연결 문자열을 반환하는 기능을 사용할 수 있습니다.Jzz의 답변과 비슷하지만 변경할 때마다 VBA 코드 내에서 연결 문자열을 편집할 필요 없이 어느 정도 유연하게 사용할 수 있습니다. 최악의 경우 기능에 새 매개 변수를 추가해야 합니다.

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

특정 응용 프로그램에 사용했던 기존 코드에서 이 코드를 수정했으므로 부분적으로 테스트되었으며 사용자의 요구 사항을 완전히 충족하려면 수정이 필요할 수 있습니다.

또한 새로운 카탈로그와 데이터 소스가 워크시트 셀에 저장되어 있다고 가정할 때 다음과 같은 호출 코드도 필요합니다.

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

저는 이 오래된 주제에 작은 기여를 하고 싶습니다.Excel 파일에 많은 연결이 있고 모든 연결에 대한 DB 이름과 DB 서버를 변경하려는 경우 다음 코드도 사용할 수 있습니다.

• 모든 연결을 반복하고 연결 문자열을 추출합니다.
• 각 연결 문자열은 문자열 배열로 분할됩니다.
• 어레이를 통해 수정할 올바른 연결 값을 반복적으로 검색합니다. 다른 연결 값은 건드리지 않습니다.
• 배열을 문자열로 재구성하고 변경 내용을 커밋합니다.

이렇게 하면 replace 및 이전 값을 사용할 필요가 없으며 나머지 문자열은 그대로 유지됩니다.또한 셀 이름을 참조할 수 있으므로 엑셀 파일에 이름이 있을 수 있습니다.

도움이 되길 바랍니다.

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

이렇게 하면 효과가 있습니다.

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

워크북의 모든 연결을 반복하고 연결 문자열을 변경합니다(두 개의 대체 문).

예제를 수정하려면 다음을 수행합니다.

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

동일한 연결 이름을 유지해야 합니까?그렇지 않으면 이를 무시하고 새 연결을 만드는 것이 가장 간단합니다.

연결 이름을 변경하고 다음 이름을 사용하여 새 연결을 만들 수 있습니다.

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

그 후에,Delete이 연결을 사용하여 이전 연결/이름을 복원합니다. (현재 직접 테스트할 수 없으므로 주의하십시오.)

또는 현재 연결을 변경할 수 있습니다.SourceConnectionFile:

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

일반적으로 연결 세부 정보가 들어 있는 시스템에 저장된 .odc 파일(Office Data Connection)을 참조합니다.이 파일은 창의 제어판에서 만들 수 있습니다.

지정하지 않았지만 현재 연결에서 사용 중인 .odc 파일일 수 있습니다.

다시 한 번 이러한 제안을 테스트할 수 없으므로 추가로 조사하여 현재 연결 세부 정보가 손실되지 않도록 예방 조치를 취해야 합니다.

언급URL : https://stackoverflow.com/questions/20114223/modify-an-embedded-connection-string-in-microsoft-excel-macro