VBA error in copying .pdf to .xlsm

[Cross Terrier]

I have the following code, and it is fine until I get to the very last line - the important part, pasting the data:

Sub Copy()

Dim CopyApp As String
Dim PDFFile As String
Dim StartCopy

CopyApp = "C:\Program Files (x86)\Internet Explorer\iexplore.exe"
PDFFile = "\\host\users\me\test data.pdf"

StartCopy = Shell("" & CopyApp & " " & PDFFile & "",1)

SendKeys ("^a")
SendKeys ("^c")

Workbooks("pdf to excel").Activate

Range("A1").Activate
ActiveSheet.PasteSpecial Format:="Text"

End Sub

Yes, I'm using the horrible SendKeys but that's actually working and the issue is with pasting the converted data into a blank sheet.

I may come back with questions about extending this code to sort Text to Columns in a while, but I shall have a go myself first, once this problem is out of the way. Thanks

 

[mole999]

has it ever worked, my knowledge of PDF is basically a OCR image and not primarily text