[算表] Excel VBA巨集匯出匯入，輕鬆備份共享

看板Office作者moodyblue時間4年前 (2020/03/28 06:57)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串1/1

Excel VBA巨集也能匯出匯入，輕鬆備份共享，完整說明VBA網路爬蟲程式網誌圖文版： https://www.b88104069.com/archives/4467 Excel VBA程式都是寫在類似Word的模組白板裡，可以像上方功能區一樣匯入匯出，藉此實現備份及共享。本文同時詳述QueryTables指令，作為介紹VBA網路爬蟲實務的基礎。第一章介紹Excel專用載入網頁內容的命令，雖然很方便，但如果是需要大量持續地取得相關網頁資料、進一步整理分析的場合，顯然一次又一次的單獨操不是很有效率。第一章最後一節分享以VBA程式碼方式一鍵取得網頁資料，本章即以此為基礎，進一步說明如何適當應用Excel的VBA，取得個人所需的網頁資料。這一節首先介紹程式如何匯出匯入，因應不同需要作複製及延伸：一、在VBA編輯環境中，程式碼是存在「模組」這個地方，以上一節為例，在右邊的「 Module1」滑鼠右鍵，「匯出檔案」。二、在「匯出檔案」視窗中，輸入希望的檔案名稱，選擇一個適當的資料夾，注意到「存檔類型」是「Basic檔案(*.bas)」，表示這是VBA程式碼，最後按「存檔」。三、在新增的Excel檔案中，上方功能區移到「開發人員」頁籤，在「程式碼」中選擇「 Visual Basic」：「開啓Visual Basic編輯器」。四、「檔案」、「匯入檔案」。五、「開啓」第二個步驟所儲存的程式碼檔案。六、「專案-VBAProject」多了一個模組資料夾，裡面的「Module1」便是上一節編輯好的程式碼。七、由於這是VBA取得網頁資料的重要程式，完整解釋程式碼如下： Sub DownloadWeb() 建立一個VBA巨集程序，名稱為「DownloadWeb」。 Application.CutCopyMode = False 清空剪貼簿。 With ActiveSheet.QueryTables.Add _ With……End With是一組固定用法，方便設置同一對象的各種屬性，中間例如「.Name = "index"」表示將這對象的「Name」屬性設置為「index」，「QueryTables.Add」是VBA取得外部資料來源的命令，「ActiveSheet.QueryTables.Add」表示將取得的外部資料建立在目前工作表，「 _」空一格再緊接著下橫線是VBA慣用符號，將過長的程式碼換行。 (Connection:="URL;https://money.udn.com/money/index", _ 外部資料來源的路徑，可以是資料庫或者文字檔，這裡是引用網頁內容，所以是想要取得資料的網址，「 _」同樣是換行符號。 Destination:=Range("$A$1")) 所取得外部資料的目的地，熟悉樞紐分析表的讀者，對於這裡的路徑和目的地應該覺得很類似。 '.CommandType = 0 因為錄製巨集所產生的不必要參數，如上一章最後一節所述，前面加一個單引號「'」，已經轉換成單純文字，其實也可以直接刪除。 .Name = "index" 設置這個外部資料的名稱。 .FieldNames = True 「True」代表所取得外部份資料有標題欄。 .RowNumbers = False 是否將列號指定為新增資料表的第一欄，比較不適用於取得網頁資料庫，設置為「False 」。 .FillAdjacentFormulas = False 是否於重新整理時更新資料表右邊的公式，比較不適用於取得網頁資料庫，設置為「 False」。 .PreserveFormatting = True 是否保留格式，通常設置為「True」。 .RefreshOnFileOpen = False 開啓檔案時是否更新，「False」代表不自動更新。 .BackgroundQuery = True 是否於後台背景中執行，設置為「True」代表Excel在取得資料同時，可以進行其他操作。 .RefreshStyle = xlInsertDeleteCells 取得資料時對於原工作表的插入或刪除方式，以便寫入外部資料，通常會在空白工作表匯入，所以保留預置值即可。 .SavePassword = False 是否儲存密碼，比較不適用於取得網頁資料，通常設置為「False」。 .SaveData = True 是否儲存所取得資料，通常設置為「True」。 .AdjustColumnWidth = True 是否自動調整欄寛，通常設置為「True」。 .RefreshPeriod = 0 設定重新整理間的分鐘數，「0」代表不會自動更新。 .WebSelectionType = xlEntirePage 取得網頁內容的型態，通常設置為「xlEntirePage」，代表取得整個網頁資料。 .WebFormatting = xlWebFormattingNone 是否沿用網頁格式，通常設置為「xlWebFormattingNone」，代表只匯入資料，不匯入格式。 .WebPreFormattedTextToColumns = True 是否同時匯入網頁中HTML資料剖析欄的標籤，通常設置為「True」。 .WebConsecutiveDelimitersAsOne = True 連續分隔符號是否視為單一的分隔字元，通常設置為「True」，有操作過Excel資料剖析的讀者，應該都能理解上面這兩個參數的意義。 .WebSingleBlockTextImport = False 網頁中HTML的<PRE>標籤是否一次性匯入，通常設置為「False」。 .WebDisableDateRecognition = False 是否停用匯入資料的日期格式辯識，通常設置為「False」，表示辯識日期。 .WebDisableRedirections = False 網頁查詢時是否重新導向時是否停用，通常設置為「False」。 .Refresh BackgroundQuery:=False 與資料庫建立連線之後，送出查詢執行後是否於背景更新，比較不適用於取得網頁資料，通常設置為「False」。 End With 結束前面以「With」開始，一連串對於參數的屬性設置，經過這麼多的程式說明之後，應該能理解為何要用With……End With簡化程式碼編寫。 End Sub （「DownloadWeb」)程序結束。雖然這麼多行的程式碼，經過每一行簡短說明之後，其實還是回歸到最主要「 ActiveSheet.QueryTables.Add」，這是VBA取得外部來源資料的主要命令，也是Excel匯入網頁資料的關鍵方法。熟悉Excel樞紐分析表操作的讀者，都知道建立樞紐分析表有兩大參數，其一是資料來源範圍，其二是產生報表的位置，同樣道理套在取得網頁資料， VBA「ActiveSheet.QueryTables.Add」最主要也是兩大參數，以Excel說明手冊的術語來說，其一是「查詢表的資料來源」（Connection），其二是「位於查詢表目的範圍的左上角的儲存格」（Destination）。其他雜七雜八的屬性，如果沒有衍生問題，毋須特別花費時間研究，例如將會造成程式錯誤的「CommandType = 0」直接刪掉即可。延伸閱讀： Excel巨集抓取網頁資料出錯了！進入VBA簡單修改，開始成為程式設計師 https://www.b88104069.com/archives/4432 打開Excel開發人員面板，錄製巨集維護快速鍵，開始VBA程式抓網頁資料 https://www.b88104069.com/archives/4425 Indirect、Match、Row這三個Excel函數組合，竟然足以搜尋任何關鍵字重組報表 https://www.b88104069.com/archives/4411 -- 周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office 會計人的Excel小教室： https://www.facebook.com/acctexcel -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.22.233.185 (中國) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1585349829.A.085.html