2019年11月29日 星期五

EXCEL VBA網路爬取大樂透歷史資料

EXCEL VBA網路爬取大樂透歷史資料

如果想將網路上總共有57頁網頁資料複製到EXCEL工作表中，
如果用土法煉鋼是非常花時間的事，如果每天需要經常性抓取網路資料，
真的非常必要用VBA程式來完成，
其實就是目前很夯的網路爬蟲，
和 Python 程式相比，VBA相對比較親民一些，
直接抓取到EXCEL相對的好處理後續的動作，
利用VBA程式，配合 For迴圈+If邏輯判斷一下，
基本上程式就可以快速一鍵完成資料下載。
接下來要產生報表作統計分析，或是視覺化圖表就更簡單了。
以下有六個步驟如下，
1.錄製巨集與修改VBA程式
2.VBA寫刪除日期列
3.VBA寫複製日期
4.VBA寫刪除列
5.VBA寫增加中獎號碼

6.VBA寫剖析資料

完成畫面：


 統計結果：

大樂特歷史資料下載程式碼


**利用資料的從WEB功能下載資料，再利用錄製巨集產生基本VBA程式加以修改

1.資料的從WEB功能

程式碼：

Sub 大樂透下載()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.lotto-8.com/listltobigbbk.asp?indexpage=1&orderby=new", _
        Destination:=Range("$A$1"))
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub 批次大樂透下載()

    For i = 1 To 57
        '1.匯入WEB資料
        If Range("A1") = "" Then
            r = 1
        Else
            r = Range("A1").End(xlDown).Row + 1
        End If
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;https://www.lotto-8.com/listltobigbbk.asp?indexpage=" & i & "&orderby=new" _
            , Destination:=Range("$A$" & r))
            .WebFormatting = xlWebFormattingNone
            .WebTables = "5"
            .Refresh BackgroundQuery:=False
        End With
    Next
End Sub

VBA程式碼

Sub 刪除日期列()

    For i = Range("A1").End(xlDown).Row To 2 Step -1
        If Cells(i, "A") = "日期" Then
            Rows(i).Delete
        End If
    Next
End Sub

Sub 複製日期()

    For i = 2 To Range("A1").End(xlDown).Row
        If (i - 2) Mod 3 = 0 Then
            Cells(i, "A") = Cells(i + 1, "A")
            Cells(i, "A").NumberFormatLocal = "yyyy/m/d"
        End If
    Next
End Sub

Sub 刪除列()

    For i = Range("A1").End(xlDown).Row To 4 Step -3
        Rows(i).Delete
        Rows(i - 1).Delete
    Next
End Sub

Sub 增加中獎號碼()

    For j = 1 To 6
        Cells(1, j + 3) = j
    Next
End Sub

Sub 剖析資料()

    For i = 2 To Range("A1").End(xlDown).Row
        Range("D" & i & ":I" & i) = VBA.Split(Cells(i, "B"), ",")
    Next
End Sub

Sub 刪除B欄()

    Columns("B").Delete
    Columns.AutoFit
End Sub

Sub 大樂透下載()

    Call 批次大樂透下載
    Call 刪除日期列
    Call 複製日期
    Call 刪除列
    Call 增加中獎號碼
    Call 剖析資料
    Call 刪除B欄
End Sub

教學影音(完整版在論壇)：

教學影音完整版在論壇：
https://groups.google.com/forum/#!forum/scu_excel_vba2_86

EXCEL VBA進階班的課程規劃

主要是延伸入門課，延伸資料庫、多工作表、工作簿、網路爬蟲、視覺化報表等應用並與Python程式協同應用
單元01_資料拆解相關(VBA)
單元02_輸入自動化與表單設計
單元03_用ADO匯入與匯出資料庫
單元04_大量工作表合併與分割
單元05_資料查詢(篩選與分割工作表)
單元06_下載網路資料(YAHOO股市)
單元07_活頁簿與檔案處理(工作表分割與合併活頁簿)
單元08_視覺化報表與快速匯入圖片

其他相關學習：
    函數東吳進修推廣部, EXCEL, EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel  excel vba教學視頻 函數教學 excel函數
    沒有留言:

    2019年11月22日 星期五

    用EXCEL VBA快速將大量的EXCEL檔轉為PDF檔

    用EXCEL VBA快速將大量的EXCEL檔轉為PDF檔

    這是EXCEL VBA進階課程的單元七的大量工作表的分割時講到，
    剛好有學員問道如果想把大量的工作表，直接分割為PDF檔，
    該怎麼做？其實只要一個For迴圈配合ExportAsFixedFormat方法，
    就可以快速的將大量的工作表輸出為一個一個的PDF檔，
    省去人工所要花費的時間，如果你每天都需要將工作表轉為PDF，
    這個分享，應該可以幫到您很多的忙，節省大量時間。

    其他可參考
    如何用EXCEL VBA批次轉PDF檔  [連結]
    [問題詢問]如何修改VBA讓EXCEL自動轉存成PDF時不會出現副檔名 [連結]
    先學會如何用EXCEL VBA批次另存成2003與CSV等格式 [連結]
    與如何用EXCEL VBA自動批次匯入圖片與超連結 [連結]
    與如何增加EXCEL VBA按鈕 [連結]

    預覽：


    大數時代來臨如果不懂得裡用自動化方式處理資料，
    處理資料將非常沒有效率，
    至於處理大數據的方案很多，
    但最通行也沒有額外費用的大概只剩VBA了。
    而且開啟EXCEL就包含VBA，除了在Windows外，
    MAC蘋果電腦一樣也有EXCEL，
    VBA也可以沿用以前VB的資源。

    程式碼：
    Public Sub 批次將工作表轉存為工作簿_PDF()
        If Dir("C:\Users\SCU\Desktop\VBA\*.xlsx") <> "" Then Call 刪除檔案
        
        Application.FileDialog(msoFileDialogFolderPicker).Show
        sPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

        Application.ScreenUpdating = False
        For i = 2 To 14
            x = Sheets("甲班").Cells(i, "I")
            Sheets(x).Copy
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & "\" & x & ".pdf"
            ActiveWorkbook.Close False
        Next
        Sheets("甲班").Select
        Application.ScreenUpdating = True
    End Sub


    教學影音(完整版在論壇)：

    教學影音完整版在論壇：
    https://groups.google.com/forum/#!forum/scu_excel_vba2_86

    EXCEL VBA進階班的課程規劃

    主要是延伸入門課，延伸資料庫、多工作表、工作簿、網路爬蟲、視覺化報表等應用並與Python程式協同應用
    單元01_資料拆解相關(VBA)
    單元02_輸入自動化與表單設計
    單元03_用ADO匯入與匯出資料庫
    單元04_大量工作表合併與分割
    單元05_資料查詢(篩選與分割工作表)
    單元06_下載網路資料(YAHOO股市)
    單元07_活頁簿與檔案處理(工作表分割與合併活頁簿)
    單元08_視覺化報表與快速匯入圖片

    其他相關學習：
      函數東吳進修推廣部, EXCEL, EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel  excel vba教學視頻 函數教學 excel函數
      沒有留言:
      訂閱： 文章 (Atom)