การดึงข้อมูลจาก Web Query และเพิ่มข้อมูลไปแบบอัตโนมัติ

[hua4106312]

เรียนอาจารย์ครับ
ผมลองศึกษาเทคนิกต่างๆในเวปต่างๆ เกี่ยวกับการใช้คำสั่ง New Web Query เพื่อดึงข้อมูลมาจาก Website
แต่ยังไม่สามารถที่จะนำไปประยุกต์ได้ตามจุดประสงค์ ซึ่งก็คือ
1. ต้องการให้ไฟล์ Excel 1 ไฟล์นี้ ทำการรับ และแปลงเป็นฐานข้อมูลเก็บข้อมูลที่อัพเดทไว้เรื่อยๆ
2. ข้อมูลจาก Web Query จะอยู่ใน sheet 1 ส่วนข้อมูลที่จะสะสมไปเรื่อยๆ ต้องการให้อยู่ใน sheet 2
3. หากวันใดที่อาจจะลืมเปิดไฟล์นี้ขึ้นมาใช้งาน (ซึ่งผมเข้าใจว่า อาจจะทำให้ได้รับข้อมูลที่ขาดช่วงไป) ก็อยากที่จะให้ไฟล์ Excel นี้ทำการเก็บข้อมูลได้อย่างต่อเนื่องได้
4. ผมลองตั้งเวลาสำหรับ Web Query ที่จะให้มาแสดงไว้ใน Sheet 1 ทุกๆ 2 นาที แต่ก็ต้องการที่จะให้ Sheet 1 ส่งข้อมูลเพิ่มไปที่ Sheet 2 ทุกๆครั้งหลังจากที่ Sheet 1 Auto Refresh แล้วน่ะครับ

ผมลองศึกษาใน Google หรือค้นหา Key word จากที่อื่นๆ อย่างเช่น
- Automatically Copy Paste+VBA
- Import data realtime
- Dashboard
และอื่นๆอีกมากมายครับ แต่ก็ยังไม่เข้าใจ

ขอคำแนะนำด้วยครับ หรืออาจจะบอกเฉพาะหัวข้อที่เกี่ยวข้อง ซึ่งอาจารย์อาจจะเคยลงไว้แล้วก็ได้ครับ
ทั้งนี้ผมได้แนบไฟล์ตัวอย่างมาด้วยครับ

ขอบคุณครับ

[snasui]

ไม่สามารถดูไฟล์แนบได้ ช่วยแนบมาให้อีกรอบครับ

[hua4106312]

ไฟล์ตัวอย่าง ครับอาจารย์

[snasui]

สอบถามเพิ่มเติมครับ ลักษณะของข้อมูลต้นแหล่งเปลี่ยนแปลงอย่างไรครับ เปลี่ยนแปลงทุกวัน ทุกชม. ทุกนาที ฯลฯ และลักษณะการเก็บข้อมูลคือเก็บเฉพาะที่ต่างหรือเก็บทุกรายการ ฯลฯ

[hua4106312]

เรียนอาจารย์ ครับ
ข้อมูลต้นทาง จะเปลี่ยนไปทุกๆ 5 นาที ครับ
แต่จะ Fix จำนวนของข้อมูล เช่นมี 100 แถว
ในแต่ละแถวจะระบุเวลากำกับไว้ด้วย
ซึ่งแต่ละครั้งที่แหล่งข้อมูลทำการอัพเดททุก 5 นาทีแล้ว ข้อมูลที่อยู่ด้านล่างสุดจะหายไป แต่จะมีข้อมูลใหม่เพิ่มขึ้นมาที่ด้านบนสุดของตารางแทน ซึ่งอย่างไรก็ตาม ข้อมูลก็ยังคงมี 100 แถวเหมือนเดิมครับ
ยกตัวอย่างเหมือนกับว่า มีข้อมูลตั้งแต่นาทีที่ 1 จนถึง นาทีที่ 100 แล้ว
เมื่อข้อมูลแหล่งต้นทางทำการอัพเดท 1 ครั้ง จะทำให้ข้อมูลในนาทีที่ 1 ที่อยู่ด้านล่างของตารางหายไป แต่จะมีข้อมูลนาทีที่ 101 เพิ่มเข้ามาแทน (แต่ข้อมูลในตารางยังมี 100 แถวเหมือนเดิมครับ)

โดยผมมีแนวคิดในการดักจับข้อมูลที่ต้องการจะเก็บสะสมไว้คือ
ดักจับข้อมูลในแถวที่อยู่กลางๆตารางของ sheet 1 ไปเรื่อยๆทุกๆ 5 นาที แล้วจึงส่งไปที่ sheet 2 น่ะครับ เพราะยังไงเดี๋ยวข้อมูลที่อยู่ด้านบน มันจะค่อยๆเลื่อนลงมาหาแถวที่เราต้องการจะเก็บน่ะครับ (เคยลองนั่ง refresh จับสถิติตอนกลางคืนแล้ว รู้สึกว่า ข้อมูลต้นทางจะทำการ อัพเดทช้าน่ะครับ)
สำหรับการเก็บข้อมูลที่ต้องการ คือ จะเก็บทั้งหมดครับ
ลักษณะของ Sheet 1 จะทำไว้เพื่อติดต่อกับ Web Query เท่านั้น ซึ่งจะทำให้มีข้อมูลอัพเดทอยู่เรื่อยๆ และมีข้อมูลเท่าที่เวปไซต์ให้มา
ลักษณะของ Sheet 2 จะทำไว้เพื่อดักจับข้อมูลในแถวที่ตั้งค่าไว้ โดยให้ดักจับทุกๆ 5 นาที และสะสมไปเรื่อยๆ (เพิ่มบรรทัดข้อมูล)

ขอบคุณอาจารย์ด้วยครับ
hua

[snasui]

ผมเข้าใจว่าคุณ hua เข้าใจ VBA แล้ว จึงน่าจะปรับปรุงเองให้เป็นไปตามต้องการได้ครับ

Code ที่ให้ไปต่อไปนี้จะเป็นการ Refresh Web Query ทุก 5 นาทีโดยให้คุณ hua ไปยกเลิกการตั้งเวลาสำหรับการ Refresh 2 นาทีนั้นออกไปครับ และเมื่อทำการ Refresh Web Query แล้วจะนำข้อมูลที่ได้ไปวางใน Sheet2 ต่อลงไปด้านล่างเรื่อย ๆ

ใน Sheet2 ผมมีปุ่ม Start กับ Stop เพื่อเริ่มและยกเลิกการทำงานของ Code หากว่าต้องการให้ Code เริ่มทำงานเมื่อเปิดไฟล์และยกเลิกเมื่อปิดไฟล์ ก็ใช้ Event ของ Workbook_Open และ Workbook_BeforeClose ตามลำดับมาช่วยได้ ครับ

Code: Select all

Option Explicit
Dim CountDown As Date

Sub Timer()
    CountDown = Now + TimeValue("00:05:00")
    Application.OnTime CountDown, "Reset"
End Sub

Sub Reset()
Dim count As Date
count = TimeValue("0:05:00")
On Error Resume Next
count = count - TimeValue("0:05:00")
    If count <= 0 Then
        Worksheets("Sheet1").Range("A9").QueryTable.Refresh BackgroundQuery:=False
        PasteData
    End If
Call Timer
End Sub

Sub DisableTimer()
On Error Resume Next
    Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

Sub PasteData()
Dim rs As Range
Dim rt As Range
With Worksheets("Sheet1")
Set rs = .Range("N9", .Range("N" & Rows.count).End(xlUp)) _
    .Offset(0, -13).Resize(, 14)
End With
Set rt = Worksheets("Sheet2").Range("N" & Rows.count).End(xlUp).Offset(1, -13)
rs.Copy rt.Paste
Application.CutCopyMode = False
End Sub

ดูตัวอย่างตามไฟล์แนบครับ

[hua4106312]

ขอบคุณครับอาจารย์
เดี๋ยวคงต้องขอนั่งศึกษาตัวสูตรซักหน่อยครับ

(เพราะเมื่อกี๊ลอง Run ดูแล้ว ข้อมูลไหลมาเป็นพรวนทั้ง Worksheet 1 เลยครับ)

[hua4106312]

หลังจากที่ผมได้ทำการแกะสูตรจากที่อาจารย์ได้แนะนำมาแล้ว
ผลปรากฏว่า ใช้งานได้ดีเลยครับ
แต่หลังจากนั้น ผมพยายามทดลองเขียนประยุกต์การใช้งานแบบอื่นๆแล้ว กลับมีข้อสงสัยเล็กน้อยครับ ดังนี้

1. หากเปิดไฟล์ Book1 ที่ทำการเขียน VBA แล้ว ถ้าเปิดไฟล์อื่นๆอีก จะทำให้ VBA ทำงานกับไฟล์ที่เปิดใหม่แทน
2. การเขียน VBA ในแต่ละ Page (sheet1, sheet2, sheet3, This workbook และใน Module) มีความแตกต่างในการทำงานหรือไม่ครับ
3. การ Run VBA ให้ใช้งานแบบอัตโนมัติ เมื่อทำการเปิดไฟล์แล้ว ผมลองเขียนเองแล้ว แต่รู้สึกว่ามันไม่ทำงานน่ะครับ คือ ต้องเข้าไปที่หน้า VBA ก่อนแล้วจึงทำการ Run / Save และจึงออกมา
ตัวโปรแกรมถึงจะเริ่มทำงานให้น่ะครับ
อย่างไรก็ตาม ผมรบกวนอาจารย์ช่วยชี้แนะอีกซักนิดครับ
ไฟล์ที่ได้เขียนประยุกต์เพิ่มเติม ตามไฟล์แนบเลยครับ

ขอบคุณครับ

[snasui]

1. หากเปิดไฟล์ Book1 ที่ทำการเขียน VBA แล้ว ถ้าเปิดไฟล์อื่นๆอีก จะทำให้ VBA ทำงานกับไฟล์ที่เปิดใหม่แทน

อยู่ที่การเขียน Code ครับ หากต้องทำงานกับหลายไฟล์พร้อมกัน ๆ กันและปัจจุบันทำงานกับไฟล์อื่น จะเขียน Code เช่นด้านล่างไม่ได้ครับ

Code: Select all

Sheets("Sheet1").Range("A1").Select
Sheets("Sheet2").Range("A1").Paste

เนื่องจาก Code นี้หมายถึงให้ทำงานกับไฟล์ที่ Active อยู่เสมอ ดังนั้นจะต้องอ้าง File เข้ามาด้วยเช่น

Code: Select all

Workbooks("Book1.xls").Sheets("Sheet1").Range("A1").Copy
Workbooks("Book1.xls").Sheets("Sheet2").Range("A1").Paste

2. การเขียน VBA ในแต่ละ Page (sheet1, sheet2, sheet3, This workbook และใน Module) มีความแตกต่างในการทำงานหรือไม่ครับ

การทำงานใน ThisWorkbook กับ Worksheet ต่าง ๆ ปกติจะใช้เมื่อเกิด Events ของโปรแกรมแล้ว เช่นเมื่อเลือกชีท เมื่อเปลี่ยนค่าในเซลล์เป็นต้น Code ที่จะวางก็ต้องดูว่าต้องการใช้ Events ใด หากวาง Code ผิดที่ Code ก็ไม่ทำงาน จึงตอบคำถามได้ว่าแตกต่างในการทำงานแน่นอนครับ

3. การ Run VBA ให้ใช้งานแบบอัตโนมัติ เมื่อทำการเปิดไฟล์แล้ว ผมลองเขียนเองแล้ว แต่รู้สึกว่ามันไม่ทำงานน่ะครับ คือ ต้องเข้าไปที่หน้า VBA ก่อนแล้วจึงทำการ Run / Save และจึงออกมา

ลองใช้ Code ตามด้านล่างใน ThisWorkbook ครับ

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DisableTimer
End Sub

Private Sub Workbook_Open()
    Timer
End Sub