|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub SQL适应路径和文件名更改()
- Dim strCon As String, iPath As String
- Dim i As Integer, j As Integer, iFlag As String, iStr As String
- Dim iT As Integer, jT As Integer
- Dim sht As Worksheet
- '定义变量
- iPath = ThisWorkbook.FullName '获取本工作簿的完全路径
- On Error Resume Next
- For Each sht In ThisWorkbook.Worksheets
- 'SQL查询表的处理
- i = sht.QueryTables.Count
- If i > 0 Then
- For j = 1 To i
- strCon = sht.QueryTables(j).Connection
- '遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
- Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
- Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式
- iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
- Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
- iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag
- Case Else '没有引入外部数据或其他方式,不予处理
- Exit Sub
- End Select
- iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息
- With sht.QueryTables(j) '替换SQL查询表中缓存信息中的文件完全路径
- .Connection = VBA.Replace(strCon, iStr, iPath)
- .CommandText = VBA.Replace(.CommandText, iStr, iPath)
- End With
- Next j
- End If
- '数据透视表的处理
- iT = sht.PivotTables.Count
- If iT > 0 Then
- For jT = 1 To iT
- strCon = sht.PivotTables(jT).PivotCache.Connection
- '遍历工作表中数据透视表中缓存连接信息赋值给变量strCon
- Select Case Left(strCon, 5) 'select case语句,条件为strCon变量中从左侧取5个字符
- Case "ODBC;" '判断缓存连接信息中的数据连接方式,如果是ODBC方式
- iFlag = "DBQ=" '将"DBQ=" 赋值给变量iFlag
- Case "OLEDB" '判断缓存连接信息中的数据连接方式,如果是OLEDB方式
- iFlag = "Source=" '将"DBQ=" 赋值给变量iFlag
- Case Else '没有引入外部数据或其他方式,不予处理
- Exit Sub
- End Select
- iStr = Split(Split(strCon, iFlag)(1), ";")(0) '在变量strCon中截取文件路径信息
- With sht.PivotTables(jT).PivotCache '替换据透视表缓存信息中的文件完全路径
- .Connection = VBA.Replace(strCon, iStr, iPath)
- .CommandText = VBA.Replace(.CommandText, iStr, iPath)
- End With
- Next
- End If
- Next
- 'SQL查询表的处理2
- For Each Conn In ThisWorkbook.Connections
- With Conn.OLEDBConnection
- .SourceDataFile = ThisWorkbook.FullName
- Arr = .Connection
- Brr = VBA.Split(Arr, ";")
- For i = 1 To UBound(Brr)
- If VBA.InStr(Brr(i), "Data Source") Then
- Brr(i) = "Data Source=" & ThisWorkbook.FullName
- Exit For
- End If
- Next
- Arr = VBA.Join(Brr, ";")
- .Connection = Arr
- End With
- Next
- End Sub
复制代码 |
|