运行VBA脚本会导致excel停止响应 [英] Running VBA script causes excel to stop responding

问题描述

我有一个VBA脚本，可以在500个excel文件中添加表格。
我没有运行VBA脚本并添加简单的表单，但是当我尝试添加一个VBA脚本和图形和按钮的工作表时，它工作一段时间，而不是冻结。

这是代码。我知道它没有错误处理 - 任何建议如何解决这个问题，或者什么是导致excel冻结？

 子FindOpenFiles（）
 
 Const ForReading = 1 
设置oFSO =新建FileSystemObject 
 
 Dim txtStream As TextStream 
 
 Dim FSO As Scripting.FileSystemObject ，文件夹As Scripting.folder，文件As Scripting.file，wb As Workbook，sh As Worksheet 
 Dim directory As String 
 
'设备列表的路径。 - 只为所有设备或所需的值流添加所需的路径。 
设置txtStream = oFSO.OpenTextFile（O：\SiteServices\Maintenance\Maintenance Support Folder\Maintenance Department Information\HTML for Knowledgebase\Excel for Knowledgebase\Equipement paths-all.txt， ForReading）
 
直到txtStream.AtEndOfStream 
 strNextLine = txtStream.ReadLine 
如果strNextLine<> 然后
 
设置FSO = CreateObject（Scripting.FileSystemObject）
设置文件夹= FSO.GetFolder（strNextLine）
 
 
对于每个文件在folder.Files 
如果Mid（file.Name，InStrRev（file.Name，。）+ 1）=xls然后
 Workbooks.Open strNextLine& Application.PathSeparator& file.Name 
 
设置wb =工作簿（设备进一步文档List.xls）
对于每个sh在工作簿（Master File.xls）。工作表
 sh。复制之后：= wb.Sheets（wb.Sheets.Count）
 Next sh 
 
 ActiveWorkbook.Close SaveChanges：= True 
 ActiveWorkbook.CheckCompatibility = False 
 
结束如果
 
 
下一个文件
结束如果
 
循环
 txtStream.Close 
 
结束子
  

解决方案

我终于解决了我的问题...

解决方案是添加一行代码：

  Application.Wait （现在+ TimeValue（0:00:01））
  

行后：

  sh.Copy After：= wb.Sheets（wb.Sheets.Count）
  

它允许时间将工作表复制到新的excel文件。

到目前为止，它一直在工作像一个魅力。

我想要感谢帮助我解决这个问题的所有人。

非常感谢。

I have a VBA script that adds sheets to around 500 excel files. I had no problems running the VBA script and adding simple sheets, but when I try to add a sheet with VBA script in it and graphs and buttons, it works for a while and than freezes.

Here is the code. I know it does not have error handling - any suggestions how to tackle this problem or maybe what is causing excel to freeze?

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

解决方案

I have finally solved my problem...

The solution was to add a line of code:

Application.Wait (Now + TimeValue("0:00:01"))

after the line:

sh.Copy After:=wb.Sheets(wb.Sheets.Count)

which allowed time to copy the sheet to the new excel file.

So far it has been working like a charm.

I want to thank everyone that helped me with this issue.

Many Thanks.

这篇关于运行VBA脚本会导致excel停止响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！