VBA遍历Excel工作簿工作表刷新查询时循环重复问题求助

问题场景 #

有两个Excel文件：

• 一个是包含代码和汇总表的code_spreadsheet.xlsm
• 另一个是存有多张带SQL查询工作表的query_sheet.xlsx

原本要实现的逻辑：遍历query_sheet.xlsx的每个工作表，刷新查询，然后把工作表名和刷新时间更新到code_spreadsheet.xlsm的「refresh dates」工作表里。但代码执行时只会重复处理第一个工作表，没法进入下一个。

问题根源 #

1. 循环集合引用不稳定：代码里用.ActiveWorkbook.Worksheets遍历，但切换工作簿后ActiveWorkbook会指向当前激活的文件，导致循环的工作表集合丢失了初始的遍历位置
2. 过度依赖激活/选择操作：全程用Activate、Select、ActiveSheet、ActiveCell这类操作，切换工作簿时会打乱循环的上下文，让循环始终停留在第一个工作表
3. 未用变量固定工作簿对象：打开查询文件后没把它赋值给变量，后续全靠激活来操作，很容易出错

修正后的代码 #

Sub refresh_data_1()
    Dim sheetName As String
    Dim ws As Worksheet
    Dim queryWb As Workbook ' 存储查询文件的工作簿对象
    Dim summaryWs As Worksheet ' 存储汇总表的工作表对象
    Dim currDir As String
    Dim nextRow As Long ' 记录汇总表的下一行
    
    ' 获取当前代码文件所在路径，拼接查询文件路径
    currDir = ThisWorkbook.Path & "\data\"
    
    ' 打开查询文件并赋值给变量，避免依赖激活
    Set queryWb = Workbooks.Open(currDir & "query_sheet.xlsx")
    
    ' 直接引用汇总表，不用激活/选择
    Set summaryWs = ThisWorkbook.Sheets("refresh dates")
    summaryWs.Visible = True
    
    ' 记录查询文件的工作表数量（修正原代码的错误逻辑）
    summaryWs.Range("D1").Value = queryWb.Sheets.Count
    
    ' 初始化汇总表的起始行
    nextRow = 3
    
    ' 直接遍历查询工作簿的工作表集合，不用切换激活
    For Each ws In queryWb.Worksheets
        ' 刷新当前工作表的查询表
        ws.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
        
        ' 直接写入汇总表，不用激活切换
        summaryWs.Cells(nextRow, "B").Value = ws.Name
        summaryWs.Cells(nextRow, "C").Value = Now()
        
        ' 切换到下一行
        nextRow = nextRow + 1
    Next ws
    
    ' 可选：关闭查询文件，根据需求决定是否保存
    ' queryWb.Close SaveChanges:=True
End Sub

关键改进点 #

• 用queryWb变量固定查询工作簿对象，不管怎么操作，遍历的集合始终是queryWb.Worksheets，不会丢失遍历位置
• 用summaryWs直接引用汇总表，全程不用Activate和Select，彻底避免上下文混乱
• 用nextRow记录汇总表的写入行，代替ActiveCell的偏移操作，逻辑更直观清晰
• 修正了原代码里记录工作表数量的错误（原代码误写为代码文件的表数，改为查询文件的表数）

补充说明 #

不需要把汇总表移到查询文件，只要用对象变量来管理工作簿和工作表，就能稳定完成遍历和更新操作，不会再出现循环卡住的问题。

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