หน้าเว็บ

วันอาทิตย์ที่ 26 มิถุนายน พ.ศ. 2554

การแสดงข้อมูลจากฐานข้อมูลตามเงื่อนไขด้วย VBA

ตัวอย่างการดึงข้อมูลจากฐานข้อมูลที่จะนำเสนอต่อไปนี้เป็นการใช้ VBA ในการจัดการ ซึ่งเป็นหนึ่งในหลาย ๆ วิธีครับ โดยมีฐานข้อมูลพนักงานอยู่ที่ชีท Database ส่วนชีท Report จะเป็นการดึงรายชื่อของพนักงานมาแสดงตามแผนกที่เลือกในเซลล์ E2 ของชีท Report

หลังจากนำข้อมูลมาวางแล้วก็กำหนดให้จัดรูปแบบให้ด้วยเพื่อให้มีความสวยงามน่าใช้งาน โดยทำการจัดรูปแบบเฉพาะช่วงที่มีข้อมูลเท่านั้น ดูภาพ Database และ Report ตามด้านล่างครับ

ภาพตัวอย่างฐานข้อมูลพนักงาน

EmpNameByDept

ภาพตัวอย่าง Report ที่ต้องการ

EmpNameByDept1

ภาพแสดงตัวอย่างการทำงาน

ShowDataByCondWithVBA

โดยใช้ Code VBA ดังต่อไปนี้

Smile Code ด้านล่างวางไว้ที่ชีท Report

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$2" And Target <> "" Then
ShowEmp
ElseIf Target.Address = "$E$2" And Target = "" Then
MsgBox "Please select data."
End If
End Sub


Smile Code ด้านล่างวางที่ Module ปกติ



Option Explicit
Option Base 1

Sub ShowEmp()
Dim a() As Variant, lng As Long
Dim r As Range, rAll As Range
Dim rt As Range, rl As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
rl = Rows.Count
With Worksheets("Database")
Set rAll = .Range("F2", .Range("F" & rl).End(xlUp))
End With
For Each r In rAll
If r = Worksheets("Report").Range("E2") Then
lng = lng + 1
ReDim Preserve a(5, lng)
a(1, lng) = lng
a(2, lng) = r.Offset(0, -5)
a(3, lng) = r.Offset(0, -4)
a(4, lng) = r.Offset(0, -3)
a(5, lng) = r.Offset(0, -2)
End If
Next r
If lng > 0 Then
With Worksheets("Report")
Set rt = .Range("A5", .Range("E" & lng - 1 + 5))
.Range("A5", .Range("A" & rl).End(xlUp).Offset(0, 4)).ClearContents
.Range("A5:E5").Copy
rt.PasteSpecial xlPasteFormats
rt = Application.Transpose(a)
.Range("B5", .Range("B" & rl).End(xlUp)).NumberFormat = "000000"
.Range(.Range("A4").End(xlDown).Offset(1, 0), .Range("E" & rl)).Clear
.Range("E2").Activate
End With
Else
MsgBox "Data not found."
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



ซึ่งมีหลักการทำงานสำคัญ ๆ ดังนี้



  1. นำเซลล์ E2 ในชีท Report ไปเปรียบเทียบกับค่าในคอลัมน์ F ของชีท Database
  2. ค่าที่ตรงกันจะเก็บไว้ใน Array
  3. นำ Array มาวางที่ชีท Report
  4. จัดรูปแบบให้กับรายงานที่ได้

นอกจากการใช้ Array เข้ามาช่วยแล้ว ทางเลือกอื่นในการเขียน Code เช่น สามารถเลือกเขียน Code ให้ทำ Advanced Filter แล้วเลือกเฉพาะข้อมูลที่ต้องการมาแสดงเช่นนี้เป็นต้นครับ Winking smile

6 ความคิดเห็น:

ไม่ระบุชื่อ กล่าวว่า...

ShowEmp ,Target คืออะไร ตรงไหนค่ะ แล้วต้องเอา Code S ub ShowEmp()ไปแปะ ลองทำตามแล้ว Error ค่ะ

snasui กล่าวว่า...

ShowEmp คือชุดคำสั่งให้แสดงข้อมูลตามเงื่อนไข
Target คือเซลล์ที่ User กำลังทำงาน

ส่งไฟล์ที่ลองทำเองแล้วไปที่ http://www.snasui.com/ จะช่วยดูให้ครับ

การใช้ VBA ในการดึงข้อมูลตามต้องการด้วยวิธีนี้ ไม่เหมาะกับผู้ที่ไม่คุ้นเคยกับ VBA เพราะไม่สามารถแก้ไขเปลี่ยนแปลงเองได้หากความต้องการเปลี่ยนไป

ไม่ระบุชื่อ กล่าวว่า...

ลักษณะไฟล์ที่ทำจะ้หมือนของคุณเลยค่ะ คือ มี Sheet ข้อมูล และ อีก sheet คือเลือกตัวที่ต้องการจะ Print ก็เลยจะเลียนแบบของคุณ แต่ก็ Error ค่ะ

snasui กล่าวว่า...

:) ถ้าเหมือนกันทุกอย่างย่อมไม่ Error แน่นอนครับ

ไม่ระบุชื่อ กล่าวว่า...

อยากสอบถามค่ะ
ต่อยอดจากตัวอย่างนะค่ะ ถ้าหยอดค่าให้แสดงข้อมูลแล้ว ให้มีปุ่มกด แล้ว แสดงออกมาเป้น Report ที่เป็น File Pdf ได้ไหมค่ะ

รบกวนไขข้อกระจ่างด้วยค่ะ
ขอบคุรล่วงหน้าคะ

snasui กล่าวว่า...

แสดงเป็น Report PDF ก็ต้อง Save File เป็น PDF ครับ กรณี 2010 สามารถทำได้เลย ส่วน 2007 ลงไปคงต้องติดตั้ง Add-ins เพิ่ม

กรณีสอบถามปัญหา Excel ให้ถามที่ http://www.snasui.com/ ครับ