1

I have a process in Excel that adds images from a PDF file with the following code:

valRef = "TabName"
FilePath = "\\network\file.pdf"
' Process stops in the next line
Worksheets(valRef).OLEObjects.Add Filename:=FilePath, Link:=False, DisplayAsIcon:=False

Some times, the process stops when adding the PDF file with the error 'Run-time error '1004' Cannot insert object

When the debug window comes up, I can press F5 and the process continues. The PDF file exists and with the same file, in the same network location, some times works without stopping.

Any ideas on how to prevent the process to stop?

Selrac
  • 2,203
  • 9
  • 41
  • 84
  • 1
    I would look into error handling, where you can retry to add the object. http://www.cpearson.com/excel/errorhandling.htm – seadoggie01 Sep 28 '18 at 14:04
  • 1
    The problem is that it is not really a problem. The file exists and the I do need to embed it in Excel – Selrac Sep 28 '18 at 14:07

1 Answers1

2

I know it looks ugly, but you could keep retrying with an error handler

if 1 = 0 then 'never enter this section, unless an error occurs
  looperror:
  Resume 'without this, the second time an error occurs, it will trigger, ignoring the on error
end if

on error goto looperror
  Worksheets(valRef).OLEObjects.Add Filename:=FilePath, Link:=False, DisplayAsIcon:=False
on error goto 0

If you don't want this to go on endlessly, you could add a counter and cancel after a few tries, or ask the user via msgbox if they want to retry.

Rob
  • 101
  • 5
  • The ingenuity here is admirable, but I think OP just wants to skip that line if it gives him an error, not retry – Marcucciboy2 Sep 28 '18 at 14:25
  • 2
    Actually the OP commented on his own question saying that he insists on the PDF being inserted, not skipping the file. Also if you read the question properly, you see that he mentions pressing F5, which does in fact trigger a retry on the crashing code line. – Rob Sep 28 '18 at 14:28
  • 1
    Thanks @Rob this is what I was looking for. I found out that the problem is the network connection. If I copy the file in locally before hand the problem does not occurs. – Selrac Sep 28 '18 at 14:53
  • Maybe excel's copy command is more resilient to network instability. If that's the case, copy it to a local folder first: https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another – Rob Sep 28 '18 at 15:20