在多个 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.

推荐答案

假设您将文件放在相对于主工作簿的文件"目录中，您的代码可能如下所示:

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屋！