PowerQuery刷新日志记录失效问题及模块刷新方法求助

一、替代Worksheet_Change的触发方案 #

PowerQuery刷新属于后台静默替换数据，不会被Excel识别为用户发起的单元格变更，因此无法触发Worksheet_Change事件。可以采用以下两种精准触发方式：

1. 利用Workbook_SheetCalculate事件 #

PowerQuery刷新后，目标工作表会触发计算事件（即使表格无公式），可在ThisWorkbook模块中编写以下代码：

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim querySheet As Worksheet
    Set querySheet = ThisWorkbook.Sheets("AReS Contract View Master")
    If Sh.Name <> querySheet.Name Then Exit Sub

    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = querySheet.ListObjects("AReS_Masters_Contract")
    On Error GoTo 0
    If tbl Is Nothing Then Exit Sub

    ' 避免重复触发：检查最近10秒内是否已记录刷新
    Dim logSheet As Worksheet
    Set logSheet = ThisWorkbook.Sheets("AReS Contract View >")
    Dim lastLogRow As Long
    lastLogRow = logSheet.Cells(logSheet.Rows.Count, "B").End(xlUp).Row
    If lastLogRow < 5 Then lastLogRow = 5

    If logSheet.Range("B" & lastLogRow).Value <> "" Then
        Dim lastLogTime As Date
        On Error Resume Next
        lastLogTime = CDate(Mid(logSheet.Range("B" & lastLogRow).Value, 11, 19))
        On Error GoTo 0
        If Now - lastLogTime < TimeValue("00:00:10") Then Exit Sub
    End If

    ' 写入刷新日志
    Dim nextRow As Long
    nextRow = lastLogRow
    If logSheet.Range("B" & nextRow).Value <> "" Then nextRow = nextRow + 1
    logSheet.Range("B" & nextRow).Value = "Refreshed: " & Format(Now, "yyyy-mm-dd hh:nn:ss") & _
                                          " by " & Environ("Username")
End Sub

注：添加10秒判断是为了避免其他计算操作重复触发日志记录。

2. 刷新代码后直接调用日志逻辑 #

如果通过代码触发PowerQuery刷新，可在刷新完成后直接执行日志写入，这是最精准的方式，结合下方模块刷新方法使用。

二、通过模块刷新PowerQuery表格的方法 #

在标准模块（如Module1）中编写以下代码，支持单表或全表刷新：

1. 刷新指定PowerQuery表格 #

Sub RefreshSpecificPQTable()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("AReS Contract View Master").ListObjects("AReS_Masters_Contract")
    
    ' 等待刷新完成后再执行后续代码
    tbl.QueryTable.Refresh BackgroundQuery:=False
    
    ' 刷新完成后写入日志
    LogPQRefresh
End Sub

' 独立的日志写入子程序
Sub LogPQRefresh()
    Dim logSheet As Worksheet
    Set logSheet = ThisWorkbook.Sheets("AReS Contract View >")
    
    Dim nextRow As Long
    nextRow = logSheet.Cells(logSheet.Rows.Count, "B").End(xlUp).Row
    If nextRow < 5 Then nextRow = 5
    If logSheet.Range("B" & nextRow).Value <> "" Then nextRow = nextRow + 1
    
    logSheet.Range("B" & nextRow).Value = "Refreshed: " & Format(Now, "yyyy-mm-dd hh:nn:ss") & _
                                          " by " & Environ("Username")
End Sub

2. 刷新工作簿中所有PowerQuery查询 #

Sub RefreshAllPQQueries()
    ' 触发所有PowerQuery查询刷新
    ThisWorkbook.RefreshAll
    
    ' 等待所有后台刷新完成
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop
    
    ' 写入刷新日志
    LogPQRefresh
End Sub

三、原代码无效的原因 #

你提供的Worksheet_Change事件代码无法生效，核心原因是PowerQuery的后台数据替换不属于Excel定义的"用户发起的单元格变更"范畴，因此不会触发该事件，日志写入逻辑自然无法执行。

内容的提问来源于stack exchange，提问作者BDASkipper