用 PowerQuery 让 Excel 自动完成数据更新

秋叶Excel
2023-11-26 11:45

大家好，我是在研究 PowerQuery 的小爽鸭~

当我们使用 PowerQuery 获取外部的 Excel 文件时，传递进去的是绝对路径。

如下图，我们导入 Excel 文件之后，进入 PQ 编辑器中，可以看到外部的 Excel 文件是个绝对路径。

一旦我们导入的文件的位置发生改变，打开刷新后，会有弹窗错误提示。

虽然单击编辑错误栏中的【编辑设置】，单击【浏览】按钮，重新选定指定路径，就可以更新好路径。

但是，如果导入外部文件的工作表太多，就要一个个更改路径，非常麻烦。

▲ 查询的表，需要一个个更改文件的路径

头大~

今天，小爽就带大家，拓展一下思路，动态获取工作簿路径~

1、路径参数化

既然要重复更改路径的话，一般就会有一种思路，将路径参数化。

具体操作 :

❶ 新建一个路径参数

在【主页】选项卡下，选择【管理参数】，【新建参数】。

① 名称：路径。
② 类型：文本。
③ 建议的值：值列表（如果常用的路径有多个，就可以采用值列表类型。只有一个就采用文本类型）。
④ 输入值列表信息，默认值，当前值。
⑤ 单击【确定】按钮，此时路径参数就新建好啦！

新建好参数之后，我们就需要把参数引用在数据源中。

❷ 路径字符串改成参数

这里可以用两种方式。

一种是更改数据源。

在【主页】选项卡下，单击【数据源设置】;

在数据源设置中，

① 单击【更改源】。
② 参数名称为：路径。
③ 类型：参数。
④ 单击【确定】按钮，关闭。

另一种方法是直接更改 M 函数公式。

将使用到文件路径的查询，分别改成参数名称（路径）即可。

上面我们已经把路径参数化了。后面一旦外部文件位置发生改变时，我们只需要更改路径参数即可，并不需要一个个去更改查询的路径。

到这里，路径参数就搞定啦~

不过，前面更改路径的参数值是针对有 PQ 基础的小伙伴，那么有没有办法，可以让我们自行选择指定路径，将路径的信息传递给参数呢？

要想实现这个效果，我们得用上 VBA。

2、交互式获取路径

首先我们先新建一个参数看看，里面的 M 函数是怎么写的。

由于这里是让用户自行选择路径，所以参数的类型选文本就可以。

接着，来看一下该参数对应的 M 函数是怎么写的。

选择查询【路径】，单击【高级编辑器】。

从图中可以看到，参数对应 M 语言如下 :

"C:\Users\80522\Desktop\小爽鸭xlsx" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

通过观察，我们可以构造出如下形式。

路径  + "meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]"

那么下面，就是利用 VBA 执行新建路径参数的操作。

主要有如下思路 :

通过 FileDialog 对象，让用户选择指定的文件，将获取到的路径字符串，进行构造路径参数的 M 函数公式，利用 VBA 添加 PQ 查询（Add 方法），让代码执行在 PQ 编辑器中新建以 M 函数公式的路径参数的查询。如果路径参数查询已经存在则先删除。

新建一个模块，输入我按照思路编写的 VBA 代码。

Sub 选择参数()    Dim dig, path        Set dig = Application.FileDialog(msoFileDialogFilePicker)            With dig                        .Filters.Add "Excel 文件", "*.xls*", 1                              .InitialFileName = ThisWorkbook.FullName                              If .Show <> 0 Then                                  path = dig.SelectedItems(1)                                        On Error Resume Next                                        ThisWorkbook.Queries("路径").Delete                                        ThisWorkbook.Queries.Add Name:="路径", Formula:= _                                           """" & path & """" & " meta [IsParameterQuery=true, Type=""Text"", IsParameterQueryRequired=true]"                                              ThisWorkbook.Queries("路径").Refresh                                          End If                                 End With                  End Sub

最后，我们插入一个图片，指定一下上述宏代码。

单击【图片】，鼠标右键，选择【指定宏】，位置【当前工作簿】，【选择参数】，单击【确定】按钮。