Run time error - Microsoft Excel cannot paste the data

[rex759]

Hello,
This is a bit baffling because the code has been working fine for most users and one person who has a new computer is giving me the headache.
The summary of this macro is it opens a PDF, copies and pastes it to sheet. I repeat this process for several PDF reports, open, copy, paste, close. Works fine for most people but for one person, it gets a runtime error:

.Range("A" & LastRow).PasteSpecial Paste:=xlPastevalue

It works fine the first run but when used for the second run, we get a runtime error. When I hit debug and F8 through the code, it continues correctly which make me think it doesn’t know where to paste the data.
I also tried .pastespecial format:=”Text” and it did the same thing, worked on the first pass, error on the second.

Here is a snippet of the code:

Set myworksheet = ActiveWorkbook.Worksheets("Interim")
Call Shell(Pathname:=shellpathname, Windowstyle:=vbNormalFocus)

‘Copy open PDF
Application.Wait Now + TimeValue("0:00:03")

SendKeys "^a"
SendKeys "^c"

'Unhide Sheet
Worksheets("Interim").Visible = True
Application.Wait Now + TimeValue("0:00:03")

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

‘Paste PDF in next available row
With myworksheet
    .Range("A1") = "A"
    LastRow = myworksheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Range("A" & LastRow).PasteSpecial Paste:=xlPastevalue
End With

Any help is greatly appreciated

 

[DanteAmor]

Another way to paste:

  Set myworksheet = ActiveWorkbook.Worksheets("Interim")
  'Unhide Sheet
  myworksheet.Visible = True
  myworksheet.Select
  LastRow = myworksheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
  myworksheet.Range("A" & LastRow).Select
  
  'Copy open PDF
  Call Shell(Pathname:=shellpathname, Windowstyle:=vbNormalFocus)
  Application.Wait Now + TimeValue("0:00:03")
  SendKeys "^a", True
  Application.Wait Now + TimeValue("0:00:01")
  SendKeys "^c", True
  Application.Wait Now + TimeValue("0:00:03")

  'Paste PDF in next available row
  ActiveSheet.Paste
  
  'Close PDF
  Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

 

[rex759]

Works fine on my machine, not on my co-workers, same error message. Its really baffling.

 

[rex759]

Difficult to pin point the issue. Wondering if has something to do with his version of Office.

 

[DanteAmor]

Maybe you should use another method to import the information from PDF to excel:

import data from PDF into excel sheet with the same structure

hello I put this link VBA Express : Excel - get the data from PDF file into Excel sheet(s) or text file(s) you can see the code . I don't attach code because is too big. so the code import all of data from file pdf into one column A into sheet excel . I...

www.mrexcel.com

PDF to Excel

How to get data from a PDF file in to Excel. The dirty secret? Word.

www.mrexcel.com

Import data from PDF in to Excel

I am trying to create a Macro in VBA to Import data from either single or multiple PDF forms in to an Excel workbook. Adding each form as a new row in the sheet. I am thinking of a Button with the macro attached on the Sheet, which then identifies a folder containing the forms. It then pulls...

www.mrexcel.com

 

[rex759]

Ok, I will go through the links you sent. I appreciate the help.