“La méthode "feuilles de calcul" de l'objet "_global” a échoué" erreur sur tous les autres courir

J'ai écrit un VBA Sub (ci-dessous) qui est censé ouvrir tous les .docx et/ou .xlsx fichiers dans un répertoire donné, effectuer un rechercher/remplacer l'opération, puis remplacer les fichiers d'origine avec les nouveaux. Cela fonctionne comme prévu chaque-autre-fois qu'il est exécuté pour une .fichier xlsx, et génère l'erreur "Méthode" Feuilles "de l'objet "_Global " échoué" à chaque fois. Ceci est ma première tentative à la programmation en VBA, il est donc probablement une réponse très simple que je ne vois pas. Il se casse sur la ligne de code: "Pour i = 1 À oWB.Les draps.Le comte"

Merci pour regarder

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

OriginalL'auteur user1678035 | 2012-09-18