楼主
whsfhwm |
Q:如何让所有的引入外部数据的透视表和SQL查询表自适应路径和文件名更改
A:在工作簿Open事件中，调用如下宏可以实现：
- '适用于本工作簿中所有工作表的SQL查询表，透视表采用引入外部数据方式对本工作簿中的连接
- '适应路径和文件名更改,但基本上不适用于SQL语句中带有路径名或文件名的情形，也不适用于引入其他工作簿的情形。
- 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 = ActiveWorkbook.FullName '获取当前活动工作簿的完全路径
- On Error Resume Next
- For Each Sht In ActiveWorkbook.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
- End Sub
注：下面的示例中的透视表是在EXCEL2010下创建的，保存为EXCEL2003的文件格式。
 如何让所有的引入外部数据的透视表和SQL查询表自适应路径和文件名更改.rar
|