Macro Read Several PDF's Documents

[afangueiro]

Good afternoon

I have this macro to read several PDF's and extract the data I want.
It happens that whenever a cell with "#NAME?" the macro gives an error and no longer advances.
Can you help me? I would be very grateful for your help.

In advance grateful

*********************************

Private Sub cmdExtrair_Click()


For Each Sheet In ThisWorkbook.Worksheets
If Sheet.Name = "Invoice" Then
Application.DisplayAlerts = False
Sheet.Delete
Application.DisplayAlerts = True
Exit For
End If
Next
Worksheets.Add.Name = "Invoice"

PDFs = Application.GetOpenFilename(FileFilter:="Arquivos PDF (*.pdf), *.pdf", MultiSelect:=True)
If IsArray(PDFs) Then
For Each caminhoArquivo In PDFs
caminhoAdobeReader = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
Call Shell(caminhoAdobeReader & " """ & caminhoArquivo & """", vbNormalFocus)
Application.Wait Now + TimeValue("0:00:06")
SendKeys "^a"
SendKeys "^c"
Application.Wait Now + TimeValue("0:00:06")
Worksheets("Invoice").Paste Destination:=Worksheets("Invoice").Range("A" & Worksheets("Invoice").Range("A1").CurrentRegion.Rows.Count)
SendKeys "^w"
Next
Call Shell("TaskKill /F /IM AcroRd32.exe")
Else
Application.DisplayAlerts = False
Worksheets("Invoice").Delete
Application.DisplayAlerts = True
MsgBox "Selecione um ou mais arquivos PDF para prosseguir.", vbExclamation
Exit Sub
End If

With Worksheets("Invoice")
.Cells(1, 3) = "NOME LOJA"
.Cells(1, 4) = "VENDAS BRUTAS"
.Cells(1, 5) = "PROMOÇÃO PARCEIRO"
.Cells(1, 6) = "TAXA"
.Cells(1, 7) = "TOTAL"
.Cells(1, 8) = "CUSTO INCIDENCIAS"
.Cells(1, 9) = "DIVIDA ACUMULADA"
.Cells(1, 10) = "PEDIDOS JÁ PAGOS"
.Cells(1, 11) = "VALOR A RECEBER"
End With


qtdlinhas = Worksheets("Invoice").Range("A1").CurrentRegion.Rows.Count

For linha = 1 To qtdlinhas

celula = Worksheets("Invoice").Cells(linha, 1)

If Left(celula, 13) = "Vendas brutas" Then
nomeloja = Worksheets("Invoice").Cells(linha - 4, 1)
proximaLinha = Worksheets("Invoice").Cells(1, 3).CurrentRegion.Rows.Count + 1
Worksheets("Invoice").Cells(proximaLinha, 3) = nomeloja

End If

If Left(celula, 13) = "Vendas brutas" Then
VendasBrutas = Mid(celula, 15, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 4) = VendasBrutas

End If

If Left(celula, 31) = "Promoção assumida pelo parceiro" Then
TotalPromocao = Mid(celula, 33, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 5) = TotalPromocao

End If


If Left(celula, 13) = "Total da Taxa" Then
TotalTaxa = Mid(celula, 15, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 6) = TotalTaxa

End If


If Left(celula, 15) = "Total da fatura" Then
TotalGlovo = Mid(celula, 17, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 7) = TotalGlovo

End If


If Left(celula, 19) = "- Coste incidencias" Then
CustoIncid = Mid(celula, 21, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 8) = CustoIncid

End If

If Left(celula, 30) = "Dívida acumulada pelo Parceiro" Then
DividaAcumul = Mid(celula, 32, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 9) = DividaAcumul

End If

If Left(celula, 28) = "Pedidos já pagos em dinheiro" Then
PedidosPagos = Mid(celula, 30, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 10) = PedidosPagos

End If

If Left(celula, 34) = "Valor a transferir para o Parceiro" Then
TotalFatura = Mid(celula, 36, Len(celula))
Worksheets("Invoice").Cells(proximaLinha, 11) = TotalFatura

End If

Worksheets("Invoice").Cells.Columns.AutoFit

Next

SendKeys "{NUMLOCK}"

End Sub

 

[Micron]

Suggest trap the error with an error handler if you can't eliminate the bad data - as long as you know what the error number is. If not, I suppose you can just Resume Next regardless of what error it is that you're getting.

TBH, you really should put more than a few lines of code between code tags (vba button on posting toolbar) and do proper indentation. Maybe I'm in the minority but I won't even try to read code that's posted like that. Example of error handler:

Private Sub cmdExtrair_Click()

On Error GoTo errHandler
''bunch of code here

exitHere:
Exit Sub

errHandler:
If err.Number = (you put your error number here without these parentheses) Then
  Resume Next
Else
  MsgBox "Error " & err.Number & ": " & err.Description
  Resume exitHere
End If

End Sub