How to dynamically change inserted object

grivage

New Member
Joined
Apr 9, 2011
Messages
3
Hello,
I would like to know how to change an inserted object automatically.
I have an excel worksheet at work that I enter part numbers into, it then displays information about that specific part. What I am unable to do is have a print of that part, a .PDF, insert itself into the worksheet. I can make a hyperlink to the object, but I am trying to get rid of the extra step of opening another program just to print the .PDF.

Thanks,

grivage


<hr style="color:#ebebeb; background-color:#ebebeb" size="1">
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Welcome to the Board!

Do you have a bunch of .pdf files that have the same name as the part number?
Part XXX and XXX.pdf?
 
Upvote 0
Each .PDF has a different part number, when I enter the part number into my sheet, all the info for that part is displayed. My goal is to get the print for that part number to up date automatically as well so I print the information and .PDF with one click
 
Upvote 0
Right now what I have set up is a hyperlink that changes according to the part number that is entered, and that is used to open the .PDF. That works pretty good, but if I could get it to insert itself into my excel sheet, it would make printing large quantities of orders much faster.
 
Upvote 0
Comments:
1) Code will close any copies of Acrobat Reader that are running.
2) There is no checking for validity of location to paste (sRange) or file location.name (sFilePathName), although it could be added.
3) Usage: ImportPDF Upper_Left_Cell, PDF_Full_Path_&_Filename<CELL address>
4) Example: ImportPDF "B19", "c:\pdffiles\R51.pdf"


Code:
Option Explicit
'Imported codeblock from:
'http://www.mvps.org/access/api/api0025.htm
'************** Code Start ***************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Const WM_CLOSE = &H10
Private Const INFINITE = &HFFFFFFFF
Private Declare Function apiPostMessage _
    Lib "user32" Alias "PostMessageA" _
    (ByVal Hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) _
    As Long
Private Declare Function apiFindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassname As String, _
    ByVal lpWindowName As String) _
    As Long
 
Private Declare Function apiWaitForSingleObject _
    Lib "kernel32" Alias "WaitForSingleObject" _
    (ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) _
    As Long
 
Private Declare Function apiIsWindow _
    Lib "user32" Alias "IsWindow" _
    (ByVal Hwnd As Long) _
    As Long
 
Private Declare Function apiGetWindowThreadProcessId _
    Lib "user32" Alias "GetWindowThreadProcessId" _
    (ByVal Hwnd As Long, _
    lpdwProcessID As Long) _
    As Long
 
Function fCloseApp(lpClassname As String) As Boolean
'Usage Examples:
'   To close Calculator:
'       ?fCloseApp("SciCalc")
'
Dim lngRet As Long, Hwnd As Long, pID As Long
    Hwnd = apiFindWindow(lpClassname, vbNullString)
    If (Hwnd) Then
        lngRet = apiPostMessage(Hwnd, WM_CLOSE, 0, ByVal 0&)
        Call apiGetWindowThreadProcessId(Hwnd, pID)
        Call apiWaitForSingleObject(pID, INFINITE)
        fCloseApp = Not (apiIsWindow(Hwnd) = 0)
    End If
End Function
'************* Code End ***************
 
Sub ImportPDF(sRange As String, sFilePathName As String)
    Range(sRange).Select
    ActiveSheet.OLEObjects.Add(Filename:=sFilePathName, Link:=False, _
        DisplayAsIcon:=False).Activate
    fCloseApp "AcrobatSDIWindow" 'This closes the instance of acrobat that stays open after previous statement
 
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,617
Messages
6,179,914
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top