在多个excel文件上运行相同的excel宏 [英] Run same excel macro on multiple excel files

问题描述

我有一个文件夹，我每天接收到1000+个excel文件，它们都是相同的格式和结构。我想做的是每天在所有100多个文件上运行一个宏？

I have a folder where I receive 1000+ excel files on daily bases they all are same format and structure. What I want to do is run a macro on all 100+ files on daily bases ?

有自动化的方法吗？所以我可以每天在1000多个文件上继续运行同一个宏。

Is there way to automate this ? So I can keep running that same macro on 1000+ files daily.

推荐答案

假设您将文件放在Files目录中，相对于主工作簿，代码可能如下所示： p>

Assuming that you put your files in "Files" directory relative to your master workbook your code might look like this:

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "\Files\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    With wb
        'Do your work here
        .Worksheets(1).Range("A1").Value = "Hello World!"
    End With
End Sub

在这个例子中 DoWork（）是您应用于所有文件的宏。确保您在宏中执行所有处理始终位于 wb （当前打开的工作簿）的上下文中。

In this example DoWork() is your macro that you apply to all of your files. Make sure that you do all your processing in your macro is always in the context of the wb (currently opened workbook).

免责声明：为简洁起见，跳过了所有可能的错误处理。

这篇关于在多个excel文件上运行相同的excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！