I am attempting to have a button create a pdf file and embed it into a workbook at a specific cell range. Once it does that I want to resize the object so that it will fit into one normal sized cell. From there the user can double click on it and it will open in their pdf viewer.
I am pretty close to figuring it out, I have a button that works to save the object in a local directory and then it grabs that file and embeds it.
The problem I am having is resizing. I want to resize the object but my skills are terrible with VBA (however they are top notch with a bow staff). Here is what I have so far...
Sub PDFthingy()
'
' PDFthingy Macro
'
Dim Obj As Object
'
Sheets("Sheet1").Select
ChDir "C:\Users\Public\Documents"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"Chart", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Range("P30").Select
ActiveSheet.OLEObjects.Add(Filename:= _
"Chart.pdf", Link:=False, _
DisplayAsIcon:=False).Select
'Set Obj = ActiveSheet.OLEObjects("Object 18").Object
'Obj.Name = "UserInput1"
' ActiveSheet.Shapes("UserInput1").ScaleWidth 0.029342723, msoFalse, _
' msoScaleFromTopLeft
' ActiveSheet.Shapes("UserInput1").ScaleHeight 0.0293427151, msoFalse, _
' msoScaleFromTopLeft
End Sub
As you can see, I have tried to resize the object based on a name I gave it. This is because I will be creating one of these charts for up to 50 embedded pdfs yet they all have to come from one sheet. So, if I have it referencing "Object 1", as soon as I hit the button again it creates it as "Object 2" and the VBA reference is lost.
How would I rename or otherwise resize this object to fit into a cell?
I have included a test workbook that I have been playing around with.
Thanks in advance!
Bookmarks