none
VBA外部資料連結錯誤問題 RRS feed

  • 問題

  • 各位大大好

    小弟遇到一個無法解決的問題,主要狀態如下文長再請大神耐心解答

    資料以excel為主體

    簡單說

    表格手動更新時正常無任何問題,以VBA操作時出現錯誤碼1004,在本機無法執行,換電腦就正常。
    ==================================================================

    作業系統 : Win 10 X3

    office: 2019 X2 及2016 X1

    測試電腦共三台僅win10+office2019 一台可正常使用其餘2台顯示錯誤訊息

    使用VBA腳本更新power query 表格資料

    錯誤敘述:Range("XXXX").ListObject.QueryTable.Refresh BackgroundQuery:=False

    ==========================================================================

    詳細狀況如下

    資料內容以"表格"格式,並以power query構成,環境在同一資料夾中A、B檔內容更新至C檔 ( A、B、C皆為.xlsx檔)

    以手動按"重新整理"連結外部資料(A、B檔)時正常可更新

    以VBA內碼如下

    Range("XXXX").ListObject.QueryTable.Refresh BackgroundQuery:=False

    顯示錯誤碼1004

    資料來源的初始設定失敗

    檢查資料庫伺服器或聯絡您的資料庫系統管理員,然後再次嘗試.......云云略

    修改語法

    Range("XXXX[[#Headers],[A]]").Select

    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    亦同上錯誤

    如此時中斷VBA,以手動更新C檔後存檔關閉,再以手動執行VBA則無錯誤訊息且可正常更新

    (VBA內容於D檔)

    但將VBA檔關閉後,重啟VBA ( Private Sub Workbook_Open() ) 時,相同故障碼出現

    此時如無將C檔手動更新並直接關閉後,以手動執行VBA則亦同,顯示相同錯誤訊息無法正常更新

    如將A、B、C、D檔複製至另一台電腦時可正常執行並無問題,複製回原電腦後一樣問題持續,隔日另台電腦淪陷

    此系統原正常執行一年以上但至9/1起突然顯示出以上問題

    原電腦已重灌office 2019狀況依舊。

    小弟直覺是不是VBA關掉了什麼而造成此狀況。

    請求好心大神給予解答

    ========================================

    目前NG語法(其他主機OK,於本機NG)

    1.

    Range("XXXX").ListObject.QueryTable.Refresh BackgroundQuery:=False

    2.

    Range("XXXX[[#Headers],[A]]").Select

    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    3.

       

    Dim tbl As ListObject

        Set tbl = ActiveWorkbook.ActiveSheet.ListObjects("XXXX")

        tbl.QueryTable.Refresh BackgroundQuery:=False

    ============================================


    9/12 補充: 

    錯誤關鍵資訊如下:

    資料來源的初始設定失敗
    檢查資料庫伺服器或聯絡您的資料庫系統管理員

    問題點為連線資料無法自動連結,故製作一簡易外部連結資料A+及C+檔,以C+檔以power query建立A+資料連線後,於C+檔進入"資料"=>"重新整理"下拉=>"連線內容"=>"定義"=>"匯出連線檔案"=>於我的資料來源建立"查詢檔案.odc"=>之後勾選永遠使用連線檔案=>"確定"

    建立一個永久默認查詢後,再於原VBA腳本中建立C+檔更新腳本後,並於Application.Run 順序調整為C+檔優先執行後再執行C檔更新即可解決"1004資料來源的初始設定失敗"問題。

    此方式為取巧方式,若不優先默認一組資料更新,錯誤訊息依舊存在。於正常電腦不須此步驟可直接正常使用Range("XXXX").ListObject.QueryTable.Refresh BackgroundQuery:=False 

    只希望有大神能給予正解以解決此問題



    • 已編輯 hugh-T 2020年9月11日 下午 04:50
    2020年9月7日 下午 03:43