大家好，我是在研究 PowerQuery 的小爽鴨~
當(dāng)我們使用 PowerQuery 獲取外部的 Excel 文件時(shí)，傳遞進(jìn)去的是絕對(duì)路徑。
如下圖，我們導(dǎo)入 Excel 文件之后，進(jìn)入 PQ 編輯器中，可以看到外部的 Excel 文件是個(gè)絕對(duì)路徑。
一旦我們導(dǎo)入的文件的位置發(fā)生改變，打開刷新后，會(huì)有彈窗錯(cuò)誤提示。
雖然單擊編輯錯(cuò)誤欄中的 【編輯設(shè)置】，單擊 【瀏覽】按鈕，重新選定指定路徑，就可以更新好路徑。
但是，如果導(dǎo)入外部文件的工作表太多，就要一個(gè)個(gè)更改路徑，非常麻煩。
頭大~
今天，小爽就帶大家，拓展一下思路 ，動(dòng)態(tài)獲取工作簿路徑~
1、路徑參數(shù)化
既然要重復(fù)更改路徑的話，一般就會(huì)有一種思路，將路徑參數(shù)化。
具體操作 :
? 新建一個(gè)路徑參數(shù)
在【主頁(yè)】選項(xiàng)卡下，選擇【管理參數(shù)】，【新建參數(shù)】。
① 名稱：路徑。
② 類型：文本。
③ 建議的值：值列表（如果常用的路徑有多個(gè)，就可以采用值列表類型。只有一個(gè)就采用文本類型）。
④ 輸入值列表信息，默認(rèn)值，當(dāng)前值。
⑤ 單擊【確定】按鈕，此時(shí)路徑參數(shù)就新建好啦！
新建好參數(shù)之后，我們就需要把參數(shù)引用在數(shù)據(jù)源中。
? 路徑字符串改成參數(shù)
這里可以用兩種方式。
一種是更改數(shù)據(jù)源。
在【主頁(yè)】選項(xiàng)卡下，單擊【數(shù)據(jù)源設(shè)置】;
在數(shù)據(jù)源設(shè)置中，
① 單擊【更改源】。
② 參數(shù)名稱為：路徑。
③ 類型：參數(shù)。
④ 單擊【確定】按鈕，關(guān)閉。
另一種方法是直接更改 M 函數(shù)公式。
將使用到文件路徑的查詢，分別改成參數(shù)名稱（路徑）即可。
上面我們已經(jīng)把路徑參數(shù)化了。后面一旦外部文件位置發(fā)生改變時(shí)，我們只需要更改路徑參數(shù)即可，并不需要一個(gè)個(gè)去更改查詢的路徑。
到這里，路徑參數(shù)就搞定啦~
不過，前面更改路徑的參數(shù)值是針對(duì)有 PQ 基礎(chǔ)的小伙伴，那么有沒有辦法，可以讓我們自行選擇指定路徑，將路徑的信息傳遞給參數(shù)呢？
要想實(shí)現(xiàn)這個(gè)效果，我們得用上 VBA。
2、交互式獲取路徑
首先我們先新建一個(gè)參數(shù)看看，里面的 M 函數(shù)是怎么寫的。
由于這里是讓用戶自行選擇路徑，所以參數(shù)的類型選文本就可以。
接著，來看一下該參數(shù)對(duì)應(yīng)的 M 函數(shù)是怎么寫的。
選擇查詢 【路徑】，單擊【高級(jí)編輯器】。
從圖中可以看到，參數(shù)對(duì)應(yīng) M 語(yǔ)言如下 :
"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 對(duì)象，讓用戶選擇指定的文件，將獲取到的路徑字符串，進(jìn)行構(gòu)造路徑參數(shù)的 M 函數(shù)公式，利用 VBA 添加 PQ 查詢（Add 方法），讓代碼執(zhí)行 在 PQ 編輯器中新建 以 M 函數(shù)公式的路徑參數(shù)的查詢。如果路徑參數(shù)查詢已經(jīng)存在則先刪除。
新建一個(gè)模塊，輸入我按照思路編寫的 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最后，我們插入一個(gè)圖片，指定一下上述宏代碼。
單擊【圖片】，鼠標(biāo)右鍵，選擇【指定宏】，位置【當(dāng)前工作簿】，【選擇參數(shù)】，單擊【確定】按鈕。
3、效果展示
現(xiàn)在，我們來做一個(gè)測(cè)試，案例中我已經(jīng)事先導(dǎo)入測(cè)試文件夾中小爽鴨的 Excel 文件，文件路徑都引用了路徑參數(shù)（此時(shí)數(shù)據(jù)源路徑是存在的）。
然后，我把「測(cè)試」文件夾中的「小爽鴨」文件移到「路徑變了」文件夾中。小爽鴨工作簿位置發(fā)生改變。
這時(shí)，更新一下數(shù)據(jù)源后，我們可以看到，錯(cuò)誤信息中顯示【未找到文件】。
接著，我們單擊一下圖片，執(zhí)行宏操作，選擇移動(dòng)后的文件，確定。
這時(shí)候，就看到查詢中的鏈接沒有提示錯(cuò)誤了。同時(shí)，路徑的參數(shù)也改成我們剛剛所選擇的文件路徑。
到這里，大家 get 到了嘛?
本文可能有點(diǎn)難度，不過比較實(shí)用。目前還沒完全懂的小伙伴可以先收藏。
4、寫在最后
因?yàn)槲覀冎?PowerQuery 引用外部文件，是個(gè)絕對(duì)路徑，不是相對(duì)路徑。
所以我們就想到把路徑作為一個(gè)參數(shù)，引用到查詢中，于是就有了新建參數(shù)的想法。
但是，還是不方便。這時(shí)我們就開始思考：對(duì)于不會(huì)使用 PowerQuery 更改路徑的小伙伴，能不能通過外部自己選擇路徑呢? 于是就有了 VBA 代碼的想法。
因?yàn)槭褂玫?VBA，所以文件后綴名，小伙伴們記得保存為 xlsm 格式！
這就是本篇文章的整體思路。
對(duì)了，本文使用版本是 Office 365 最新版本，不同版本的提示可能有些不一樣，大家也注意一下哦。
本文來自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小爽
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。