Vào cuối năm ngoái, hai cải tiến mới, khá nhỏ, đã được thực hiện đối với sự hỗ trợ của Excel VBA cho Power Query. giờ đây bạn có thể làm mới và xóa các truy vấn Power Query riêng lẻ. Những phương pháp này hiện có sẵn cho mọi người sử dụng bản dựng Người dùng nội bộ của Excel. Bản thân nó không đặc biệt thú vị [tôi đã viết một bài cách đây vài năm về Excel VBA và Power Query nếu bạn quan tâm] nhưng nó khiến tôi suy nghĩ lại về chủ đề này. Ngoài ra, vào cuối năm ngoái, tôi đã nâng cấp lên Windows 11 tích hợp sẵn Power Automate cho máy tính để bàn. Power Automate dành cho máy tính để bàn giúp tự động hóa các tác vụ hàng ngày cực kỳ dễ dàng và hỗ trợ tuyệt vời cho Excel – bao gồm khả năng chạy macro VBA của Excel. Vì vậy, tôi tự hỏi. bạn có thể sử dụng Power Automate cho máy tính để tự động làm mới các truy vấn Excel Power Query của mình không?
Đây là một ví dụ đơn giản. Tôi đã tạo một truy vấn Power Query có tên là GetTheDateAndTime trả về ngày và giờ hiện tại trong một bảng có một hàng và một cột. Đây là mã M cho truy vấn
#table[type table [RunDate=datetime], {{DateTime.FixedLocalNow[]}}]
Tiếp theo, tôi đã tạo một macro VBA có tên là RunPQQuery để làm mới truy vấn Power Query này bằng phương pháp làm mới mới mà tôi đã đề cập trước đó
Sub RunPQQuery[]
ActiveWorkbook.Queries["GetTheDateAndTime"].Refresh
End Sub
Sau đó, tôi đã lưu sổ làm việc Excel dưới dạng. tập tin xlsm
Tiếp theo, tôi đã mở Power Automate cho máy tính để bàn và tạo một dòng máy tính để bàn mới theo hướng dẫn tại đây để mở Excel, chạy macro và đóng lại Excel. Tôi nhận thấy rằng nếu tôi đóng Excel ngay sau khi chạy macro thì nó sẽ đóng Excel trước khi truy vấn Power Query kết thúc, vì vậy tôi đã thêm độ trễ mười giây sau khi chạy macro để có thời gian hoàn thành. Có lẽ có nhiều cách phức tạp hơn để giải quyết vấn đề này. ví dụ: bạn có thể đọc giá trị của một ô trong bảng được trả về bởi truy vấn mà bạn biết sẽ thay đổi, sau đó chạy vòng lặp truy vấn cho đến khi giá trị bạn đã đọc thay đổi. Đây là dòng máy tính để bàn của tôi
Thông thường, người dùng cần tự động làm mới các tệp Excel có chứa các hàm dễ bay hơi hoặc Kết nối truy vấn. Chỉ Macro VBA mới tự động làm mới tệp Excel mà không cần mở. Kết nối truy vấn để tự động làm mới không hỗ trợ tự động làm mới cho các tệp đã đóng. Thuộc tính kết nối truy vấn chỉ có thể tự động làm mới dữ liệu của tệp Excel đã mở sau một khoảng thời gian đã đặt cụ thể. Quá trình này là một tệp Excel xlsm khác chạy macro để tự động làm mới tệp Excel đã đóng được chỉ định. Trong bài viết này, chúng tôi trình bày ứng dụng của hai macro để tự động làm mới tệp Excel mà không cần mở tệp
Mục lục
Tải xuống Sổ làm việc Excel
Tệp Macro Excel của trình trợ giúp chạy macro để tự động làm mới các tệp hoặc tệp mục tiêu
Tự động làm mới. xlsm
2 cách dễ dàng để tự động làm mới tệp Excel mà không cần mở
Một tệp Excel khác chạy macro mở và đóng tệp Excel được chỉ định để tự động làm mới bằng cách mở. Vì tệp Excel “Đếm ngược” chứa các hàm được cập nhật khi mở
Xem qua phần dưới đây để có thể tự động làm mới tệp Excel mà không cần mở chúng
Phương pháp 1. Sử dụng VBA Macro để tự động làm mới một tệp Excel cụ thể mà không cần mở
File Countdown Excel có các hàm TODAY[] và NOW[] để đếm thời gian đếm ngược theo thời gian thực. Chúng tôi muốn tự động làm mới các giá trị của hàm mà không cần mở tệp
Các bước. Sử dụng tệp Excel khác để chèn Mô-đun Visual Basic [Alt+F11 > Chèn > Mô-đun hoặc tab Nhà phát triển > Visual Macro > Chèn > Mô-đun]. Trong mô-đun, dán macro sau.
Public Sub AutoRefreshClosedFile[]
FilePath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\Countdown"
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
Workbooks.Open FilePath
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
Giải thích vĩ mô
- Đặt Đường dẫn tệp trong thiết bị để tự động làm mới
- Câu lệnh VBA VỚI thực thi nhiều lệnh không làm thay đổi tệp
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With- Open mở tệp được gán
- Đóng đóng tệp đã mở
- Một VBA VỚI khác cập nhật tệp sau khi tự động làm mới
- Nhấn F5 để chạy macro. Excel mở Đếm ngược. tập tin xlsx. Vì tệp chứa các hàm dễ bay hơi như TODAY[] và NOW[], nên tệp sẽ tự động làm mới khi mở trong nền. Bạn có thể kiểm tra kết quả bằng cách mở lại tệp
⧭Mẹo. Trong trường hợp người dùng cần tự động làm mới tệp Excel đã mở, hãy sử dụng macro sau hoặc chỉ các dòng macro có lệnh. Tất nhiên, thay đổi Phạm vi [i. e. , A1. D14] và thời gian làm mới tự động [i. e. , “s”, 30] sử dụng Ứng dụng. OnTime trong macro.
Sub AutoCalculationRange[]
Range["A1:D14"].Calculate
Application.OnTime DateAdd["s", 30, Now], "AutoCalculationRange"
End Sub
Đọc thêm. Cách tự động làm mới trang tính Excel bằng VBA [4 phương pháp]
bài đọc tương tự
- Cách Tự động Làm mới Bảng Pivot mà không cần VBA trong Excel [3 Phương pháp Thông minh]
- [Đã sửa. ] Các ô Excel không cập nhật trừ khi nhấp đúp chuột [5 giải pháp]
- Cách Làm Mới Biểu Đồ Trong Excel [2 Cách Hiệu Quả]
- Cách tự động cập nhật Pivot Table khi dữ liệu nguồn thay đổi
Phương pháp 2. Thực thi Tự động làm mới cho tất cả các tệp Excel trong một thư mục
Thay thế cho phương pháp 1, người dùng có thể tự động làm mới tất cả các tệp Excel trong một thư mục. Thực thi macro này sẽ cho phép người dùng mở, do đó tự động làm mới các tệp Excel bên trong một thư mục được chỉ định
Các bước. Thay thế macro trước bằng macro sau.
Public Sub AutoRefreshFolder[]
Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object
mPath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\"
Set mrf = CreateObject["Scripting.FileSystemObject"]
Set mfolder = mrf.GetFolder[mPath]
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
For Each mfile In mfolder.Files
If Right[mfile.Name, 4] = "xlsx" Or Right[mfile.Name, 3] = "xlsx" Then
Workbooks.Open mPath & mfile.Name
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
End If
Next
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
End With
End Sub
Giải thích vĩ mô
- Tham khảo các biến như đối tượng
Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object- Đặt mPath để cung cấp một thư mục cụ thể
- Câu lệnh VBA VỚI thực thi nhiều lệnh mà không thay đổi tệp
- VBA FOR và IF đi qua từng tệp xlsx để mở và đóng chúng từ xa
For Each mfile In mfolder.Files
If Right[mfile.Name, 4] = "xlsx" Or Right[mfile.Name, 3] = "xlsx" Then
Workbooks.Open mPath & mfile.Name
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
End If
Next- Cuối cùng, macro tự động làm mới các tệp bằng VBA VỚI
- Vào Run > Run Sub/UserForm F5 để tự động làm mới các tệp bên trong thư mục. Đối với các tệp đã mở, nhấn F9 để làm mới trang tính đang hoạt động. Cuối cùng, kết quả tương tự như phương pháp 1 đạt được
Đọc thêm. Cách tự động làm mới bảng tổng hợp mà không cần VBA trong Excel [3 phương pháp thông minh]
Phần kết luận
Bài viết này thảo luận về các biến thể macro để tự động làm mới tệp Excel mà không cần mở chúng. Sử dụng bất kỳ macro nào được mô tả để làm như vậy. Chúng tôi hy vọng các macro này sẽ mở các tệp xlsx và do đó tự động làm mới chúng