13.3　数据源移动后如何修改“Microsoft Query”查询中的连接

由于运用“Microsoft Query”数据查询创建的数据透视表需要指定数据源工作簿所在位置，一旦数据源表的位置发生改变就需要修改“Microsoft Query”数据查询中的连接路径，否则无法刷新数据透视表。手工修改数据源移动后“Microsoft Query”查询中的连接路径非常繁琐，无异于重新创建数据透视表，这里不作讲解，只介绍高效的VBA代码解决方法。

示例13.4　修改数据源移动后的“Microsoft Query”查询连接

如果希望对图13-36所示的数据透视表利用VBA代码自动修改“Microsoft Query”数据查询所连接工作簿的变更路径，请参照以下方法。

假设所有数据源工作簿和生成了数据透视表的工作簿都保存在D盘根目录下的“VBA代码修改查询路径”文件夹中。

步　骤1

打开“Microsoft Query查询汇总.xlsx”工作簿，在“汇总”工作表标签上单击鼠标右键，在弹出的快捷菜单中单击“查看代码”命令，打开VBA代码窗口，如图13-37所示。

图13-37　打开VBA代码窗口

步　骤2

双击“Thisworkbook”，在代码窗口中输入VBA代码，如图13-38所示。

步　骤3

按<Alt+F11>组合键切换到工作簿窗口，将当前工作表另存为“Excel启用宏的工作簿”，如图13-39所示。

图13-38　输入VBA代码

图13-39　另存为“Excel启用宏的工作簿”

现在，如果将“VBA代码修改查询路径”文件夹剪切到磁盘中的任何位置，打开“Microsoft Query查询汇总.XLSM”文件都可以自动识别“Microsoft Query”查询中所连接的工作簿路径，数据透视表可以正常刷新。

注意

使用此VBA代码自动修改“Microsoft Query”查询中数据源的连接路径，要求移动后的数据源和数据透视表仍在同一个文件夹中，否则不能自动识别数据源位置改变后的查询路径。