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]

Maybe it helps, you're missing the "s" in xlPastevalues

 

[rex759]

Thank you Dante for responding. Believe it or not, when I add the "s" to value, I get an Error - Paste Special Method of Range class failed. I changed it to

pasteSpecial Paste:="text"

Which works on my machine but not my co-workers. I believe it has something to do with the version of Adobe he is using.

 

[DanteAmor]

The right is xlPastevalues.

I believe it has something to do with the version of Adobe he is using.

It could be, perhaps, Control+a is not to select in some version of Adobe.

Maybe the memory is empty so it doesn't have something to paste. Try rearranging the lines, for example:

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

  'Paste PDF in next available row
  myworksheet.Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
  
  Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

 

[rex759]

Testing your suggestion.....

 

[DanteAmor]

It definitely cleans the memory when you run this instruction: Call Shell("TaskKill /F /IM Acrobat.exe", vbHide).

The following code works for me.

  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.PasteSpecial Format:="Texto"
  
  'Close PDF
  Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

 

[rex759]

Same kind of issue unfortunately. Run time error on Paste Special Values when I ran the macro a second time. Changed it to text and encountered the same thing.

Now something I did notice

I have the user selecting the folder where the PDFs are stored. It saves the path so they dont have to select it a second or third time. What I found if I delete the path on the sheet tab that it is saved, forcing the user to select the folder once again, it works. But only for this specific user, otherwise, it works fine for every one else.

Here is the top portion of the code.

'Check if Path is loaded

If Worksheets("data").Range("A1") = "" Then
'User picks folder, path is loaded to Data tab
On Error GoTo err
Dim fileExplorer As FileDialog
Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
Dim folderPath As String

'To allow or disable to multi select
fileExplorer.AllowMultiSelect = False

With fileExplorer
If .Show = -1 Then 'Any folder is selected
folderPath = .SelectedItems.Item(1)

Else ' else dialog is cancelled

MsgBox "You have press cancelled"

folderPath = "NONE" ' when cancelled set blank as file path.

Exit Sub

End If

End With

err:

ThisWorkbook.Sheets("Data").Range("A1") = folderPath & "\"

End If

 

[rex759]

I didnt see your code before posting my last message. I will give that one a try

 

[rex759]

I got a run time error, PasteSpecial method of worksheet class failed. I assume this might of been a typo: ActiveSheet.PasteSpecial Format:="Texto", I changed to "Text" and it worked on my computer. Then when tested on my co-worker, it doesnt paste at all, getting a run time error of Microsoft Excel cannot pate the file.

I had my co-worker update his Adobe to the same version as mine. Still no go

 

[rex759]

I should mentioned that if I hit debug and step through, it works