修改VBA中现有的excel连接名称 [英] Modify existing excel Connection Name in VBA

问题描述

我需要更新excel工作簿的连接名称。这是我的尝试。我已经能够通过标准替换来修改连接字符串和命令文本。

I need to update the Connection Name of an excel workbook's sql connection. This is my attempt. I have been able to modify the Connection String and Command Text by doing a standard replace.

Sub ConnectionString_modify()

Dim i As Long
Dim cnt As Long
Dim modtext As String
Dim modrange As String

'Grab nummber of workbook connections
cnt = ActiveWorkbook.Connections.Count

For i = 1 To cnt

    'Changes to Connection string --This works
    modtext = ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection
    modtext = VBA.Replace(modtext, "_FY2013", "_FY2014")
    ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection = modtext

    'Changes Connection Name
    modname = ActiveWorkbook.Connections.Item(i).Name
    modname = VBA.Replace(modname, "_FY2013", "_FY2014")
    ActiveWorkbook.Connections.Item(i).Name = modname 
 Next i

End sub

任何帮助都会很好。谢谢。

Any help would be great. Thanks.

推荐答案

尝试这样：

Sub ConnectionString_modify()

Dim i As Long
Dim cnt As Long
Dim modtext As String
Dim modrange As String
Dim conn

'Grab nummber of workbook connections
cnt = ActiveWorkbook.Connections.Count

For i = cnt To 1 Step -1

    Set conn = ActiveWorkbook.Connections.Item(i)

    modtext = conn.OLEDBConnection.Connection
    modtext = VBA.Replace(modtext, "_FY2013", "_FY2014")
    conn.OLEDBConnection.Connection = modtext

    conn.Name = VBA.Replace(conn.Name, "_FY2013", "_FY2014")

Next i

End sub

这篇关于修改VBA中现有的excel连接名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！