Import .pdf ...?

[Solved/Closed]
Report
-
Posts
1
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 12, 2017
-
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!



1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 12, 2017

Thanks so much Trowa!
Your code works perfectly!

Happy Days!:)