如何在Microsoft Excel‧動態更新Power Query連接來源檔案

01. 今天同事提到一個問題，她有一個Excel檔案，用Power Query連接到SharePoint上的Access數據庫。而此Excel檔案，同事由2個同事共同維護。

02. 最基本的做法，是在Power Query中，用『Web.Contents()』連接到SharePoint的檔案，以便2個同事打開Excel檔案時，能更新最新內容。

03. 同事反映，如果用『Web.Contents』連接到SharePoint檔案方法，更新需要10分鐘以上。檢查一下問題，那個放在SharePoint上的Access數據庫檔案，容量接近1G。同時，那個Excel檔案，Power Query可不止連上一個Access數據庫檔案，而是4個。根據公司網速只有50Gbps，似乎更新慢是正常的。

04. 現時同事做法，是將SharePoint上的Access數據庫檔案，用SharePoint同步(sync)到本機電腦。而在Excel檔案Power Query中，改用『File.Contents()』連接到本機的Access數據庫檔案。

05. 更新速度不錯是快了，但另一個問題又出現了，因為Excel檔案是2個同事共用。而SharePoint同步(sync)到本機電腦，會儲存在『c:\Users\用戶名稱』資料夾內。即是第1個同事在Excel檔案Power Query中，更改了『File.Contents()』連接上Access數據庫檔案位置，第2個同事因為用戶名稱不同，所以要再次改動『File.Contents()』位置，才能在Excel檔案中成功更新。

06. 既然Access數據庫檔案放在SharePoint上，更新速度慢是解決不了，解決辦法似乎只有如何處理2個同事之間，令Power Query可以根據2位同事用戶名稱自動作出更改。

07. 首先建立一個Power Query Excel(帶巨集)檔案(powerquery.xlsm)。

08. 同時，建立2個資料夾(Chocolate及KatKat)，放置各一個Excel檔案(workbook.xlsx)，當作是模擬Access數據庫檔案情況。每個『workbook.xlsx』檔案，放置一堆數據，以便之後能檢查在Power Query更新後結果。

09. 打開『powerquery.xlsm』檔案。首先要令其可以自動取得Windows的用戶名稱，即今次測試用到的『c:\Temp\用戶名稱』位置。而在網上，找到可以用VBA方法取得相關資料。

10. 在『powerquery.xlsm』檔案中，建立一個新『file_selection』工作表(worksheet)。按『ALT + F11』打開VBA編輯器，選擇『ThisWorkbook』位置，加入以下程式。這程式主要是是在每次打開『powerquery.xlsm』檔案時，取得Windows登入用戶名稱、網域名稱及Excel用戶名稱，並儲存在『file_selection』工作表。而今次最主要用到的，就是Windows登入用戶名稱。

Private Sub Workbook_Open()
Dim UserFullName As String
Dim WSHnet As Object
Dim objUser As Object
Dim UserName As String
Dim UserDomain As String
Set WSHnet = CreateObject("WScript.Network")
UserName = WSHnet.UserName
UserDomain = WSHnet.UserDomain
Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
UserFullName = objUser.FullName
Sheets("file_selection").Cells(1, 1) = UserName
Sheets("file_selection").Cells(2, 1) = UserDomain
Sheets("file_selection").Cells(3, 1) = UserFullName
End Sub

11. 而在『file_selection』工作表另一儲存格(cells)，建立動態內容及其標題(file_path)，以對應根據用戶名稱變動的資料夾名稱。

12. 選取擇動態內容及其標題，在上面選單選擇『插入(Insert)』、『表格(Tables)』。

13. 建立一新表格，並勾選『我的表格有標題(My table has headers)』。

14. 將新表格命名一個名稱(file)。

15. 繼續選取擇動態內容及其標題，在上面選單選擇『資料(Data)』、『從表格/範圍(From Tables/Ranges)』。

16. 之後會自動跳入『Power Query編輯器』，並將之前選取擇動態內容及其標題，加到Power Query裡面。

17. 選擇『file_path』數值，按滑鼠右鍵，選擇『向下切入(Drill Down』。

18. 完成後，便會先到『file_path』數值，已展開及可檢視其內部細節。

19. 在『Power Query編輯器』左邊欄位，按滑鼠右鍵，選擇『新增查詢(New Query)』、『檔案(Files)』、『Excel 活頁簿(Excel Workbook)』。

20. 選擇『Chocolate』活頁簿。

21. 選擇工作表。

22. 完成後，便會看到『Chocolate』活頁簿中，『Sheet1』工作表已加入。按左上角『關閉並載入』按鈕，退出『Power Query編輯器』。

23. 返回『powerquery.xlsm』檔案，會見到『Chocolate』活頁簿中，『Sheet1』工作表內容，亦加到『powerquery.xlsm』檔案『Sheet1』裡面。

24. 再次打開『Power Query編輯器』。選擇『Sheet1』查詢。在右邊選擇『來源(Sources)』，按上面『進階編輯器』按鈕。

25. 進入進階編輯器畫面。(註：檔案名稱及路徑，以及名稱，需按上面設定而作出更改。如『file』名稱，即上面第5點設定名稱。如『C:\temp\Chocolate\workbook.xlsx』，此為『workbook.xlsx』檔案，實際儲存位置。而『path』名稱，將『file』數值放到下一步的『來源(Sources)』裡面，所以可按自己設定不同名稱。)

將：
來源 = Excel.Workbook(File.Contents("C:\temp\Chocolate\workbook.xlsx"), null, true),
更改為：
path = file,
來源 = Excel.Workbook(File.Contents(path), null, true),

26. 更改後，會發現更新時出現『Formula.Firewall』錯誤。這原因是因為Excel原設定的安全基制。

27. 按『ALT + F』打開檔案選單，選擇『選項及設定(Options and settings』、『查詢選項(Query Options)』。

28. 在左邊『目前活頁簿(Current File section)』位置，『隱私權(Privacy)』，將『隱私權等級(Privacy level)』，更改為『忽略隱私權等級可能會改善效能(Ignore the privacy levels and potentially improve performance)』。