Report

Import .pdf ...? [Solved]

Ask a question RickRoll - Last answered on Apr 12, 2017 at 03:48 AM by RickRoll
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!



Helpful
+2
plus moins
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
Was this answer helpful?  
RickRoll 1Posts Tuesday March 28, 2017Registration date April 12, 2017 Last seen - Apr 12, 2017 at 03:48 AM
Thanks so much Trowa!
Your code works perfectly!

Happy Days!:)
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!