สวัสดีครับ
ต่อเนื่องจากเรื่องที่แล้วครับ (ทำให้ Excel ฉลาดด้วย Macro) มาตอนนี้ว่าด้วยการดึงข้อมูลจาก Database เข้ามาใน Excel โดยใช้คำสั่ง 
SQL เลือกดึง ข้อมูลตามต้องการ ขั้นตอนของการทำโปรแกรมนี้ก็เริ่มจาก เตรียม Database ที่ต้องการติดต่อด้วย จากนั้นตรวจดูว่าที่ตัว
Excel ว่ามี module ของ Microsoft Query หรือไม่ ถ้าไม่มีให้ Add จากแผ่นที่ใช้ Install Program เพิ่มโดยใช้ Add and Remove Program 
เริ่มด้วยไปที่ การสั่งบันทึก Macro จากนั้นไปที่ Menu DATA > Get External Data > Create new Query

   ภาพ แสดงการเข้า Menu query                                       ภาพแสดง การอ้าง ตำแหน่ง Database

 

                                       ภาพแสดง Query ODBC        


เมื่อทำตามคำสั่ง Wizard จบก็จะได้ข้อมูลมาอยู่ใน Cell ของตาราง Excel จากนั้นให้ กดปุ่มหยุดการบันทึก คำสั่งแมคโคร และมาทำการแก้
ไขบางส่วนของ Source Code โปรแกรม VB เช่น การเพิ่มเงื่อนใน Where cause เป็นต้น
จาก Code ตัวอย่างจะเป็นการทำงานโดยรวม จากอ่านข้อมูลที่ file database ชื่อ ictran.dbf  ซึ่งเป็น Foxbase (กรณีถ้าเป็น database  ชนิดอื่นเช่น  Access ก็ให้เลือกตอนทำ Wizard ซึ่งจุดนี้ ถ้าเป็น Oracle ก็สามารถเลือกได้แต่ต้องมี driver ด้วย) จากนั้นทำคำสั่ง VB ต่อเนื่อง
ไปในส่วนที่เป็น SQL เพื่อดึงข้อมูลด้าน วัตถุดิบ ตามชื่อ Field ที่ระบุ และ ตามด้วย Where cause ที่กำหนดว่าให้เลือกเฉพาะข้อมุล ของ
วัถตุดิบที่มีรายการตามระบุใน Table ที่ 2 เท่านั้น ซึ่งการทำงาน จะเป็น คำสั่ง SQL แบบซ้อนคำสั่ง เพื่อทำงานในลักษณะ ดึงข้อมูลตาม
เงื่อนไขที่เลือกข้อมูลจาก SQL ในส่วนที่ 2 โดยข้อมูลนั้นจะอยู่คนละ Table ในที่นี้คือ file ชื่อ bom3.dbf นั่นเอง 

Sub BOM()
'
' Keyboard Shortcut: Ctrl+b
'

ตัวแปร sqlstring กำหนดเพื่อเป็น SQL command ที่จะเลือก get ข้อมูล filed ต่างๆ พร้อมทั้งบอกว่าดึงจากที่ใดด้วย
sqlstring = "SELECT ITEMNO, DESP, LOCATION, SIGN, QTY, TYPE, DATE, REFNO, VOID, PRICE_BIL,NAME,BREM1,BREM2" & " FROM `c:`\ictran.dbf " _
& "WHERE ITEMNO " _
& " IN (SELECT ITEMNO from `c:'\bom3.dbf)"   กำหนดคำสั่ง SQL ซ้อนเพื่อดึงข้อมูลจาก Table อีก Table หนึ่ง
                                                            ที่ค่าตัวแปร DBQ และ Default จะเป็นการบอกตำแหน่ง Directory ที่เก็บ file ข้อมูลไว้

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;CollatingSequence=ASCII;DBQ=c:;DefaultDir=c:;Deleted=1;Driver={Microsoft FoxPro Driver (*.dbf)};DriverId=536;FIL=Fo" _
), Array( _
"xPro 2.0;ImplicitCommitSync=Yes;MaxBufferSize=1024;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UserC" _
), Array("ommitSync=Yes;")), Destination:=Range("A1"))   กำหนดตำแหน่งที่จะนำข้อมูลมาวางใน Sheet ณ.ช่อง A1ซึ่งจุดนี้สามารถเปลี่ยนได้ตามความต้องการ
.Sql = Array(sqlstring)          ตัวโปรแกรมจะนำ sqlstring มา run ตรงนี้
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.Refresh BackgroundQuery:=False
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.SavePassword = True
.SaveData = True
End With
End Sub

เมื่อได้ข้อมูลจากการดึงจาก SQL แล้ว Source code ต่อไปนี้เป็นการ จัดกลุ่มข้อมูล เริ่มจากการ Sort ข้อมูล และ การวน Loop เพื่อ SUM
ค่าต่างๆ พร้อมทั้งนำค่าที่อยู่ใน Cells มาตรวจสอบเงื่อนไข หลังจากนั้นมีการแทรก ROW เพิ่ม พร้อมทั้งทำสีพื้นให้เป็นสี แดง แล้วนำค่าที่
SUM ได้มาใส่ในตำแหน่งช่อง Cell ที่ต้องการ

Sub Sum()
'
' Keyboard Shortcut: Ctrl+s
'
'Sheets("sheet2").Select      เลือก sheet ที่จะทำงาน
Columns("A:K").Select      เลือกบริเวณข้อมูลที่ต้องการทำการ Sort
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("K2") _    กำหนด Sort key ณ.ช่อง cells ต่างๆคือ A2,K2 และ C2
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Cells(1, 1).Select

i = 2                   กำหนดตัวแปร i
QTYSUM = 0    กำหนดตัวแปร 
Do Until ((Cells(i, 1).Value) = "")       เริ่มต้น วน Loop จนกว่าเงื่อนไขเป็นจริง 
Cells(i, 1).Select
If Trim(Cells(i, 9).Value) = "Y" Then     ตรวจสอบว่า collum  I row หมายเลขตามตัวแปร i ว่ามีค่าเป็นตัวอักษร  Y หรือไม่ ซึ่งทั้งหมดต้อง trim อักษรว่าทิ้งด้วย 
Cells(i, 4).Value = 0
End If
QTYSUM = QTYSUM + (Cells(i, 4).Value * Cells(i, 5).Value)
If (Cells(i, 1).Value <> Cells(i + 1, 1).Value) Or (Cells(i, 3).Value <> Cells(i + 1, 3).Value) Then
Rows(i + 1).Select                 เลือกข้อมูลที่แถว หมายเลขตามตัวแปร i+1 แล้ว select เป็นแถบถึบ
Selection.Insert Shift:=xlDown         Insert เพิ่มแถวของ sheet
i = i + 1
Rows(i).Select
With Selection.Interior
.ColorIndex = 3                     เปลี่ยนสีพื้นเป็นสีแดง (แทนด้วยเลข 3)
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells(i, 1).Value = Cells(i - 1, 1).Value
Cells(i, 3).Value = Cells(i - 1, 3).Value           copy ค่าไปไว้อีก ช่องหนึ่ง
Cells(i, 5).Value = QTYSUM

QTYSUM = 0
End If
i = i + 1
Loop
End Sub

แถมท้ายด้วยการทำหน้าจอที่มีปุ่มที่สวยงาม และเมื่อ User กด ก็จะเกิดการทำงานตาม Macro ที่ระบุไว้ ขั้นตอนการทำเริ่มโดย ไปที่ Menu
View > Toolbars > Form จากนั้นใช้ Mouse Drag ปุ่มมาวางไว้ที่ Sheet จากนั้น แก้ข้อความชื่อปุ่ม หรือเปลี่ยนสี ของตัวอักษรตามต้องการ นอกจากนั้นยัง สามารถนำรูปต่างๆ มา เป็น Background เพื่อเพิ่มความสวยงามได้อีกด้วยครับ
                            
สุดท้ายนี้ผู้เขียนหวังเป็นอย่างยิ่งว่าคงเป็นประโยชน์ตามสมควร แก่ผู้ที่ต้องการ ทำให้เกิดประโยชน์สูงสุดจาก ตัว Software Excel

เขียนเสร็จเมื่อ
วันที่ 10 ส.ค. 44