Thep Excel

มีคนถามผมมาเยอะมาก เกี่ยวกับวิธีที่จะทำให้ เปิด Excel อัตโนมัติ พร้อมรีเฟรช Power Query เพื่อดึงข้อมูลอัตโนมัติแบบไม่ต้องเปิดไฟล์มาคลิกเอง แต่ไม้รู้จะเริ่มยังไงดี

บทความนี้จะพาไปดู “สารพัดวิธี” ที่ทำให้คอมฯ ของเราช่วยเปิดไฟล์ Excel และรีเฟรช Query อัตโนมัติ ตั้งแต่วิธีเบสิกสุดจนถึงขั้นเทพ อย่างไรก็ตาม ผมจะเน้นเฉพาะวิธีที่ไฟล์ยังบันทึกเป็น xlsx ธรรมดาได้นะครับ แปลว่าเราจะไม่มีการสร้าง Macro/VBA ในไฟล์ Excel เด็ดขาด

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

ป.ล. ก่อนหน้านี้ผมเคยเขียนบทความเพื่อที่จะทำงาน Excel แบบ Automate 100% ไปแล้วก็จริง แต่คราวนี้ผมจะมาเจาะลึกขั้นตอนของการเปิดไฟล์ Excel ขึ้นมาแล้ว Refresh อัตโนมัติ ว่ามีวิธีไหนบ้างที่พอจะช่วยได้ จะได้เห็นทางเลือกที่หลากหลายมากขึ้นครับ

ไฟล์ Excel ตัวอย่าง

ลองสร้าง Excel ที่มี Query ที่เมื่อได้รับการ Refresh แล้วผลลัพธ์เปลี่ยนทันที (จะได้เห็นภาพ)

ลองสร้าง Blank Query โดย Get Data->From Other Source -> Blank Query

แล้วกดเข้า Home หรือ View -> Advanced Editor แล้วใส่ M Code แบบนี้เข้าไป (ลบอันเดิมออกให้หมดก่อน)

Number.RandomBetween(1,100)

ตั้งชื่อ Query ว่า MyRandom ก็ได้

กด Close & Load จากนั้นเซฟไฟล์ Excel เอาไว้ที่ D:\Data\SalesReport.xlsx

ซึ่งถ้าเราลอง Refresh Query แล้วค่าที่ได้จะเปลี่ยนไปเรื่อยๆ

วิธีบังคับปิดไฟล์ Excel ที่ค้าง

ขอบอกเผื่อไว้ ถ้าใครเปิด Excel ด้วยวิธีใดๆ ข้างล่าง แล้วมันค้างไม่สามารถปิดได้… ให้ Run -> PowerShell with administrator แล้วใส่คำสั่งนี้เพื่อ Kill Instance Excel ทั้งหมด

จากนั้นใส่คำสั่งว่า

Stop-Process -Name EXCEL -Force

วิธี 1 : ตั้งค่า Power Query ให้รีเฟรชอัตโนมัติเมื่อเปิดไฟล์ แล้วผูกกับ Batch File

ขั้นตอน:

• เปิดไฟล์ Excel (.xlsx) ที่มี Power Query เช่น D:\Data\SalesReport.xlsx
• คลิ๊กขวาที่ Query ที่ต้องการในแทบ Data -> Queries & Connection
• คลิก Properties แล้วติ๊กตัวเลือก Refresh data when opening the file
• บันทึกไฟล์ทับอันเดิมได้เลย

แค่นี้เวลาเราเปิดไฟล์ Excel ขึ้นมา ตัว Query นั้นๆ ก็จะถูก Refresh อัตโนมัติแล้ว

ซึ่งเหมาะกับกรณีที่เราตั้งใจจะเปิดไฟล์ Excel ขึ้นมาเอง หรือ วิธีใดๆ ก็ตามที่จะมาสั่งไฟล์ Excel ให้เปิดขึ้นมาเฉยๆ แล้วปล่อยให้ Excel ทำงานต่อเอง

ใช้ Batch File

วิธีนี้ต้องตั้งค่า Query ใน Excel ให้รีเฟรชอัตโนมัติเมื่อเปิดไฟล์ก่อน จากนั้นใช้ Text Editor เช่น Notepad สร้างไฟล์ .bat เช่น D:\Scripts\RefreshSalesReport.bat แล้วใส่ code นี้เข้าไปแล้ว save ด้วย

start "" "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "D:\Data\SalesReport.xlsx"

ผูก Batch กับ Task Scheduler

1. Search คำว่า Task -> เปิดโปรแกรม Task Scheduler > Action -> Create Basic Task…
2. ตั้งชื่อเช่น Refresh Sales Report
3. Trigger → Daily -> เลือกเวลาที่ต้องการ (เช่น Daily 08:00)
4. Action → “Start a program”

Program/script:

D:\Scripts\RefreshSalesReport.bat

กด Finish

หากเราใช้วิธีนี้ Excel จะถูดเปิดขึ้นมาในเวลาที่กำหนด แล้ว รีเฟรช Query อัตโนมัติ แต่จะไม่ได้บันทึกไฟล์หรือปิดเองนะ

วิธี 2 : ใช้ Power Automate Desktop (ฟรี)

ให้สร้าง Flow ใหม่แล้วกำหนดดังนี้ได้เลย (แต่ละขั้นตอน search ได้)

1. Launch Excel

กำหนด Action: Launch Excel แบบ and opening the following documents โดยไม่ต้อง Visible ก็ได้ (จะทำงานเร็วกว่า) ดังรูป

Output: ใช้ตัวแปร ExcelInstance

2. Wait (ใน Flow Control)

• Duration: 60 วินาที (ปรับตามความจำเป็น)

3. Save Excel

• Excel instance: %ExcelInstance%
• Save mode: Save document

4. Close Excel

• Excel instance: %ExcelInstance%
• Before closing Excel: Do not save document

เราต้องสั่ง Run Workflow นี้ มันถึงจะทำการเปิด Excel ขึ้นมาโดยอัตโนมัติ

แต่ถ้าเราอยากจะ Run Flow นี้แบบอัตโนมัติในเวลาที่กำหนด เราสามารถใช้ Power Automate Cloud Flow มา Trigger ได้อีกที แต่ต้องมี License พิเศษนะ หึหึ 😎

จริงๆ ทำผ่าน Task Scheduler ได้นะ แต่ว่ามันก็ต้องมี License พิเศษอยู่ดี ถึงจะใช้ Run URL ได้

ดังนั้นถ้าใครไม่มี License ก็ไปวิธีข้างล่างดีกว่า

วิธี 3 : ใช้ VBScript ( .vbs ) + Task Scheduler

ตั้งแต่วิธีนี้เป็นต้นไป ใน File Excel ไม่จำเป็นต้องไปติ๊ก Property ที่ Query ว่า Refresh data when opening the file อีกต่อไปแล้ว นะ เพราะเราสั่งจากภายนอกได้เลย

เปิด Notepad แล้วใส่ Code ว่า

On Error Resume Next
Dim xl : Set xl = CreateObject("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
xl.AskToUpdateLinks = False

Set wb = xl.Workbooks.Open("D:\Data\SalesReport.xlsx")
wb.RefreshAll
WScript.Sleep 60000   ' รอ query/refresh 60 วิ
wb.Save
wb.Close False
xl.Quit

Set wb = Nothing
Set xl = Nothing

เซฟไฟล์เป็น RefreshExcel.vbs ไว้ในที่ที่กำหนด เช่น D:\Scripts\

สร้าง Task ใน Windows Task Scheduler

Program:

wscript.exe

Arguments:

"D:\Scripts\RefreshExcel.vbs"

วิธีนี้ไฟล์จะถูกเปิดเอง Refresh เอง แล้ว Save จากนั้นปิดเอง แต่อาจต้องเผื่อเวลา Query ให้มันรันให้จบ เช่น ใน Code นี้รอ 60 วินาที

วิธี 4 : ใช้ PowerShell (ยืดหยุ่นกว่า VBS)

เปิด Notepad แล้วใส่ Code ว่า

# ===================================================================
#                      CONFIGURABLE VARIABLES
# ===================================================================
# Path to your Excel file (can be .xlsx)
$excelFilePath = "D:\Data\SalesReport.xlsx" # แก้ไขเป็น path ไฟล์ .xlsx ของคุณ

# Maximum time to wait in seconds for the refresh to complete
$timeoutSeconds = 600 # 10 นาที (ปรับตามความเหมาะสม)
# ===================================================================

# Create an Excel COM object
$excel = $null
$workbook = $null

try {
    Write-Host "Starting Excel automation..."
    $excel = New-Object -ComObject Excel.Application

    # Make Excel invisible (runs in the background)
    $excel.Visible = $false
    # Or make it visible for debugging:
    # $excel.Visible = $true

    # Disable alerts (e.g., save prompts)
    $excel.DisplayAlerts = $false

    # Open the workbook
    Write-Host "Opening workbook: $excelFilePath"
    $workbook = $excel.Workbooks.Open($excelFilePath)

    # Refresh all data connections
    Write-Host "Starting RefreshAll command..."
    $workbook.RefreshAll()

    # --- Robust Wait Loop (Replaces Start-Sleep) ---
    Write-Host "Waiting for queries to complete..."
    $stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
    $isRefreshing = $true
    while ($isRefreshing) {
        # Check if timeout has been reached
        if ($stopwatch.Elapsed.TotalSeconds -ge $timeoutSeconds) {
            throw "Timeout: Queries did not complete within $timeoutSeconds seconds."
        }
        
        # Assume refresh is done, unless we find one that is still running
        $isRefreshing = $false 

        # Loop through all connections to check their status
        foreach ($connection in $workbook.Connections) {
            if ($connection.Refreshing) {
                $isRefreshing = $true
                break # Found a refreshing connection, no need to check others in this loop
            }
        }
        
        # Wait a moment before checking again to avoid pegging the CPU
        Start-Sleep -Seconds 1 
    }
    $stopwatch.Stop()
    Write-Host ("Queries completed in {0:N2} seconds." -f $stopwatch.Elapsed.TotalSeconds)
    # --- End of Wait Loop ---

    # Save the workbook (important to keep the refreshed data)
    Write-Host "Saving workbook..."
    $workbook.Save()

    Write-Host "Closing workbook..."
    $workbook.Close()

    Write-Host "Quitting Excel application..."
    $excel.Quit()

}
catch {
    # If any error occurs, log it
    Write-Error "An error occurred: $($_.Exception.Message)"
}
finally {
    # This block ALWAYS runs, ensuring Excel is closed and memory is released
    # even if an error occurred. This prevents lingering Excel processes.
    if ($workbook -ne $null) { [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null }
    if ($excel -ne $null) { [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null }
    Remove-Variable excel, workbook -ErrorAction SilentlyContinue
    Write-Host "Cleanup complete. Excel process should be terminated."
}

Write-Host "Process finished."

เซฟไฟล์เป็น ไว้ในที่ที่กำหนด เช่น D:\Scripts\RefreshExcel.ps1

สร้าง Task ใน Windows Task Scheduler

Program:

powershell.exe

Arguments:

-ExecutionPolicy Bypass -File "D:\Scripts\RefreshExcel.ps1"

วิธีใช้ PowerShell จะมีความยืดหยุ่นกว่า VBS และเป็นภาษาสมัยใหม่กว่าด้วยครับ อันนี้ถ้า Query ทำงานเสร็จก็จะปิดตัวเองอย่างรวดเร็ว ไม่ต้องมี Wait เผื่อด้วย

วิธี 5 : ใช้ Python Script

ติดตั้ง Python และแพ็กเกจ pywin32:

โดยเข้า cmd / powershell แล้วรัน

pip install pywin32

สร้างสคริปต์ Python เพื่อเปิด Excel, รีเฟรช Query และบันทึกไฟล์ excel:

import win32com.client
import time

WAIT_SECONDS = 60  # <<< ปรับตรงนี้ได้เลย จะรอกี่วิ

try:
    print("Starting Excel refresh process...")
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False
    print("Excel application started.")

    wb = excel.Workbooks.Open(r"D:\Data\SalesReport.xlsx")
    print("Workbook opened.")

    wb.RefreshAll()
    print(f"RefreshAll called. Waiting {WAIT_SECONDS} sec for refresh to complete.")
    time.sleep(WAIT_SECONDS)

    wb.Save()
    print("Workbook saved.")

    wb.Close()
    print("Workbook closed.")

    excel.Quit()
    print("Excel application quit.")

except Exception as e:
    print(f"ERROR: {e}")

บันทึกสคริปต์เป็นไฟล์ .py (เช่น D:\Scripts\RefreshExcel.py) ใช้ Task Scheduler เพื่อรันสคริปต์อัตโนมัติ:

• ใน Actions, เลือก “Start a program”
• Program: python.exe
• Add arguments : D:\Scripts\RefreshExcel.py
• ตั้งค่า Triggers ตามเวลาที่ต้องการ

ทดสอบเพื่อให้แน่ใจว่าไฟล์ .xlsx ถูกเปิด, รีเฟรช และบันทึก

วิธี 6 : รอติดตามอัปเดทต่อไป…

ยังไม่หมดแค่นี้! สำหรับใครที่อยากรู้วิธีอื่นอีก เช่น ใช้ Power Automate Cloud หรือ ใช้ n8n มา Trigger รออัปเดทต่อไปได้นะครับ พอดีไม่มีเวลาเขียนละ 55