<p>我避免使用多个工作簿,如瘟疫,这是一个痛苦,如果这是一个持续的需求,那么我建议您回顾一下您的工作簿设计,看看是否可以合并回一个工作簿。我经常看到每个月保存的工作簿都应该有一个工作簿,其中一个工作表包含原始数据,每行表示一个月,然后是另一个显示工作表,用于查找用户选择的原始数据。这是一个非常大的概括,你很可能处于完全不同的情况。在</p>
<p>如果这是一次性的-我知道这不是你想要的,但我认为你最好使用VBA循环查看工作簿。类似(未经测试):</p>
<p>Excel 2003:</p>
<pre><code>Sub AdjustMultipleFiles()
Dim lCount As Long
Dim wbLoopBook As Workbook
Dim wsLoopSheet As Worksheet
With Application
.ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With
With Application.FileSearch
.NewSearch
'// Change path to suit
.LookIn = "C:\MyDocuments"
'// ALL Excel files
.FileType = msoFileTypeExcelWorkbooks
'// Uncomment if file naming convention needed
'.Filename = "Book*.xls"
'// Check for workbooks
If .Execute > 0 Then
'// Loop through all.
For lCount = 1 To .FoundFiles.Count
'// Open Workbook x and Set a Workbook variable to it
Set wbLoopBook = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
'// Loop through all worksheets
For Each wsLoopSheet In wbLoopBook.Worksheets
'//Update your worksheets here...
Next wsLoopSheet
'// Close Workbook & Save
wbLoopBook.Close SaveChanges:=True
'// Release object variable
Set wbLoopBook = Nothing
Next lCount
End If
End With
With Application
.ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
End With
End Sub
</code></pre>
<p>对于EXCEL 2007+:</p>
^{pr2}$
<p>Excel 2007+(文件系统对象-LateBinding)</p>
<pre><code>Sub AdjustMultipleFiles()
Dim wbLoopBook As Workbook
Dim wsLoopSheet As Worksheet
With Application
.ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With
With CreateObject("Scripting.FileSystemObject")
'// Change path to suit
For Each File In .GetFolder("C:\Documents").Files
'// ALL Excel 2007 files
If .GetExtensionName(File) = "xlsx" Then
'// Open Workbook x and Set a Workbook variable to it
Set wbLoopBook = Workbooks.Open(Filename:=File.Path, UpdateLinks:=0)
'// Loop through all worksheets
For Each wsLoopSheet In wbLoopBook.Worksheets
'//Update your worksheets here...
Next wsLoopSheet
'// Close Workbook & Save
wbLoopBook.Close SaveChanges:=True
'// Release object variable
Set wbLoopBook = Nothing
End If
Next File
End With
With Application
.ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
End With
End Sub
</code></pre>