VBA小工具（二）针对大量PQ模型，批量打开刷新更新数据源输出报表

诡途

发布于 2022-05-09 18:59:45

4220

发布于 2022-05-09 18:59:45

文章被收录于专栏：诡途的python路

工具下载地址：https://download.csdn.net/download/qq_35866846/12196151

前文《VBA小工具（一）Excel批量插图》文章链接：https://blog.csdn.net/qq_35866846/article/details/104388461 ——————————》正文分割线《———————— 针对大量PQ模型，批量打开刷新更新数据源输出报表应用场景：针对各个品牌店铺创建PQ模型，定期执行刷新各个文件夹（按类目区分）下的模型说明：主文件目录必须写全路径子目录写文件夹名即可

代码如下

Sub 批量刷新()
    Dim MyFileName, MyPath As String
    Dim MyBook As Workbook
    Dim count As Integer
    Dim pw As String
    
    Dim c As Range
    Dim t As Integer


    
    On Error Resume Next '容错处理
    
    count = 0
    
    MyPath0 = Cells(3, 3).Value '获得c3单元格的值即主文件目录
    For Each c In Range("c4：c6"):
        MyPath = MyPath0 & "\" & c.Value
        MyFileName = Dir(MyPath & "\*.xlsx") '子文件夹下遍历xlsx文件
        Application.ScreenUpdating = False '不打开屏幕更新
        Application.DisplayAlerts = False '禁止警告弹窗
        Do Until MyFileName = ""
            Workbooks.Open MyPath & "\" & MyFileName
            'ActiveWorkbook.RefreshAll
        
            Set MyBook = ActiveWorkbook
            MyBook.RefreshAll
            Application.CalculateUntilAsyncQueriesDone '暂停宏程序，直到刷新完成
            MyBook.Save
            MyBook.Close True
            MyFileName = Dir
            count = count + 1
         Loop
            Application.ScreenUpdating = True
            Application.DisplayAlerts = True
    Next
    MsgBox (count & " 个文件全部刷新完成")
End Sub

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-02-27，如有侵权请联系 cloudcommunity@tencent.com 删除

https