如何在文件夹中的所有(关闭)Excel文件上运行一个VBA宏? [英] How can I run one VBA macro on all the (closed) Excel files in a folder?

问题描述

• Excel 2010

一旦我自己打开了带有原始测量值的文件，就会有一个宏，它对原始数据执行一些操作，即对它进行排序，删除某些内容，将某些内容复制到新的工作表中.

Once I opened the file with the raw measurement by myself, I have a macro which performs some actions on the raw data, i.e. sorts it, deletes some stuff, copies some stuff into a new sheet.

最后，它关闭原始数据文件(而不保存更改)，还关闭，保存并重命名新创建的Excel文件，该文件是在所有排序，删除和复制之后创建的宏.

At the end it closes the raw data file (while not saving the changes) and also closes, saves and renames the newly created Excel file the macro created after all the sorting, deleting and copying.

所以考虑到我只有这个宏文件(.xlsm)打开...

So considering I only have this macro file (.xlsm) open...

如何在该宏文件的同一文件夹中的所有Excel文件(.xlsx)上运行该宏?我不希望一个个地打开它们，然后重新运行宏.

How can I run that macro on all the Excel files (.xlsx) which are in the same folder of said macro file? I'd prefer not to open them one by one and re-run the macro again.

我目前正在通过以下方式关闭打开的原始"文件:

I am currently closing the open "raw" file via:

ActiveWorkbook.Saved = True
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

不得不承认，我不知道它们是否完全有意义，但它们确实有效.

Have to admit I don't know if they fully make sense but they work.

推荐答案

比您发布的链接更简单.
见下文:

It is simpler than the links you posted.
See below:

Sub stantial()
   Dim myfiles, wb As Workbook, ws As Worksheet
   myfiles = Dir(Thisworkbook.Path & "\*.xlsx")
   Do While Len(myfiles) <> 0
       'Debug.Print myfiles
       '~~> open as readonly since you don't want to alter the original
       Set wb = Workbooks.Open(Thisworkbook.Path & "\" & myfiles, , True)
       '~~> Do your stuff here (edit, copy, sort etc...)
       wb.Close False
       Set wb = Nothing '~~> clean up
       myfiles = Dir
   Loop   
End Sub

以上代码使用 .xlsx 扩展名检索所有文件名，然后使用 Workbooks Open 方法将其打开.打开工作簿对象并进行处理.
在继续下一个工作之前，请关闭工作簿，清理变量.HTH.

Above code retrieves all filenames with .xlsx extention then opens it using Workbooks Open method.
Once you've opened the file, you can work on the open workbook object and do your stuff.
Before you proceed with the next, close the workbook, clean up variables. HTH.

这篇关于如何在文件夹中的所有(关闭)Excel文件上运行一个VBA宏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！