- 1 -
Excel有密码，PQ没办法
一直以来，我们在用Power Query接入各种各样的数据的时候，如果这个数据源需要用户名密码，比如各种数据库，Power Query通常都直接提供输入用户名、密码的对话框，这样就可以在后续刷新数据的时候完整自动验证的过程。
然鹅，偏偏回到Excel本身，如果工作簿设置了密码，Power Query却没有提供输入密码自动刷新的功能，结果，碰到有密码的Excel工作簿时，刷新就会出错，还提示为“文件包含损坏的数据”……如下图所示：
那如果希望能一气呵成地自动刷新，难道就没有办法了吗？
当然不会！
这个时候，不要忘了还有咱们的老朋友VBA嘛！通过Power Query与VBA的强强联合，咱们就可以刷新对带密码Excel工作簿数据的自动刷新。
- 2 -
Excel和PQ中的处理
为方便实现动态路径，我们先建立一个路径表，类似于我在以前文章（视频）《批量汇总Excel数据的建议解法-1_同一工作簿内多表》里的做法，这里直接在Excel里处理好数据源的引用路径，既方便Power Query的引用，也方便在VBA里引用：
同时将这个表接入到Power Query里：
这样，就可以在获取数据源时直接引用已经处理好的路径：
重要技巧：这里如果直接引用带密码的Excel工作簿，后面的数据处理过程将无法进行，所以，应该先把要加密的Excel工作簿解密，处理好后再重新加密。或者，先复制一份，去掉密码，做好后续处理再修改为对加密Excel工作簿的引用路径。
- 3 -
VBA实现解密刷新
通过VBA，我们可以用密码打开数据源工作簿，清除密码，然后刷新查询，刷新完毕后再对数据源工作簿重新加密……是不是很像“把大象放进冰箱里”的三个步骤？
具体过程如下图所示：
具体代码如下，供大家按需参考改用：
Sub RefreshQuery()
Dim path As String Dim wb As Workbook path = ThisWorkbook.Sheets("路径").Range("B2").Value Application.ScreenUpdating = False
'1、打开工作簿，清除密码并保存关闭 Set wb = Workbooks.Open(path, Password:="123456") wb.Password = "" wb.Save wb.Close
'2、刷新数据 ThisWorkbook.Connections("查询 - 表2").Refresh
'3、重新打开工作簿，设置密码并保存关闭 Set wb = Workbooks.Open(path) wb.Password = "123456" wb.Save wb.Close Application.ScreenUpdating = True
End Sub 编辑好代码后，我们还可以在返回数据的Excel界面里，插入数据刷新按钮：
使刷新按钮指向前面编辑的宏：
最后，我们再设置Power Query查询的属性为【不】“允许后台刷新”，避免在数据未完成刷新时，刷新代码后面的步骤（重新加密）被执行而导致刷新失败：
这样，我们就又可以一键刷新了：
注意，这里数据刷新后，查询上仍然会提示如文章开头所说的错误信息，但这并不影响数据的更新和使用。
- 4 -
PBI怎么办？
上面通过与VBA的强强联合，我们实现对Excel的Power Query引用加密Excel数据的自动刷新，但是，Power BI里没有VBA哦，怎么办？
研究工作已完成，敬请期待……
【近期热门文章】
联系客服
