การสร้างลิสท์ด้วยใช้ข้อมูลจาก REST API ใน Excel

ในการพัฒนาระบบนั้น บ่อยครั้งที่ต้องรองรับการ Import ข้อมูลเข้าระบบเป็น Excel ไฟล์ ซึ่ง Excel File ดังกล่าวมักจะมีข้อมูลพื้นฐานบางส่วนซึ่งหากเราทำเป็นลิสท์ใน Excel ไว้และวันนึงข้อมูลดังกล่าวมีการเปลี่ยนแปลง เรามักจะต้องมาอัพเดทไฟล์ใหม่อยู่เสมอ ๆ

ภาพตัวอย่างลิสท์ข้อมูลพื้นฐานบนไฟล์ Excel

เพราะงั้นวันนี้เราจะลองทำการดึงข้อมูลพื้นฐานเหล่านี้ผ่าน API เพื่อลดปัญหาดังกล่าวกันครับ

เบื้องต้นผมมี API สำหรับลองเรียกที่จะส่งข้อมูลเป็น Array ให้ เตรียมไว้ดังนี้ครับ

ขั้นตอนที่ 1 เริ่มจากสร้างไฟล์ Excel ขึ้นมาและสร้าง Column Employee ไว้ครับ

ขั้นตอนที่ 2 ไปยัง Tab Formula แล้วเลือก Defined Name เพื่อสร้างแหล่งข้อมูล โดยใส่ข้อมูลลงไปตามภาพครับ โดยเราจะใช้ Sheet2!$A:$A เพื่อเก็บข้อมูลสำหรับทำเป็นลิสท์ครับ

ขั้นตอนที่ 3 ทำกการสร้างลิสท์ด้วยคลุม Cells ที่คอลัมน์ A ใน Sheet 1 ทั้งหมด จากนั้นเลือกที่ Data Validation

ขั้นตอนที่ 4 เลือกข้อมูลตามภาพเพื่อสร้าง list

ขั้นตอนที่ 5 วาดปุ่มสำหรับใช้ดึงข้อมูล (หากใครไม่มีแถบ Developer ให้ไปทำการเปิดขึ้นมาก่อนจาก Files => Options => Customize Ribbon นะครับ)

ขั้นตอนที่ 6 กด Assign macro => New เพื่อทำการสร้างฟังก์ชันให้ปุ่มทำงานเพื่อไปเรียกข้อมูลจาก API

ขั้นตอนที่ 7 Download Lib สำหรับแปลงข้อมูล Json จาก https://github.com/VBA-tools/VBA-JSON จากนั้นในหน้าต่าง Macro ที่เปิดมาจากขั้นตอน 6 ให้กด File => Import File และเลือกไฟล์ JsonConverter.bas เพื่อ Import Lib สำหรับใช้งาน

ขั้นตอนที่ 8 จากนั้นในหน้าต่าง Macro กดที่ Tools References และเลือก Microsoft Scripting Runtime

ขั้นตอนที่ 9 ทำการพิมพ์โค้ดดังนี้ (Button2_Click ในที่นี้คือฟังก์ชันที่เกิดจากตอนกด Assign Macro ครับ)

เราจะใช้ MSXML2.ServerXMLHTTP ในการสร้าง Request ไปยัง API ที่มีการเตรียมไว้ http://localhost:56618/api/values จากนั้นใช้ JsonConverter สำหรับอ่าน Json ครับ

ขั้นตอนที่ 10 ทำการทดสอบโดยกดปุ่มที่สร้างขึ้นจะพบว่าข้อมูลจะถูก write ไว้ใน Sheet ที่ 2 และทำให้ข้อมูลแสดงในลิสท์ที่ผูกไว้เรียบร้อยครับ

สำหรับท่านใดที่อาจนำไปใช้ในการ Post ข้อมูลไปบันทึกที่ API เลยก็สามารถนำวิธีการเรียก API ไปประยุกต์ได้ครับ ทั้งนี้อาจต้องมีการเพิ่มเติมเรื่องการทำ Authentication เพิ่มเติมไปด้วยครับ

References:

https://stackoverflow.com/questions/48747859/xhr-request-response-text-has-unexpected-character-set
https://stackoverflow.com/questions/11242399/blank-values-in-excel-data-validation-drop-down-list
https://www.contextures.com/xldataval01.html
https://github.com/VBA-tools/VBA-JSON