工作簿有密码，自动刷新数据，没问题！ | PQ重要技巧
工作簿有密码，自动刷新数据，没问题！ | PQ重要技巧
- 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哦，怎么办？
本文分享自 Excel到PowerBI 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与?腾讯云自媒体同步曝光计划? ，欢迎热爱写作的你一起参与！