|
excel 2010以上版本使用下面代码更改xlsx文件里所有的数据连接代码,注:所有commandtext 查询语句的数据源只能为一个表格文件。查询不同路径的sql语句,需要加入楼主的commandtext代码。
Private Sub Workbook_Open()
Dim strCon As String, i As Integer, iStr As String
Const dSrcPath = "\数据源\数据.xlsx"
For i = 1 To Connections.Count
If Connections(i).Type = 1 Then ' xlConnectionTypeOLEDB
strCon = Connections(i).OLEDBConnection.Connection
iStr = Split(Split(strCon, "Source=")(1), ";")(0)
Connections(i).OLEDBConnection.Connection = VBA.Replace(strCon, iStr, ThisWorkbook.Path & dSrcPath)
End If
Next |
|