要想實現(xiàn)這個效果，我們得用上 VBA。

2、交互式獲取路徑

首先我們先新建一個參數(shù)看看，里面的 M 函數(shù)是怎么寫的。

由于這里是讓用戶自行選擇路徑，所以參數(shù)的類型選文本就可以。

接著，來看一下該參數(shù)對應(yīng)的 M 函數(shù)是怎么寫的。

選擇查詢 【路徑】，單擊【高級編輯器】。

從圖中可以看到，參數(shù)對應(yīng) M 語言如下 :

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

通過觀察，我們可以構(gòu)造出如下形式。

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

那么下面，就是利用 VBA 執(zhí)行新建路徑參數(shù)的操作。

主要有如下思路 :

通過 FileDialog 對象，讓用戶選擇指定的文件，將獲取到的路徑字符串，進行構(gòu)造路徑參數(shù)的 M 函數(shù)公式，利用 VBA 添加 PQ 查詢（Add 方法），讓代碼執(zhí)行 在 PQ 編輯器中新建 以 M 函數(shù)公式的路徑參數(shù)的查詢。如果路徑參數(shù)查詢已經(jīng)存在則先刪除。

新建一個模塊，輸入我按照思路編寫的 VBA 代碼。

Sub 選擇參數(shù)()
    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

最后，我們插入一個圖片，指定一下上述宏代碼。

單擊【圖片】，鼠標(biāo)右鍵，選擇【指定宏】，位置【當(dāng)前工作簿】，【選擇參數(shù)】，單擊【確定】按鈕。

3、效果展示

現(xiàn)在，我們來做一個測試，案例中我已經(jīng)事先導(dǎo)入測試文件夾中小爽鴨的 Excel 文件，文件路徑都引用了路徑參數(shù)（此時數(shù)據(jù)源路徑是存在的）。

然后，我把「測試」文件夾中的「小爽鴨」文件移到「路徑變了」文件夾中。小爽鴨工作簿位置發(fā)生改變。

這時，更新一下數(shù)據(jù)源后，我們可以看到，錯誤信息中顯示【未找到文件】。

接著，我們單擊一下圖片，執(zhí)行宏操作，選擇移動后的文件，確定。

這時候，就看到查詢中的鏈接沒有提示錯誤了。同時，路徑的參數(shù)也改成我們剛剛所選擇的文件路徑。

到這里，大家 get 到了嘛?

本文可能有點難度，不過比較實用。目前還沒完全懂的小伙伴可以先收藏。

4、寫在最后

因為我們知道 PowerQuery 引用外部文件，是個絕對路徑，不是相對路徑。

所以我們就想到把路徑作為一個參數(shù)，引用到查詢中，于是就有了新建參數(shù)的想法。

但是，還是不方便。這時我們就開始思考：對于不會使用 PowerQuery 更改路徑的小伙伴，能不能通過外部自己選擇路徑呢? 于是就有了 VBA 代碼的想法。

因為使用到 VBA，所以文件后綴名，小伙伴們記得保存為 xlsm 格式！

這就是本篇文章的整體思路。

對了，本文使用版本是 Office 365 最新版本，不同版本的提示可能有些不一樣，大家也注意一下哦。

最新評論