Import .pdf ...?Solved/Closed

Question

Hello everybody, 

Thanks so much for this thread, it has been extremely helpful!

I have used the basic code suggested by Trowa, and it works for images, but I have two requests:

Firstly, this code does not work for .pdf files - how might I adapt it in order to make it work for single page PDF's?

Secondly, is there a way to "dummy proof" the code so that if junk is typed into D5 an error message appears instead of the screen flipping to the VBA code?

UPDATE:

Hi all,

The code that I am currently using is as follows:

--- 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5")) Is Nothing Then Exit Sub
Dim myPict As Picture

ActiveSheet.Pictures.Delete
If Target.Value = vbNullString Then Exit Sub

With Range("I7:J12")
Set myPict = Range("I7:J12").Parent.Pictures.Insert("C:\Users\Henri\Pictures\" & Target.Value & ".jpg")
    myPict.Top = .Top
    myPict.Width = .Width
    myPict.Height = .Height
    myPict.Left = .Left
    myPict.Placement = xlMoveAndSize
End With

End Sub

---

The main problems that I am having is that the code crashes if the name of the image which is called in D5 is not found in the specified directory. i.e. if I type "asdfsdxcv" into D5 then the code crashed. I would prefer for nothing to happen.

And then of course, as the subject of the thread indicates - it would be great if I could convert this code to import .pdf

Any advise would be much appreciated!



System Configuration: Windows / Chrome 56.0.2924.87

TrowaD · Accepted Answer

Hi Rick,

The following code has been adjusted to work with pdf files as well as the dummy-proof part. Remove code line 18 when you don't like a message to show.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5")) Is Nothing Then Exit Sub
Application.DisplayAlerts = False 'this will prevent an alert when filename is not found
ActiveSheet.OLEObjects.Delete 'deletes previously inserted .pdf
If Target.Value = vbNullString Then Exit Sub

On Error GoTo ErrorMessage

Range("I7").Select
ActiveSheet.OLEObjects.Add(Filename:="C:\Users\Henri\Pictures\" & _
Target & ".pdf", Link:=False, DisplayAsIcon:=False).Select

Application.DisplayAlerts = True 'enables alerts again when filename is found
ErrorMessage:
Application.DisplayAlerts = True 'enables alerts again when filename is not found
MsgBox "File not found", vbInformation 'this displays a message to inform the user

End Sub
 
Best regards,
Trowa

Import .pdf ...?

1 reply

Subscribe To Our Newsletter!