公告版位

目前分類:02_EXCEL VBA自動化教學 (121)

瀏覽方式: 標題列表 簡短摘要

班級資料工作表的分割與合併(用EXCEL VBA)

 

最常遇到學員問到如何將工作表分割與合併為同一個工作表

若是用人工方式複製貼上,真的非常浪費時間,

工作表越多越麻煩,

其實只要一個迴圈,加上範圍複製,與資料篩選,

其實就可以在瞬間完成工作,

以下分享用VBA直接撰寫程式,將工作表分割合併的方法。

 

 

完成畫面:


分割工作表

 

程式碼

 合併工作表

 

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

 

教學影音完整版在論壇:

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函數

吳老師 發表在 痞客邦 留言(0) 人氣()

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函數

文章標籤

吳老師 發表在 痞客邦 留言(0) 人氣()

用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的資源,這樣看來VBA處理大數據應該沒有什麼對手了。

 

 

 

程式碼:

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函數

文章標籤

吳老師 發表在 痞客邦 留言(0) 人氣()

範例_字串切割與Do_While迴圈不定數量結果

 

練習檔 [下載]

這個範例是學員工作上的問題,

每天都需要將儲存格中的超連結取出到B欄中,

若儲存格中只有一個超連結還好解決,

可以用Find函數找中括弧位置,再用Mid函數切割,

剛好這個範例裡面不只一個超連結,

可能有兩個、三個甚至更多,

也就是數量不定,如果要用For迴圈,也要知道數量範圍,

所以只能用 Do While 迴圈了,

從第一個字找起,之後再從找到的位置加一再找了,該如何做。

預覽影片:

 

一、函數

=FIND(C$1,A2)

=FIND(D$1,A2)

=MID(A2,C2+1,D2-C2-1)

 

如果用VBA撰寫的程式

 

一、階段一,先撰寫只取一個超連結

外面的For迴圈是跑每一列,用 Instr函數找"【<"和">】",

分別放在將找到位置的值放在 a和b 中,

如果a或b為0,表示找不到。

 

Sub 字串切割()

    '1.迴圈範圍

    For i = 2 To Range("A2").End(xlDown).Row

        '2.取得頭尾位置與切割字串

        a = VBA.InStr(Cells(i, "A"), "【<")

        b = VBA.InStr(Cells(i, "A"), ">】")

        If a <> 0 Then

            '5.輸出結果

            Cells(i, "B") = Mid(Cells(i, "A"), a + 1, b - a)

        End If

    Next

End Sub

 

如果多個超連結,可以先多產生 a1和b1變數,預設值為 1,

即從頭找起,找到之後再把  a1和b1 加1之後繼續找,

直到找不到為止,Do While 後面就是邏輯,為 True 就繼續找,

反之就離開迴圈了。

 

Sub 字串切割_所有超連結()

    '1.迴圈範圍

    For i = 2 To Range("A2").End(xlDown).Row

        '兩個位置初始值,從1開始找

        a1 = 1

        b1= 1

        '2.取得頭尾位置與切割字串

        '當找到關鍵字就執行以下程序

        Do While InStr(a1, Cells(i, "A"), "【<") <> 0

            a = InStr(a1, Cells(i, "A"), "【<")

            b = InStr(b1, Cells(i, "A"),  ">】")

            S = S & Mid(Cells(i, "A"), a + 1, b - a) & Chr(10)

            a1 = a + 1

            b1 = b + 1

        Loop

        '輸出到B欄

        Cells(i, "B") = S

        '清空變數資料

        S = ""

    Next

End Sub

 

以下是清除資料的程式碼

 

Public Sub 清除()

    Range("B2:B" & Range("B2").End(xlDown).Row).ClearContents

End Sub

 

以上範例主要學會如何用 VBA的 Instr與Mid函數取出要的資料,

如果範圍不定,一定要懂得使用 Do While迴圈了。

 

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

 

教學影音完整版在論壇:

https://groups.google.com/forum/#!forum/scu_excel_vba2_107

 

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函數

文章標籤

吳老師 發表在 痞客邦 留言(0) 人氣()

直接用VBA取得儲存格中的數字或英文資料

 

之前常有同學問到,若是儲存格中,中、英、數字資料夾雜,

若只想留下數字部分,該怎麼做,有沒有函數可以解決,

不懂VBA,這個問題將非常麻煩,資料少還可以土法煉鋼,

慢慢複製貼上,但若資料非常巨量,將是巨大麻煩,

本文分享如何簡單解決這個問題,

雖然用MID、CODE、ROW、IFFERROR、SUM加上陣列計算數字個數,

已經夠複雜的了,若要再取出數字部分,那就更加困難,

建議,這麼複雜的事情,還是改為VBA來做會比較適合,

也許直接跳過 EXCEL裡的函數,

直接寫 VBA 回更簡單,以下簡單說明如下。


預覽畫面:

 

VBA部分:

 

階段一:

Sub 數字個數()

    For i = 2 To 9

        c = 0

        For j = 1 To Len(Cells(i, "A"))

            If VBA.Asc(Mid(Cells(i, "A"), j, 1)) > 47 And VBA.Asc(Mid(Cells(i, "A"), j, 1)) < 58 Then

                c = c + 1

            End If

        Next

        Cells(i, "B") = c

    Next

End Sub

 

Sub 非數字個數()

    For i = 2 To 9

        '1.字數迴圈範圍

        c = 0

        For j = 1 To Len(Cells(i, "A"))

            '2.判斷是否為數字

            If VBA.Asc(Mid(Cells(i, "A"), j, 1)) <= 47 Or VBA.Asc(Mid(Cells(i, "A"), j, 1)) >= 58 Then

                '3.如果是就+1

                c = c + 1

            End If

        Next

        '4.傳給B欄i列的儲存格

        Cells(i, "C") = c

    Next

End Sub

 

Sub 留下數字()

    For i = 2 To 9

        '1.字數迴圈範圍

        S = ""

        For j = 1 To Len(Cells(i, "A"))

            '2.判斷是否為數字

            If VBA.Asc(Mid(Cells(i, "A"), j, 1)) > 47 And VBA.Asc(Mid(Cells(i, "A"), j, 1)) < 58 Then

                '3.如果是就+1

                S = S & Mid(Cells(i, "A"), j, 1)

            End If

        Next

        '4.傳給B欄i列的儲存格

        Cells(i, "D") = S

    Next

End Sub

 

Sub 留下英文()

    For i = 2 To 9

        '1.字數迴圈範圍

        S = ""

        For j = 1 To Len(Cells(i, "A"))

            '2.判斷是否為數字

            If VBA.Asc(VBA.UCase(Mid(Cells(i, "A"), j, 1))) > 64 And VBA.Asc(VBA.UCase(Mid(Cells(i, "A"), j, 1))) < 91 Then

                '3.如果是就+1

                S = S & Mid(Cells(i, "A"), j, 1)

            End If

        Next

        '4.傳給B欄i列的儲存格

        Cells(i, "E") = S

    Next

End Sub

 

Public Sub 留下中文()

    For i = 2 To 9

        '1.字數迴圈範圍

        S = ""

        For j = 1 To Len(Cells(i, "A"))

            '2.判斷是否為數字

            If VBA.Asc(Mid(Cells(i, "A"), j, 1))