เนื้อหา
กรณีที่พบบ่อยมากในทางปฏิบัติ: คุณต้องเรียกใช้มาโครของคุณอย่างน้อยหนึ่งรายการในเวลาที่กำหนดหรือที่ความถี่ที่แน่นอน ตัวอย่างเช่น คุณมีรายงานขนาดใหญ่และหนักที่อัปเดตครึ่งชั่วโมง และคุณต้องการเรียกใช้การอัปเดตครึ่งชั่วโมงก่อนมาถึงที่ทำงานในตอนเช้า หรือคุณมีมาโครที่ควรส่งอีเมลถึงพนักงานโดยอัตโนมัติตามความถี่ที่กำหนด หรือเมื่อทำงานกับ PivotTable คุณต้องการให้อัปเดตทันทีทุกๆ 10 วินาที และอื่นๆ
มาดูกันว่า Excel และ Windows มีความสามารถใดบ้างในการดำเนินการนี้
เรียกใช้แมโครที่ความถี่ที่กำหนด
วิธีที่ง่ายที่สุดในการทำเช่นนี้คือการใช้วิธี VBA ในตัว แอปพลิเคชัน OnTimeที่เรียกใช้แมโครที่ระบุในเวลาที่กำหนด มาทำความเข้าใจสิ่งนี้ด้วยตัวอย่างที่ใช้งานได้จริง
เปิดตัวแก้ไข Visual Basic ด้วยปุ่มชื่อเดียวกันบนแท็บ ผู้พัฒนา (ผู้พัฒนา) หรือแป้นพิมพ์ลัด อื่น ๆ+F11, แทรกโมดูลใหม่ผ่านเมนู แทรก – โมดูล และคัดลอกรหัสต่อไปนี้ที่นั่น:
Dim TimeToRun 'ตัวแปรทั่วโลกที่เก็บเวลาทำงานครั้งต่อไป' นี่คือมาโครหลัก Sub MyMacro() Application.Calculate 'คำนวณใหม่ช่วงหนังสือ ("A1").Interior.ColorIndex = Int(Rnd() * 56) 'เติม เซลล์ A1 ที่มีสีสุ่ม :) เรียก NextRun 'เรียกใช้แมโคร NextRun เพื่อตั้งเวลารันถัดไป End Sub 'มาโครนี้กำหนดเวลาสำหรับการรันแมโครหลักถัดไป Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'เพิ่ม 3 วินาทีให้กับเวลาปัจจุบัน Application.OnTime TimeToRun, "MyMacro" 'กำหนดเวลาการรันถัดไป End Sub 'มาโครเพื่อเริ่มลำดับการทำซ้ำ Sub Start() โทร NextRun End Sub 'มาโครเพื่อหยุดลำดับการทำซ้ำ เสร็จสิ้นย่อย () Application.OnTime TimeToRun, "MyMacro", , False End Sub
มาดูกันว่าที่นี่มีอะไรบ้าง
อันดับแรก เราต้องการตัวแปรที่จะเก็บเวลาของการรันมาโครครั้งต่อไป - ฉันเรียกมันว่า ไทม์ทูรัน. โปรดทราบว่าเนื้อหาของตัวแปรนี้จะต้องใช้ได้กับมาโครที่ตามมาทั้งหมดของเรา ดังนั้นเราจึงจำเป็นต้องสร้างมัน ทั่วโลกกล่าวคือประกาศที่จุดเริ่มต้นของโมดูลก่อนครั้งแรก ต.
ถัดมาเป็นมาโครหลักของเรา มายมาโครซึ่งจะทำหน้าที่หลัก – คำนวณหนังสือใหม่โดยใช้วิธี แอพพลิเคชั่นคำนวณ. เพื่อให้ชัดเจนขึ้น ฉันได้เพิ่มสูตร =TDATE() ลงในชีตในเซลล์ A1 ซึ่งแสดงวันที่และเวลา – เมื่อคำนวณใหม่ เนื้อหาจะได้รับการอัปเดตต่อหน้าต่อตาเรา (เพียงแค่เปิดการแสดงวินาทีในเซลล์ รูปแบบ). เพื่อความสนุกสนานเป็นพิเศษ ฉันยังเพิ่มคำสั่งมาโครเพื่อเติมเซลล์ A1 ด้วยสีที่เลือกแบบสุ่ม (รหัสสีเป็นจำนวนเต็มในช่วง 0..56 ซึ่งสร้างโดยฟังก์ชัน อาร์เอ็น และปัดเศษขึ้นเป็นฟังก์ชันจำนวนเต็ม Int).
แมโคร วิ่งต่อไป บวกกับค่าเดิม ไทม์ทูรัน อีก 3 วินาทีแล้วกำหนดเวลาการรันแมโครหลักครั้งต่อไป มายมาโคร สำหรับครั้งใหม่นี้ แน่นอน ในทางปฏิบัติ คุณสามารถใช้ช่วงเวลาอื่นๆ ที่ต้องการได้โดยการตั้งค่าอาร์กิวเมนต์ของฟังก์ชัน ค่าเวลา ในรูปแบบ hh:mm:ss.
และสุดท้าย เพื่อความสะดวก มีการเพิ่มมาโครการเรียกใช้ตามลำดับเพิ่มเติม หน้าแรก และแล้วเสร็จ เสร็จสิ้น. อันสุดท้ายใช้อาร์กิวเมนต์วิธีที่สี่เพื่อแยกลำดับ ตามเวลา เท่ากัน เท็จ.
รวมถ้าคุณเรียกใช้แมโคร หน้าแรกจากนั้นม้าหมุนทั้งหมดนี้จะหมุน และเราจะเห็นภาพต่อไปนี้บนแผ่นงาน:
คุณสามารถหยุดลำดับได้โดยการเรียกใช้แมโครตามลำดับ เสร็จสิ้น. เพื่อความสะดวก คุณสามารถกำหนดแป้นพิมพ์ลัดให้กับมาโครทั้งสองโดยใช้คำสั่ง มาโคร – ตัวเลือก แถบ ผู้พัฒนา (ผู้พัฒนา — มาโคร — ตัวเลือก).
เรียกใช้แมโครตามกำหนดเวลา
แน่นอน ทุกอย่างที่อธิบายข้างต้นจะเป็นไปได้ก็ต่อเมื่อคุณมี Microsoft Excel ทำงานอยู่และไฟล์ของเราเปิดอยู่ ตอนนี้ มาดูกรณีที่ซับซ้อนมากขึ้น: คุณต้องเรียกใช้ Excel ตามกำหนดการที่กำหนด เช่น ทุกวันเวลา 5:00 น. เปิดรายงานขนาดใหญ่และซับซ้อนในนั้น และอัปเดตการเชื่อมต่อและคิวรีทั้งหมดในนั้น ให้พร้อมเมื่อถึงที่ทำงาน 🙂
ในสถานการณ์เช่นนี้ ควรใช้ ตัวกำหนดเวลา Windows – โปรแกรมที่สร้างขึ้นเป็นพิเศษใน Windows รุ่นใดก็ได้ที่สามารถดำเนินการตามกำหนดเวลาได้ ที่จริงแล้ว คุณกำลังใช้งานอยู่แล้วโดยที่คุณไม่รู้ตัว เพราะพีซีของคุณตรวจหาการอัปเดตเป็นประจำ ดาวน์โหลดฐานข้อมูลต่อต้านไวรัสใหม่ ซิงโครไนซ์โฟลเดอร์บนคลาวด์ ฯลฯ ทั้งหมดนี้เป็นงานของเครื่องมือจัดกำหนดการ ดังนั้นงานของเราคือเพิ่มงานที่มีอยู่อีกงานหนึ่งที่จะเปิด Excel และเปิดไฟล์ที่ระบุในนั้น และเราจะแขวนแมโครของเราไว้ที่งาน สมุดงาน_เปิด ไฟล์นี้ – และปัญหาได้รับการแก้ไขแล้ว
ฉันต้องการเตือนคุณทันทีว่าการทำงานกับตัวจัดกำหนดการอาจต้องใช้สิทธิ์ของผู้ใช้ขั้นสูง ดังนั้น หากคุณไม่พบคำสั่งและฟังก์ชันที่อธิบายไว้ด้านล่างบนคอมพิวเตอร์ที่ทำงานของคุณในสำนักงาน โปรดติดต่อผู้เชี่ยวชาญด้านไอทีของคุณเพื่อขอความช่วยเหลือ
เปิดตัวกำหนดการ
มาเริ่มตัวจัดกำหนดการกันเถอะ ในการทำเช่นนี้ คุณสามารถ:
- คลิกขวาที่ปุ่ม เริ่มต้น และเลือก การจัดการคอมพิวเตอร์ (การจัดการคอมพิวเตอร์)
- เลือกในแผงควบคุม: การบริหาร – Task Scheduler (แผงควบคุม — เครื่องมือการดูแลระบบ — ตัวกำหนดเวลางาน)
- เลือกจากเมนูหลัก เริ่ม – อุปกรณ์เสริม – เครื่องมือระบบ – ตัวกำหนดเวลางาน
- กดแป้นพิมพ์ลัด ชนะ+Rป้อน taskschd.msc และกด เข้าสู่
The following window should appear on the screen (I have an English version, but you can also have a version):
สร้างงาน
ในการสร้างงานใหม่โดยใช้วิซาร์ดทีละขั้นตอนง่ายๆ ให้คลิกที่ลิงก์ สร้างงานง่ายๆ (สร้างงานพื้นฐาน) ในแผงด้านขวา
ในขั้นตอนแรกของวิซาร์ด ให้ป้อนชื่อและคำอธิบายของงานที่จะสร้าง:
คลิกที่ปุ่ม ถัดไป (ต่อไป) และในขั้นตอนต่อไป เราเลือกทริกเกอร์ – ความถี่การเปิดตัวหรือเหตุการณ์ที่จะเริ่มต้นงานของเรา (เช่น การเปิดคอมพิวเตอร์):
ถ้าคุณเลือก ทุกวัน (รายวัน)จากนั้นในขั้นตอนต่อไป คุณจะต้องเลือกเวลาที่ต้องการ วันที่เริ่มต้นของลำดับ และขั้นตอน (ทุกวันที่ 2, วันที่ 5 เป็นต้น):
ขั้นตอนต่อไปคือการเลือกการกระทำ – เรียกใช้โปรแกรม (เริ่มโปรแกรม):
และสุดท้าย สิ่งที่น่าสนใจที่สุดคือต้องเปิดอะไรกันแน่:
ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร โปรแกรมหรือสคริปต์ (โปรแกรม/สคริปต์) คุณต้องป้อนเส้นทางไปยัง Microsoft Excel เป็นโปรแกรม กล่าวคือ ไปยังโปรแกรมปฏิบัติการ Excel โดยตรง ในคอมพิวเตอร์เครื่องต่างๆ ที่มี Windows และ Office เวอร์ชันต่างๆ กัน ไฟล์นี้อาจอยู่ในโฟลเดอร์ต่างๆ กัน ดังนั้นต่อไปนี้คือวิธีสองสามวิธีในการค้นหาตำแหน่งของไฟล์:
- คลิกขวาที่ไอคอน (ทางลัด) เพื่อเปิด Excel บนเดสก์ท็อปหรือในแถบงาน แล้วเลือกคำสั่ง วัสดุ (คุณสมบัติ)จากนั้นในหน้าต่างที่เปิดขึ้น ให้คัดลอกเส้นทางจากบรรทัด เป้า:
- เปิดเวิร์กบุ๊ก Excel จากนั้นเปิด ที่ Task Manager (ผู้จัดการงาน) ใจเร่งเร้า Ctrl+อื่น ๆ+จาก และโดยการคลิกขวาที่เส้น Microsoft Excel, เลือกคำสั่ง วัสดุ (คุณสมบัติ). ในหน้าต่างที่เปิดขึ้น คุณสามารถคัดลอกเส้นทาง อย่าลืมเพิ่มแบ็กสแลชและ EXCEL.EXE ในตอนท้าย:
- เปิด Excel เปิดโปรแกรมแก้ไข Visual Basic ด้วยแป้นพิมพ์ลัด อื่น ๆ+F11, เปิดแผง ทันที การรวมกันของ Ctrl+Gให้ป้อนคำสั่งลงไป:
? Application.Path
… และคลิกที่ เข้าสู่
คัดลอกเส้นทางผลลัพธ์ อย่าลืมเพิ่มแบ็กสแลชและ EXCEL.EXE ในตอนท้าย.
ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร เพิ่มอาร์กิวเมนต์ (ตัวเลือก) (เพิ่มอาร์กิวเมนต์ (ไม่บังคับ)) คุณต้องแทรกเส้นทางแบบเต็มไปยังหนังสือด้วยมาโครที่เราต้องการเปิด
เมื่อใส่ครบแล้วให้กด ถัดไป แล้วก็ เสร็จสิ้น (เสร็จสิ้น). ควรเพิ่มงานในรายการทั่วไป:
สะดวกในการจัดการงานที่สร้างขึ้นโดยใช้ปุ่มทางด้านขวา ที่นี่คุณสามารถทดสอบงานโดยเรียกใช้ทันที (วิ่ง)โดยไม่ต้องรอเวลาที่กำหนด คุณสามารถปิดการใช้งานงานชั่วคราว (ปิดการใช้งาน)เพื่อให้หยุดวิ่งไปช่วงหนึ่ง เช่น วันหยุดของคุณ คุณสามารถเปลี่ยนพารามิเตอร์ (วันที่ เวลา ชื่อไฟล์) ผ่านปุ่มได้ตลอดเวลา วัสดุ (คุณสมบัติ).
เพิ่มมาโครเพื่อเปิดไฟล์
ตอนนี้ยังคงค้างอยู่ในหนังสือของเราเกี่ยวกับการเปิดตัวมาโครที่เราต้องการในเหตุการณ์เปิดไฟล์ ในการดำเนินการนี้ ให้เปิดหนังสือและไปที่ตัวแก้ไข Visual Basic โดยใช้แป้นพิมพ์ลัด อื่น ๆ+F11 หรือปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา). ในหน้าต่างที่เปิดขึ้นที่มุมบนซ้าย คุณต้องค้นหาไฟล์ของเราบนแผนผังและดับเบิลคลิกเพื่อเปิดโมดูล หนังสือเล่มนี้ (สมุดงานนี้).
หากคุณไม่เห็นหน้าต่างนี้ในตัวแก้ไข Visual Basic คุณสามารถเปิดหน้าต่างนี้ผ่านเมนู ดู — Project Explorer.
ในหน้าต่างโมดูลที่เปิดขึ้น ให้เพิ่มตัวจัดการเหตุการณ์ที่เปิดหนังสือโดยเลือกจากรายการดรอปดาวน์ที่ด้านบน สมุดงาน и จุดเปิดตามลำดับ:
เทมเพลตขั้นตอนควรปรากฏบนหน้าจอ สมุดงาน_เปิดที่ไหนระหว่างบรรทัด ย่อยส่วนตัว и ย่อยสิ้นสุด และคุณต้องแทรกคำสั่ง VBA ที่ควรดำเนินการโดยอัตโนมัติเมื่อเปิดเวิร์กบุ๊ก Excel นี้ เมื่อตัวจัดกำหนดการเปิดขึ้นตามกำหนดการ ต่อไปนี้คือตัวเลือกที่มีประโยชน์สำหรับการโอเวอร์คล็อก:
- เวิร์กบุ๊กนี้รีเฟรชทั้งหมด – รีเฟรชคิวรีข้อมูลภายนอก คิวรี Power Query และ PivotTables ทั้งหมด ตัวเลือกที่หลากหลายที่สุด อย่าลืมอนุญาตการเชื่อมต่อกับข้อมูลภายนอกโดยค่าเริ่มต้นและอัปเดตลิงก์ผ่าน ไฟล์ – ตัวเลือก – ศูนย์ความเชื่อถือ – ตัวเลือกศูนย์ความเชื่อถือ – เนื้อหาภายนอกมิฉะนั้นเมื่อคุณเปิดหนังสือคำเตือนมาตรฐานจะปรากฏขึ้นและ Excel โดยไม่ต้องอัปเดตอะไรเลยจะรอคำอวยพรของคุณในรูปแบบของการคลิกที่ปุ่ม เปิดใช้งานเนื้อหา (เปิดใช้งานเนื้อหา):
- ActiveWorkbook.Connections(“Connection_Name”).รีเฟรช — อัปเดตข้อมูลในการเชื่อมต่อ Connection_Name
- แผ่นงาน(“แผ่นที่5“).PivotTable(“PivotTable1«).PivotCache.รีเฟรช – อัปเดตตารางเดือยเดี่ยวชื่อ PivotTable 1 บนแผ่น Sheet5.
- แอพพลิเคชั่นคำนวณ – การคำนวณใหม่ของสมุดงาน Excel ที่เปิดอยู่ทั้งหมด
- แอปพลิเคชัน CalculateFullRebuild – บังคับให้คำนวณสูตรใหม่ทั้งหมดและสร้างการพึ่งพาทั้งหมดระหว่างเซลล์ในเวิร์กบุ๊กที่เปิดอยู่ทั้งหมด (เทียบเท่ากับการป้อนสูตรทั้งหมดอีกครั้ง)
- แผ่นงาน(“รายงาน”).PrintOut – แผ่นงานพิมพ์ ภาพถ่าย.
- โทร MyMacro – เรียกใช้แมโครชื่อ มายมาโคร.
- สมุดงานนี้บันทึก – บันทึกเล่มปัจจุบัน
- ThisWorkbooks.SaveAs “D:ArchiveReport” & แทนที่ (ตอนนี้ “:”, “-“) & “.xlsx” – บันทึกหนังสือลงในโฟลเดอร์ D:เก็บถาวร ภายใต้ชื่อ ภาพถ่าย พร้อมวันที่และเวลาต่อท้ายชื่อ
หากคุณต้องการให้แมโครทำงานเฉพาะเมื่อไฟล์ถูกเปิดโดยตัวจัดกำหนดการเวลา 5:00 น. และไม่ใช่ทุกครั้งที่ผู้ใช้เปิดเวิร์กบุ๊กในวันทำการ คุณควรเพิ่มการตรวจสอบเวลา เช่น
ถ้า Format(ตอนนี้ "hh:mm") = "05:00" แล้ว ThisWorkbook.RefreshAll
นั่นคือทั้งหมดที่ อย่าลืมบันทึกเวิร์กบุ๊กของคุณในรูปแบบที่เปิดใช้งานมาโคร (xlsm หรือ xlsb) และคุณสามารถปิด Excel และกลับบ้านได้อย่างปลอดภัย โดยเปิดคอมพิวเตอร์ทิ้งไว้ ในช่วงเวลาที่กำหนด (แม้ว่าพีซีจะถูกล็อค) ตัวจัดกำหนดการจะเปิด Excel และเปิดไฟล์ที่ระบุในนั้น และมาโครของเราจะดำเนินการตามโปรแกรม และคุณจะนอนอย่างมีความสุขในขณะที่รายงานหนัก ๆ ของคุณจะถูกคำนวณใหม่โดยอัตโนมัติ – สวยงาม! 🙂
- มาโครคืออะไร, ใช้งานอย่างไร, ตำแหน่งที่จะแทรกโค้ด Visual Basic ใน Excel
- วิธีสร้างมาโคร add-in ของคุณเองสำหรับ Excel
- วิธีใช้ Personal Macro Workbook เป็นไลบรารีสำหรับมาโครของคุณใน Excel