[VBA]在导入外部数据创建数据透视表中自适应路径及文件名的变化

jssy

在导入外部数据创建数据透视表后,如果文件被移动后或改名后会出现如下警告:


使用以下代码可以解决这一问题:(该代码同样适用于2007版数据透视表)

1. Private Sub Workbook_Open()
   
2.     Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String
   
3.     '定义变量
   
4.     On Error Resume Next
   
5.     strCon = ActiveSheet.PivotTables(1).PivotCache.Connection
   
6.     '将当前活动数据透视表中缓存连接信息赋值给变量strCon
   
7.     Select Case Left(strCon, 5)  'select case语句,条件为strCon变量中从左侧取5个字符
   
8.     Case "ODBC;"                 '用于判断缓存连接信息中的数据连接方式,如果是ODBC方式
   
9.         iFlag = "DBQ="           '将"DBQ=" 赋值给变量iFlag
   
10.     Case "OLEDB"                 '用于判断缓存连接信息中的数据连接方式,如果是OLEDB方式
    
11.     iFlag = "Source="            '将"DBQ=" 赋值给变量iFlag
    
12.     Case Else
    
13.         Exit Sub
    
14.     End Select
    
15.     iStr = Split(Split(strCon, iFlag)(1), ";")(0)  '在变量strCon中截取文件路径信息
    
16.     iPath = ActiveWorkbook.FullName                '获取当前活动工作簿的完全路径
    
17.     With ActiveSheet.PivotTables(1).PivotCache     '替换数据透视表中缓存信息中的文件完全路径
    
18.         .Connection = VBA.Replace(strCon, iStr, iPath)
    
19.         .CommandText = VBA.Replace(.CommandText, iStr, iPath)
    
20.     End With
    
21. End Sub
    

复制代码

自适应路径及文件名的变化.rar (18.64 KB, 下载次数: 1322)

2009-8-18 14:48 上传

点击文件名下载附件

[ 本帖最后由 jssy 于 2009-9-4 16:30 编辑 ]

小蚁雄兵

谢谢版主，学习了

bolliy

谢谢版主分享，收藏学习。

jiahaocn

谢谢,很实用的,解决了很大问题

liechun1983

谢谢！！！！！！！！

开心萝卜惠州

谢谢分享，收藏慢慢学习

penghaifeng

学习中！~~~

dgxsdr

下载学习中。。。

ddx001

JSSY 大哥，有个问题呀！假如最后退出保存EXCEL时不是那个有外部数据的透视表页面的话，移动位置后再打开此表，那个路径就不会更改了呀。

假如只有这一个透视表的话，每次保存时有意识的切换到此表页面后再退出还能小小的解决一下麻烦。但万一我表中集合了好多透视表，有的有外部数据（可能还有不同路径的），有的没有外部数据，这样还能搞定吗？

[ 本帖最后由 ddx001 于 2009-10-30 11:18 编辑 ]

ddx001

比如我1个excel中有三张表：sheet1、sheet2、sheet3，其中sheet2中有使用外部数据的透视表，代码应该怎么写才能实现以下功能？

    当关闭excel时，若不处于sheet2页面则跳出提示：“请点击工作表sheet2,再关闭”。只有最后处于sheet2页面时，才能正常关闭。