BI神器Power Query（20）-- PQ自适应目录

使用Power Query可以轻松导入文本文件、XML、CSV文件等多种数据文件，但是如果保存文件的目录发生变化（例如数据文件和Excel模板发送给客户），刷新查询时会出现类似于下图所示的DataSource.Error错误提示。

此时需要打开【Power Query编辑器】，在【高级编辑器】对话框中更新文件目录和名称，但是PQ公式语法要求很严格，稍有不慎可能就会出现其他错误。

如果数据文件和Excel文件保存在同一目录中，使用VBA代码在实现自动更新，下述代码写入Excel文件的ThisWorkbook模块中。

Private Sub Workbook_Open()
     Dim strCurPath As String
     Dim strPath As String
     Dim strFormula As String
     Dim arrPath As Variant
     Dim objQuery As Object
     Dim objRegEx As Object
     Dim objMatch As Object
     strCurPath = ThisWorkbook.Path
     Set objQuery = ThisWorkbook.Queries(1)
     strFormula = objQuery.Formula
     Set objRegEx = CreateObject("vbscript.regexp")
     objRegEx.Pattern = "Contents\(""([\S\s]:.+?)(?=\""\))"
     objRegEx.Global = True
     Set objMatch = objRegEx.Execute(strFormula)
     If objMatch.Count > 0 Then
         strPath = objMatch(0).submatches(0)
         arrPath = Split(strPath, "\")
         strCurPath = ThisWorkbook.Path & "\" & arrPath(UBound(arrPath))
         If Not UCase$(strCurPath) = UCase$(strPath) Then _
            objQuery.Formula = VBA.Replace(strFormula, strPath, strCurPath)
         Debug.Print objQuery.Formula
     Else
         Debug.Print "PowerQuery公式中没有文件目录"
     End If
     Set objMatch = Nothing
     Set objRegEx = Nothing
     Set objQuery = Nothing
End Sub

【代码解析】
VBA代码为工作簿的Open事件代码，工作簿打开时将自动执行此过程。
第9行代码获取示例文件所在路径。
第10行代码获取示例文件中的查询对象。
第11行代码获取查询的公式，即Power Query公式。
第12行代码创建正则表达式对象。
第13行代码指定正则匹配模式字符串，用于提取Power Query公式中的路径和文件名。
第14行代码设置正则匹配为全局模式。
第15行代码对Power Query公式进行正则匹配。
如果匹配成功，那么第17行代码获取匹配字符组。
第18行代码将路径和文件名使用“\”作为分隔符拆分为数组。
第19行代码构建新的路径和文件名，其中arrPath(UBound(arrPath))为CSV文件名称。
第20行判断新路径与原路径是否一致，如果发生了变化，则更新Power Query公式。
第22行代码在【立即】窗口中输出更新后的Power Query公式。
如果正则表达式无法匹配Power Query公式中的字符串，那么第24行代码在【立即】窗口中输出提示信息。

有了这个代码，从此无论保存在哪个目录，PQ刷新都不是事儿！