|
- Private Sub Workbook_Open()
- Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String
- Dim oSht As Worksheet
- Dim oPvTb As PivotTable
- Dim oQyTb As QueryTable
- For Each oSht In ThisWorkbook.Worksheets
- If oSht.PivotTables.Count > 0 Then
- For Each oPvTb In oSht.PivotTables
- If oPvTb.PivotCache.SourceType = xlExternal Then
- strCon = oPvTb.PivotCache.Connection
- Select Case Left(strCon, 5)
- Case "ODBC;"
- iFlag = "DBQ="
- Case "OLEDB"
- iFlag = "Source="
- Case Else
- End Select
- iStr = Split(Split(strCon, iFlag)(1), ";")(0)
- iPath = Left(iStr, InStrRev(iStr, "") - 1)
- With oPvTb.PivotCache
- .Connection = VBA.Replace(strCon, iPath, ThisWorkbook.Path)
- .CommandText = VBA.Replace(.CommandText, iPath, ThisWorkbook.Path)
- End With
- End If
- Next
- End If
- If oSht.QueryTables.Count > 0 Then
- For Each oQyTb In oSht.QueryTables
- If oQyTb.QueryType <> xlWebQuery Then
- strCon = oQyTb.Connection
- Select Case Left(strCon, 5)
- Case "ODBC;"
- iFlag = "DBQ="
- Case "OLEDB"
- iFlag = "Source="
- Case Else
- End Select
- iStr = Split(Split(strCon, iFlag)(1), ";")(0)
- iPath = Left(iStr, InStrRev(iStr, "") - 1)
- With oQyTb
- .Connection = VBA.Replace(strCon, iPath, ThisWorkbook.Path)
- .CommandText = VBA.Replace(.CommandText, iPath, ThisWorkbook.Path)
- End With
- End If
- Next
- End If
- Next
- End Sub
复制代码 |
|