Closed Thread
Results 1 to 16 of 16

Embed Image in xlam and insert in header / footer using macro / VBA

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Embed Image in xlam and insert in header / footer using macro / VBA

    I have a problem, very similar to this one and that answer really doesn't help me..

    To be specific, I'm trying to create a macro in an xlam file. The macro should insert images stored in the xlam file into the active excel file in the header and footer.

    This little piece of code works as long as it's referencing a full path filename, but if I copy the xlam file to a different computer, the image files cannot be found (since obviously the files are not located there).

    So how do I reference the images stored in the xlam file in a macro like this one?
    I need to exchange the Filenames with something relative or something embedded in the xlam.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    If you store the images in ActiveX image controls on the sheet, you can use the SavePicture function to save them to a folder temporarily then assign them to the header. After that, you can delete the files again.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    wow .. although that sounds really complex, it also sounds like it would really work

    But I'm a bit new to excel macros (even though I'm really not new to programming).
    Could you possibly point me in the direction of some examples on doing these steps?

    I have no idea how to
    *store images in ActiveX image controls
    *use the SavePicture function
    *overcome file security access to save the images to disk / delete them again (I've heard plenty of stories about excel security to know this can be an issue, but a working example could prove me wrong )

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    1. Select the Developer tab, click the Insert dropdown, choose the Image control from the ActiveX section and draw it on the sheet.
    2. Make a note of the name of the control (in the Name box to the left of the formula bar).
    3. Right-click the control and select Properties
    4. Click in the Picture property, then click the ellipsis button and choose your picture.


    Your code is then something like this:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Well this is my first time working with ActiveX objects in excel..

    It looks like this ActiveX object has quite limited capabilities compared to a normal image. Transparency for instance is nowhere to be found..

    Is there really no way to use a normal image inserted somewhere in a sheet before saving as a xlam file?

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    There is but then the code becomes much more complicated. You either have to copy the picture to a blank chart and export it from there, or you have to use API calls - search for Stephen Bullen's PastePicture code.

  7. #7
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Soo .. there's simply no way to assign an embedded image directly to a blank excel file without creating the image as a file first?

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Not to a header or footer, no.

  9. #9
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    bugger ... Thanks for making that clarification.

    I wonder if there's some way to work around this limitation then ..

    I think I'll open up a new ticket for a workaround since it's a completely different topic than adding a picture to the header / footer.

  10. #10
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Ok workaround 2 is still inside this scope of adding a picture to the header / footer.

    How about adding a filepicker, and simply have the image files picked seperately and saved in the macro. I know that's throwing quite a bit of the automatic stuff out the window, but I can't really see a better way right now, since I really need transparency in one of the images.

    My mind is completely bugged about this problem, so any help would be really appreciated.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Did you look at the PastePicture code?

  12. #12
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Yes I read it through, twice, and as far as I can tell, it's using only bitmaps, which again has a limitation on transparency. Please let me know if I'm wrong about that.

    I need to add a transparent image on all printed pages by the click of a customUI button. I know I'm close, since I've made the button and the xlam file (as shown in my initial code), but I'm still searching for a workaround to the absolute path in the macro referencing the images.

  13. #13
    Registered User
    Join Date
    10-15-2014
    Location
    Denmark
    MS-Off Ver
    2007
    Posts
    16

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    ah .. let me correct that. The initial code shows only the example of adding the logo files to the front page. It's actually the watermark code which is causing a headacke.

    Although it's very similar, it's still a bit different.

    Here it is:
    Please Login or Register  to view this content.
    Which part of the header or footer is used is actually irellevant, as long as it's not already used by something else. I find the CenterHeader is rarely used, and thus a perfect target for a watermark.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    No, it's not just bitmaps. For example with a transparent shape on a sheet and the PastePicture module in place:
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-28-2019
    Location
    California
    MS-Off Ver
    2016
    Posts
    2

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    Thank you romperstomper, your coding and instructions worked perfectly for my footer; however, I am looking to enhance it a bit to fit my needs. I found another VBA code that sets a different footer for the last page, but I can't seem to wrap my head around on how to merge the two codes.

    Sub test()
    Dim TotPages As Long
    TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    With ActiveSheet.PageSetup
    .CenterFooter = "Repeated Footer Filename"
    ActiveSheet.PrintOut From:=1, To:=TotPages - 1
    .CenterFooter = "Last Footer Filename"
    ActiveSheet.PrintOut From:=TotPages, To:=TotPages
    End With
    End Sub
    Last edited by andydavidbrown; 03-28-2019 at 02:07 AM.

  16. #16
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Embed Image in xlam and insert in header / footer using macro / VBA

    @andydavidbrown

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    In addition, please read and understand our forum rules. Pay particular attention to the rules regarding code tags which you have failed to employ here.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Embed Image in .xlam and Insert Using VBA
    By nicotob in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2014, 03:49 AM
  2. Embed image as resource and insert into new spreadsheet?
    By macro2014 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2014, 01:51 PM
  3. [SOLVED] Insert into Header/Footer List Box
    By HORNUNG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2006, 07:50 PM
  4. Image in Header/Footer
    By jwwceo in forum Excel General
    Replies: 2
    Last Post: 06-09-2006, 11:35 AM
  5. [SOLVED] insert the content of a cell into a header/footer
    By subscriptionsink in forum Excel General
    Replies: 1
    Last Post: 05-26-2005, 05:15 PM

Tags for this Thread

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