Pesquisa de string VBA em arquivos no diretório que não funcionam no Excel 2007

Question

Estou tentando procurar uma string dentro de uma pasta contendo vários arquivos. Depois que a string for encontrada, ela deve selecionar e copiar um valor em uma célula adjacente de 5 colunas para a direita (Offset(0,5)) , depois colá-la na minha pasta de trabalho. Se mais strings forem encontradas, elas devem ser coladas como uma lista End (xlDown).

Essa é a ideia, mas não consigo fazê-lo funcionar, a mensagem de erro: " Subscrito fora do intervalo ".

Sub searchBOM()

Dim BOM As String 'the text i'm gonna look for
Dim path As String 'the folder containing several files
Dim filename As String 'one of the files to search in

path = "D:\folder\"
filename = Dir(path)
BOM = InputBox("please enter bom code") 'where the user enters the string

If BOM = "" Then
        MsgBox ("please input valid BOM code") 'not important really, just a small validation
   Else
        Do While filename <> "" 'so the DIR function scans all files
              **'here I get the error message SUBSCRIPT OUT OF RANGE:**
              Workbooks.Open(path & filename).Sheets("Sheet1").UsedRange.Find(BOM).Offset(0, 5).Copy

              'the macro never gets to this line: 
              ThisWorkbook.Sheets("Sheet1").Range("c" & Range("c5").End(xlDown).Row).Paste
              Workbooks.Open(path & filename).Close
              filename = Dir
        Loop

End If

End Sub

    

Answer 1

Aqui está minha abordagem para me aproximar do problema real:

Divida a linha muito longa que causa o problema em partes menores, por exemplo

  Set wrkbk = Workbooks.Open(path & filename)
  Set sht = wrkbk.Sheets("Sheet1")
  Set rang = sht.UsedRange
  Set fnd = rang.Find(BOM)
  If Not (fnd Is Nothing) Then
     cpy = fnd.Offset(0, 5).Copy
  End If

Então você verá que o problema está na linha Set sht = wrkbk.Sheets("Sheet1") .

Isso significa que existe uma pasta de trabalho que não possui uma planilha chamada "Sheet1". Talvez a folha tenha sido renomeada pelo usuário.

Quando o Excel parar no erro, clique com o botão direito do mouse em filename e escolha Add watch para ver qual arquivo é afetado pelo nome incorreto da planilha.