EXCEL2013版VBA如何快速建立查詢系統

 

預覽畫面:

 

關於資料查詢部分,EXCEL的基本查詢有檢視與參照的相關函數,

最常用的有VLOOKUP等函數

查詢的功能很有限,實在無法達到自動化的目的,

所以如果要查詢更複雜切大量的資料,可以用資料庫的查詢功能,

可以藉由SQL語法Select敘述,達成所需要的效果,

但若只是單純的EXCEL資料,則可以藉由篩選功能完成,

若需要達到自動化目的,可以配合巨集錄製與修改。

以下屆由問題05範例來說明。

 

完成畫面:

 

 

01_篩選業務巨集錄製程式碼

02_防止查詢工作表已存在(手動)

03_防止查詢工作表已存在(自動)

04_如何避免位輸入資料與相同工作表名稱

05_如何關閉VBA的刪除提示訊息

06_如何產生其他欄位查詢與簡化與呼叫程序

 

**篩選功能與錄製巨集

練習錄製並修改篩選業務

 

Sub 篩選業務()

    X = InputBox("請輸入業務姓名!!")

    '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

    '8.取消篩選

    Selection.AutoFilter

   '9.切回到A1

   Range("A1").Select

End Sub

Sub 篩選業務_簡化()

   X = InputBox("請輸入業務姓名!!")

   '2.篩選

   Sheets(1).Range("$A$1:$L$" & Range("A1").End(xlDown).Row).AutoFilter Field:=3, Criteria1:=X

   '3.複製

   Range("A1").CurrentRegion.Copy

 

   Range("A1").CurrentRegion.select

  Selection.copy

   '4.新增工作表

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

   Sheets(Sheets.Count).Name = X

   '5.貼上

   Range("A1").Select

   ActiveSheet.Paste

   '6.自動調整欄寬

   Columns("A:L").AutoFit

   Range("A1").Select

   '7.切回原工作表

   Sheets(1).Select

   '8.取消篩選

   Selection.AutoFilter

   Range("A1").Select

End Sub

 

**防止按鈕因篩選而變形:

 

 

**如何將篩選改為關鍵字查詢

方法1:

 

方法2:

 

   Y = "=*" & X & "*"

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

 

**避免程式錯誤的三種防呆判斷

Sub 篩選業務_防呆()

 

   x = InputBox("請輸入業務姓名!!")

   

   '防呆1:如何防止查詢已存在的名稱而產生錯誤

   For i = 2 To Sheets.Count

       If x = Sheets(i).Name Then

           'MsgBox "工作表已存在請先刪除!!"

           'Exit Sub

           Application.DisplayAlerts = False

           Sheets(i).Delete

           Application.DisplayAlerts = True

           Exit For            

       End If

   Next

   '防呆2:無填寫

   If x = "" Then

       MsgBox "請務必輸入資料!!"

       Exit Sub

   End If    

   

   '2.篩選

   Selection.AutoFilter

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

 

   '防呆3:無結果

   If Range("A1").End(xlDown).Row = 1048576 Then

       MsgBox "查無資料!!"

       Selection.AutoFilter

       Exit Sub

   End If

   

   '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

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) 人氣()