EXCEL VBA批次查詢清單到新增與刪除工作表

 

預覽畫面:

 

結果畫面:

 

如何將單次查詢改為批次查詢,

可以按照分類清單,將資料自動查詢並輸出成為工作表,

這部分可以先從建立清單開始,

其次是,批次新增工作表,

若有舊的查詢結果則先刪除工作表之後,

重新批次查詢一次。

 

**如何批次查詢資料

1.建立清單

2.批次新增工作表

Public Sub 批次新增工作表()

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

       X = Sheets("清單").Cells(i, "A")

       '1.新增工作表

       Sheets.Add After:=Sheets(Sheets.Count)

       Sheets(Sheets.Count).Name = X

   Next

End Sub

 

3.批次刪除工作表

Public Sub 批次刪除工作表()

   Application.DisplayAlerts = False

   For i = Sheets.Count To 3 Step -1

       Sheets(i).Delete

   Next

   Application.DisplayAlerts = True

End Sub

4.批次篩選

Sub 批次篩選業務()

'關閉畫面更新

   Application.ScreenUpdating = False

   Sheets(1).Select

   Call 批次刪除工作表

 

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

        X = Sheets("清單").Cells(i, "A")

 

        '1.游標放B1

        Range("B1").Select

        '2.篩選

        Selection.AutoFilter

        ActiveSheet.Range("$A$1:$L$2500").AutoFilter Field:=3, Criteria1:=X

        '3.複製

        Range("A1").Select

        Range(Selection, Selection.End(xlToRight)).Select

        Range(Selection, Selection.End(xlDown)).Select

        Selection.Copy

 

        '4.新增工作表

        Sheets.Add After:=Sheets(Sheets.Count)

        Sheets(Sheets.Count).Name = X

 

        '5.貼上

        Range("A1").Select

        ActiveSheet.Paste

        '6.自動調整欄寬

        Selection.Columns.AutoFit

       

        Range("A1").Select

        '7.切回原工作表

        Sheets(1).Select

        Application.CutCopyMode = False

        '8.取消篩選

        Selection.AutoFilter       

        Range("A1").Select

 

    Next

 Application.ScreenUpdating = True

End Sub

**如何增加狀態列訊息(Application.StatusBar)

 

**補充如何自行撰寫移除重複

Public Sub 移除重複()

   '排序

   Range("A1").CurrentRegion.Select

   '遞增xlAscending或1、遞減xlDescending或2

   Selection.Sort Key1:=Range("A1"), Order1:=1, Header:=xlYes

 

   r = Range("A1").End(xlDown).Row

   For i = r To 2 Step -1

       'Cells(i, "A").Select

       If Cells(i, "A") = Cells(i - 1, "A") Then

           Rows(i - 1).Delete

       End If

   Next

End Sub

 

5.共用程序與傳遞參數

Sub 批次篩選(清單 As String, 欄位 As Integer)

   'On Error Resume Next

   

   Application.ScreenUpdating = False

   

   Call 批次刪除工作表

   

   r = Sheets("清單").Range(清單 & "2").End(xlDown).Row

   For i = 2 To r

       Application.StatusBar = "目前進度:總共筆數:" & r - 1 & " 進行筆數:" & i - 1

       X = Sheets("清單").Cells(i, 清單)

       '1.游標放B1

       Range("B1").Select

       '2.篩選

       Selection.AutoFilter

       ActiveSheet.Range("$A$1:$L$2500").AutoFilter Field:=欄位, Criteria1:=X

       '3.複製

       Range("A1").Select

       Range(Selection, Selection.End(xlToRight)).Select

       Range(Selection, Selection.End(xlDown)).Select

       Selection.Copy

       '4.新增工作表

       Sheets.Add After:=Sheets(Sheets.Count)

       Sheets(Sheets.Count).Name = X

       '5.貼上

       Range("A1").Select

       ActiveSheet.Paste

       '6.自動調整欄寬

       Selection.Columns.AutoFit

      

       Range("A1").Select

       '7.切回原工作表

       Sheets(1).Select

       Application.CutCopyMode = False

       '8.取消篩選

       Selection.AutoFilter

      

       Range("A1").Select

   Next

   

   Application.ScreenUpdating = True

   

End Sub

 

6.呼叫共用程序

Sub 批次篩選業務()

    Call 批次篩選("A", 3)

End Sub

Sub 批次篩選產業別()

    Call 批次篩選("B", 4)

End Sub

Sub 批次篩選產品()

   Call 批次篩選("C", 5)

End Sub

Sub 批次篩選客戶名稱()

   Call 批次篩選("D", 12)

End Sub

 

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

 

教學影音完整版在論壇:

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

 

課程特色:

1.如何將函數轉成VBA2.VBA與資料庫快速結合

 

EXCEL函數 VBA程式設計資料庫是分別屬於三個領域的知識,

但卻是目前大家都需要的一項專業技能,要把三者融合的很好實在非常不容易,

剛好我有近20年的VB程式設計資料庫設計的經驗,

教EXCEL函數與相關課程也有多年,因此清楚如何把最重要的知識教給大家,

ADO資料庫設計的知識非常多,但根據我多年的設計實務經驗,

覺得最重要的是掌握SQL語言,就可以輕易的完成查詢、新增、修改與刪除等功能,

就可以輕易的完成自己想處理的大量資料,大大提高工作效率了!

 

上課用書是:

Excel函數&VBA其實很簡單(http://www.books.com.tw/exep/prod/booksfile.php?item=0010457292)

Excel VBA 與資料庫整合大活用(http://www.books.com.tw/exep/prod/booksfile.php?item=0010463634)

 

完整教學影音DVD申請:http://goo.gl/ZlBZE

論壇:http://groups.google.com/group/labor_excel_vba?hl=zh-TW

 

其他相關學習:

 

1.EXCEL VBA設計(自強基金會2012)第4次上課

http://terry55wu.blogspot.tw/2012/03/excel-vba20124.html

 

2.如何把EXCEL"函數"變為 "VBA"?自強基金會2012第5次上課

http://terry55wu.blogspot.tw/2012/04/excel-vba.html

 

3.自強基金會2012第8次上課

http://terry55wu.blogspot.tw/2012/05/excel-vba20128.html

 

4.自強基金會2012第9次上課

http://terry55wu.blogspot.tw/2012/05/excel-vba20129.html

 

5.EXCEL_VBA與資料庫--自強基金會2012(Ending)

http://terry55wu.blogspot.tw/2012/07/excelvba-2012ending.html

 

EXCEL,VBA,函數東吳進修推廣部,EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel  excel vba教學視頻 excel函數教學 excel函數 MYSQL

文章標籤
創作者介紹
創作者 吳老師 的頭像
吳老師

吳老師教學部落格(痞客邦分站)

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