我有一个 VBA 脚本,可以将工作表添加到大约 500 个 Excel 文件中。
我运行 VBA 脚本和添加简单的工作表没有任何问题,但是当我尝试添加其中包含 VBA 脚本以及图形和按钮的工作表时,它会工作一段时间然后冻结。
这是代码。我知道它没有错误处理 - 有什么建议如何解决这个问题或者是什么导致 Excel 冻结?
Sub FindOpenFiles()
Const ForReading = 1
Set oFSO = New FileSystemObject
Dim txtStream As TextStream
Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file, wb As Workbook, sh As Worksheet
Dim directory As String
'The path for the equipement list. - add the desired path for all equipement or desired value stream only.
Set txtStream = oFSO.OpenTextFile("O:\SiteServices\Maintenance\Maintenance Support Folder\Maintenance Department Information\HTML for Knowledgebase\Excel for Knowledgebase\Equipement paths-all.txt", ForReading)
Do Until txtStream.AtEndOfStream
strNextLine = txtStream.ReadLine
If strNextLine <> "" Then
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(strNextLine)
For Each file In folder.Files
If Mid(file.Name, InStrRev(file.Name, ".") + 1) = "xls" Then
Workbooks.Open strNextLine & Application.PathSeparator & file.Name
Set wb = Workbooks("Equipment Further Documentation List.xls")
For Each sh In Workbooks("Master File.xls").Worksheets
sh.Copy After:=wb.Sheets(wb.Sheets.Count)
Next sh
ActiveWorkbook.Close SaveChanges:=True
ActiveWorkbook.CheckCompatibility = False
End If
Next file
End If
Loop
txtStream.Close
End Sub
所以,给你一些建议:
第一。 (根据评论)
添加作为第一行到您的子程序中:Application.ScreenUpdating = false
并在前面添加另一行End Sub
: Application.ScreenUpdating = true
第二。移动这一行(它设置 constance 参考):
Set wb = Workbooks("Equipment Further Documentation List.xls")
before:
Do Until txtStream.AtEndOfStream
第三只是一个提示。
要查看子进程的进度,请添加以下行:
Application.StatusBar = file.Name
在这一行之后:
Workbooks.Open strNextLine & Application.PathSeparator & file.Name
之前End Sub
另外添加以下代码:
Application.StatusBar = false
因此,您可以在 Excel 应用程序的状态栏中看到当前正在处理的文件名。
请记住,处理 500 个文件一定很耗时。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)