vba “对象‘_global’的方法‘工作表’失败”每隔一次运行就会出错

"Method 'worksheets' of object '_global' failed" error on every other run

提问by user1678035

I have written a VBA Sub (below) that is supposed to open all of the .docx and/or .xlsx files in a given directory, perform a find/replace operation, and then overwrite the original files with the new ones. This works as intended every-other-time it is runfor a .xlsx file, and throws the error "Method 'Sheets' of object '_Global' failed" every other time. This is my first attempt at programming in VBA, so there is probably a very simple answer that I just cannot see. It breaks on the line of code: "For i = 1 To oWB.Sheets.Count"

我编写了一个 VBA Sub（如下），它应该打开给定目录中的所有 .docx 和/或 .xlsx 文件，执行查找/替换操作，然后用新文件覆盖原始文件。这在每隔一次为 .xlsx 文件运行时按预期工作，并且每隔一次抛出错误“对象'_Global'的方法'表'失败”。这是我第一次尝试用 VBA 编程，所以可能有一个我看不到的非常简单的答案。它在代码行上中断：“For i = 1 To oWB.Sheets.Count”

Thanks for looking

感谢您的关注

Option Explicit
Public SearchPhrase As String
Public ReplacePhrase As String

Sub StringReplacer()

 Dim fd As FileDialog
 Dim PathOfSelectedFolder As String
 Dim SelectedFolder
 Dim SelectedFolderTemp
 Dim MyPath As FileDialog
 Dim fs
 Dim ExtraSlash As String
 ExtraSlash = "\"
 Dim MyFile
 Dim rngTemp As Range
 Dim MinExtensionX As String
 Dim arr() As Variant
 Dim lngLoc As Variant
 Dim oExcel As New Excel.Application
 Dim oWB As Excel.Workbook
 Dim ws As Worksheet
 Dim i As Integer
 Dim doc As String
 Dim xls As String
 Dim redlines As String

 'get desired file extensions from checkboxes in GUI and put them into an array
 doc = ActiveDocument.FormFields("CKdocx").CheckBox.Value
 If doc = True Then
    doc = "docx"
 Else
    doc = " "
 End If
 xls = ActiveDocument.FormFields("CKxlsx").CheckBox.Value
 If xls = True Then
    xls = "xlsx"
 Else
    xls = " "
 End If
 arr = Array(doc, xls)

 'set redlines variable from redlines checkbox in GUI
 redlines = ActiveDocument.FormFields("CKredlines").CheckBox.Value

 'Prepare to open a modal window, where a folder is selected
 Set MyPath = Application.FileDialog(msoFileDialogFolderPicker)
 With MyPath
    'Open modal window
    .AllowMultiSelect = False
    If .Show Then
        'The user has selected a folder
        'Loop through the chosen folder
        For Each SelectedFolder In .SelectedItems
            'record name of the selected folder
            PathOfSelectedFolder = SelectedFolder & ExtraSlash
            Set fs = CreateObject("Scripting.FileSystemObject")
            Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder)
            'Loop through the files in the selected folder
            For Each MyFile In SelectedFolderTemp.Files
                'grab extension of file
                MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)
                'check to see if extension of the file is in the accepible list
                If IsInArray(MinExtensionX, arr) Then

                    If MinExtensionX = "docx" Then
                        'Open the Document (.docx)
                        Documents.Open FileName:=PathOfSelectedFolder & MyFile.Name
                        'turn off "track changes" if that option was selected
                        If redlines = True Then
                        ActiveDocument.TrackRevisions = False
                        ActiveDocument.Revisions.AcceptAll
                        End If
                        'replace all keyphrases (.docx)
                        Set rngTemp = ActiveDocument.Content
                        With rngTemp.Find
                            .ClearFormatting
                            .Replacement.ClearFormatting
                            .MatchWholeWord = True
                            .Execute FindText:=SearchPhrase, ReplaceWith:=ReplacePhrase, Replace:=wdReplaceAll
                        End With
                        'save and close the document (.docx)
                        Application.DisplayAlerts = False
                        ActiveDocument.SaveAs FileName:=PathOfSelectedFolder & MyFile.Name
                        ActiveDocument.Close
                        Application.DisplayAlerts = True
                    End If

                    If MinExtensionX = "xlsx" Then
                        'open the document (.xlsx)
                        oExcel.Visible = True
                        Set oWB = oExcel.Workbooks.Add(PathOfSelectedFolder & MyFile.Name)
                        oWB.Activate
                        'replace all keyphrases sheet by sheet(.xslx)
                        For i = 1 To oWB.Sheets.Count
                            Sheets(i).Activate
                            ActiveSheet.Cells.Replace What:=SearchPhrase, Replacement:=ReplacePhrase, LookAt:=xlPart, MatchCase:=False
                        Next i
                        'save and close the document (.xslx)
                        Application.DisplayAlerts = False
                        oWB.SaveAs FileName:=PathOfSelectedFolder & MyFile.Name
                        oWB.Close
                        Application.DisplayAlerts = True
                    End If

                End If
            Next
        Next
    End If
 End With

 'close teh excel application and clean up
 oExcel.Quit
 Set oExcel = Nothing

 End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

采纳答案by Daniel

The problem is with this line: Sheets(i).ActivateReplace with oWB.Sheets.Activate

问题出在这一行：Sheets(i).Activate替换为oWB.Sheets.Activate

Because of other problems you will run into, I rewrote your entire if statement for ".xlsx" files with all the right references. I also added long winded comments to explain why I changed it:

由于您会遇到其他问题，我用所有正确的引用重写了“.xlsx”文件的整个 if 语句。我还添加了冗长的评论来解释我为什么要更改它：

If MinExtensionX = "xlsx" Then
    'open the document (.xlsx)
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Add(PathOfSelectedFolder & MyFile.Name)
    oWB.Activate
    'replace all keyphrases sheet by sheet(.xslx)
    For i = 1 To oWB.Sheets.Count
        oWB.Sheets(i).Activate 'Must provide the workbook or Sheets() fails
        oWB.ActiveSheet.Cells.Replace What:=SearchPhrase, Replacement:=ReplacePhrase, LookAt:=xlPart, MatchCase:=False 'Must provide the workbook or tries to find activesheet in calling application.
    Next i
    'save and close the document (.xslx)
    oExcel.DisplayAlerts = False 'Using Application instead of oExcel affects calling app instead of Excel
    oWB.SaveAs Filename:=PathOfSelectedFolder & MyFile.Name
    oWB.Close
    oExcel.DisplayAlerts = True 'Using Application instead of oExcel affects calling app instead of Excel
End If

回答by ApplePie

This might not be your specific issue, but in my case it has been in the past. Using Sheetshas proven to cause a lot of problems when you do not need the other types of sheets it contains that Worksheetsdoes not contain. Try replacing all Sheetsreferences with Worksheets.

这可能不是您的具体问题，但就我而言，它已经成为过去。Sheets事实证明，当您不需要它包含Worksheets但不包含的其他类型的工作表时，使用会导致很多问题。尝试更换所有Sheets与引用Worksheets。