Excel VBA更改连接路径，但保留分隔符和列格式

Sub ChangeConnectionPath()
    Dim conn As WorkbookConnection
    Dim oldPath As String
    Dim newpath As String
    
    ' 获取第一个连接
    Set conn = ThisWorkbook.Connections(1)
    
    ' 获取当前连接路径
    oldPath = conn.OLEDBConnection.Connection
    
    ' 设置新的连接路径
    newpath = "新的连接路径"
    conn.OLEDBConnection.Connection = newpath
    
    ' 刷新连接
    conn.Refresh
End Sub

Sub ChangeConnectionPathWithFormat()
    Dim conn As WorkbookConnection
    Dim oldPath As String
    Dim newpath As String
    Dim delimiter As String
    Dim columnFormat As String
    
    ' 获取第一个连接
    Set conn = ThisWorkbook.Connections(1)
    
    ' 获取当前连接路径
    oldPath = conn.OLEDBConnection.Connection
    
    ' 获取当前分隔符和列格式
    delimiter = conn.TextFileColumnDataTypes(1).Delimiter
    columnFormat = conn.TextFileColumnDataTypes(1).ColumnDataTypes
    
    ' 设置新的连接路径
    newpath = "新的连接路径"
    conn.OLEDBConnection.Connection = newpath
    
    ' 恢复分隔符和列格式
    conn.TextFileColumnDataTypes(1).Delimiter = delimiter
    conn.TextFileColumnDataTypes(1).ColumnDataTypes = columnFormat
    
    ' 刷新连接
    conn.Refresh
End Sub