Results 1 to 2 of 2

Printing single sheet to .pdf then embedding the file. (renaming OLEObject?)

Threaded View

  1. #1
    Registered User
    Join Date
    07-04-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    36

    Printing single sheet to .pdf then embedding the file. (renaming OLEObject?)

    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!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Writing a file’s name (or renaming it ) based on existing excel sheet data
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-30-2012, 08:11 PM
  2. Printing only one single sheet
    By ghorpade.naveen in forum Excel General
    Replies: 2
    Last Post: 02-04-2011, 06:12 AM
  3. Printing OLEObject from Excel
    By GISgirl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2010, 01:12 PM
  4. Exporting (printing) multiple worksheets as a single pdf file
    By Dano in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2008, 01:24 PM
  5. [SOLVED] I want to put page no in a single sheet in excel while printing
    By ravi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1