Data realtime แสดงด้วย pivot table จาก access query

[godman]

คือผมอยากให้ sheet pivot summary แสดงข้อมูลจาก sheet แรกมาครับ แต่ปัญหาความต้องการคือให้ผู้ใช้ใส่เลข 3 ตัวท้ายของ part number หรือหมายงานชิ้นงาน เช่น 15695-04 ก็ใส่เพียง 695 เพราะ 04 ไม่เกี่ยวครับ แล้วให้ผู้ใช้สามารถเลือกวันวันที่ที่จะ updated ได้ด้วยครับ ไม่ทราบว่าต้องเขียน macro อย่างไรดีครับ ให้ผู้ใช้กรอกข้อมูล 2 ช่องบนนะครับ

[snasui]

ลองดูตัวอย่างตามไฟล์แนบครับ โดยมีการให้ชื่อกับ Data ใช้ Code VBA ตามด้านล่าง ตรวจสอบข้อมูลตามเงื่อนไข หากเข้าเงื่อนไขให้แสดง True หากไม่เข้าเงื่อนไขให้แสดง False

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        r.Offset(, -2) = Worksheets("Pivot Summary").Range("B2") Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
    If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
        MsgBox "Can not found data."
        Exit Sub
    Else
        Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
    End If
Next r
MsgBox "Finish."
End Sub

[godman]

ขอบคุณครับ อาจารย์ แต่ว่าต้องยอมรับว่าผมยังไม่ได้ลงลึกใน VBA ก็เลยต้องพึงพาอาจารย์ไปก่อน แต่เรื่องสูตรต่างๆที่เรียนไปผมก็นำไปต่อยอดครับ
แต่สำหรับกรณีนี้ ผมอาจจะให้ข้อมูลไม่ครบ หรืออธิบายไม่กระจ่าง คือ ที่อาจารย์ทำมาตรงเงืื่อนไข 100% ก็นำมา แต่ปัญหาคือผู้ใช้จำเวลาในระดับนาที ไม่ได้ นะสิครับ
ผมเลยคิดว่าควรจะเอาข้อมูล update ตั้งแต่ 6 ทุ่มจนถึง 6 ทุมของอีกวันคือเอาข้อมูลจาก 6 ทุ่มจนถึงเวลาปัจจุบัน ว่า data summary ยอดเท่าไหร่ครับและคำที่ลงท้ายด้วย xxx 3 ตัวหลังทั้งหมดครับ ผมได้ให้แนวทางคำตอบมาตามไฟล์แนบด้วยครับ ขอบพระคุณเป็ฯอย่างสูงครับ

[snasui]

ปรับ Code เป็นด้านล่าง ซึ่งเป็นการดูเฉพาะวันที่ ไม่ต้องดูเวลาครับ เนื่องจากงวดที่เวลาที่เป็นเงื่อนไขคือเวลา 1 วัน เริ่มหลังเที่ยงคืนจนถึงเที่ยงคืนอยู่แล้ว

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        DateValue(r.Offset(, -2)) = DateValue(Worksheets("Pivot Summary").Range("B2")) Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
    If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
        MsgBox "Can not found data."
        Exit Sub
    Else
        Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
    End If
Next r
MsgBox "Finish."
End Sub

[godman]

อาจารย์ครับ ผมลองใส่ 700 ลงไปข้อมูลมาไม่หมดครับ และถ้าผมต้องการใช้ช่อง วันที่ เวลา เป็น นับถึงวันที่ เวลา ณ ปัจจุบัน ต้องเขียนโค้ดยังไงครับ

[snasui]

ค่อย ๆ ถามตอบกันไปนะครับ เท่าที่ได้ตรวจสอบเงื่อนไข

B5 = 25/7/2011 14:33:52 (เงื่อนไขคือต้องเป็นวันที่ 25/7/2011)
F10 = 23/7/2011 19:29:23
F11 = 25/7/2011 9:43:43
F12 = 24/5/2011 4:16:00

ดังนั้นดึงมาได้รายการเดียวจึงถูกต้องแล้วครับ เพราะ F10 และ F12 ไม่เข้าเงื่อนไข

[snasui]

ปรับ Code เป็นด้านล่าง ซึ่งเป็นการดูเฉพาะวันที่ ไม่ต้องดูเวลาครับ เนื่องจากงวดที่เวลาที่เป็นเงื่อนไขคือเวลา 1 วัน เริ่มหลังเที่ยงคืนจนถึงเที่ยงคืนอยู่แล้ว

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        DateValue(r.Offset(, -2)) = DateValue(Worksheets("Pivot Summary").Range("B2")) Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
    If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
        MsgBox "Can not found data."
        Exit Sub
    Else
        Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
    End If
Next r
MsgBox "Finish."
End Sub

จาก Code ด้านบนยังไม่ถูกต้องเนื่องจากบรรทัดสับกันครับ ผมปรับมาให้ใหม่ตามด้านล่าง ซึ่งรวมทั้งการปรับให้ดูวันที่และเวลาจนถึงปัจจุบันแล้วครับ

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        (CDbl(r.Offset(, -2)) >= CDbl(Worksheets("Pivot Summary").Range("B2")) _
        And CDbl(r.Offset(, -2)) <= CDbl(Date + Time)) Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
Next r
If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
    MsgBox "Can not found data."
    Exit Sub
Else
    Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
End If
MsgBox "Finish."
End Sub

[godman]

อาจารย์ครับ ใช่ครับ อาจารย์ทำถูกแล้วครับ
อยากให้ข้อมูลมันแสดงทั้งหมดไม่ว่าจะอยู่ของวันใหน เช่น คีย์ 720 ก็ให้มันเอาข้อมูลของวันที่ 23 กค 24 กค 27 กค จนถึงเวลานี้now จะต้องเปลี่ยน code อย่างไรบ้างครับ

[snasui]

อยากให้ข้อมูลมันแสดงทั้งหมดไม่ว่าจะอยู่ของวันใหน เช่น คีย์ 720 ก็ให้มันเอาข้อมูลของวันที่ 23 กค 24 กค 27 กค จนถึงเวลานี้now จะต้องเปลี่ยน code อย่างไรบ้างครับ

ใช้ Code ล่าสุดด้านบนและวันที่ต้องคีย์วันที่ 23/7/2011 ครับ เพื่อจะได้นำข้อมูลตั้งแต่ 23/7/2011 จนถึงปัจจุบันมาแสดง

[godman]

ผมลอง run ใส่ 695 มันขั้น errer ว่า run time errer 12 type mismatch ครับ

[godman]

ผมแนบไฟล์มาด้วยครับ ลืมไป

[snasui]

บรรทัดที่ 392-406 คือต้นเหตุครับ

ผมปรับ Code ให้ใหม่ตามด้านล่างเพื่อให้ Run ได้แม้ว่าจะเกิด Error ครับ

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
On Error Resume Next
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        (CDbl(r.Offset(, -2)) >= CDbl(Worksheets("Pivot Summary").Range("B2")) _
        And CDbl(r.Offset(, -2)) <= CDbl(Date + Time)) Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
Next r
If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
    MsgBox "Can not found data."
    Exit Sub
Else
    Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
End If
MsgBox "Finish."
End Sub

[godman]

ผมลองรัน 695 มาเฉพาะของวันที่ 27 ครับ แต่ของอีก 2 วันไม่แสดงครับอาจารย์ ใช้มาโครใหม่แล้ว

[godman]

ผมลองใส่ 700 ก็ไม่มาครับ มีหลายหลายวัน ก่อนหน้า

[snasui]

อีก 2 วันที่ว่าคือวันที่เท่าไรครับ

สำหรับ Code ที่ผมเขียนนั้น เป็นการตรวจสอบตามด้านล่างครับ

ในเซลล์ B2 ชีท Pivot Summary กรอกวันที่ใดไว้ โปรแกรมจะดึงข้อมูลตั้งแต่วันนั้นจนถึงปัจจุบันครับ ถ้ากรอกวันที่ปัจจุบันก็ได้ข้อมูลเพียงวันปัจจุบันเท่านั้นครับ

ไม่ทราบว่าที่ต้องการเป็นแบบใด ขอรายละเอียดที่ชัดเจนอีกครั้งครับ อธิบายได้เต็มที่ครับ

[snasui]

ส่วน Code ด้านล่างนี้เป็นการตรวจสอบว่าในเซลล์ B2 ของชีท Pivot Summary เป็นวันที่เท่าไร ก็ให้ดึงข้อมูลของวันที่ก่อนหน้าจนกระทั่งถึงวันที่ในเซลล์ B2

Code: Select all

Sub FillData()
Dim rAll As Range
Dim r As Range
On Error Resume Next
With Worksheets("Data input")
    Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
    If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
        CDbl(r.Offset(, -2)) <= CDbl(Worksheets("Pivot Summary").Range("B2")) Then
            r.Offset(, 8) = True
    Else
            r.Offset(, 8) = False
    End If
Next r
If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
    MsgBox "Can not found data."
    Exit Sub
Else
    Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
End If
MsgBox "Finish."
End Sub

[godman]

ผมอาจจะสื่อสารคลาดเคลื่อน ผมต้องการให้วันในอดีตก่อนหน้า จนถึง วันนี้ เวลานี้ แสดงครับ แสดงว่า code บนสุดโอเค ครับ ขอบคุณครับ ผมลองดูครับ

[godman]

ได้แล้วครับ แต่ผมอาจจะมีคำถามเพิ่มเติมในภายหลัง ครับ เกี่ยวกับการแสดงข้อมูลจาก access และการแสดงข้อมูลเลือก วันที่นี้แวลานี้ ถึง วันที่อื่นเวลาอื่น แล้วให้มันไปแสดงโดยขึ้นไฟล์ใหม่ แล้วให้ sheet หายไปคงเหลือแต่ sheet หลัก แต่ตอนนี้ขอบคุณมากครับ อาจารย์

[godman]

อ้างอิงจาก code ที่เกี่ยวข้องข้างบนครับ
Sub FillData()
Dim rAll As Range
Dim r As Range
On Error Resume Next
With Worksheets("Data input")
Set rAll = .Range("E2", .Range("E2").End(xlDown))
End With
For Each r In rAll
If CInt(Mid(r, 3, 3)) = Worksheets("Pivot Summary").Range("B1") And _
CDbl(r.Offset(, -2)) <= CDbl(Worksheets("Pivot Summary").Range("B2")) Then
r.Offset(, 8) = True
Else
r.Offset(, 8) = False
End If
Next r
If Application.CountIf(Worksheets("Data input").Range("M:M"), True) = 0 Then
MsgBox "Can not found data."
Exit Sub
Else
Worksheets("Pivot Summary").PivotTables(1).PivotCache.Refresh
End If
MsgBox "Finish."
End Sub
อาจารย์ครับจากตรงนี้ ถ้าผมเปลี่ยนเป็น -1 หมายความว่าให้เอาข้อมูลประวัติก่อนหน้าจนถึงวันที่เวลาตามที่เลือกในช่อง B2 ใช่ไหมครับ เพราะผมลองเปลี่ยนแล้วมันโอเค นำไปใช้ได้ไใช่ไหมครับ และนำไปประยุกต์ใช้ในเรื่องอื่นๆได้ด้วยใช่ไหมครับ ผมตื่นเต้นมากที่มันทำได้
CDbl(r.Offset(, -2)) <= CDbl(Worksheets("Pivot Summary").Range("B2")) Then

[godman]

ผมต้องรบกวนเพิ่มเติมครับ เนื่องจากผู้บังคับบัญชาต้องการให้แสดงข้อมูลจากวนที่....ถึงวันที่.....ครับ โดยให้คีย์ part number เต็ม ครับ ต้องเปลี่ยนโค้ดอย่างไรบ้างครับ ขอขอบคุณมากนะครับที่ให้ code ผมจะลองศีกษา โค้ดอาจารย์ดู ผมแนบไฟล์มาด้วย