excel vba picture in cell to header

lepaka · Mar 17th 2016

Hi

I am trying to insert a picture in a header of an Excel File. It is clear to me how to do it from a local file in the hard disk, what I can not figure it out is:

if the picture is one in a sheet from the same Workbook, as example:

Code

ActiveSheet.PageSetup.RightHeaderPicture = Sheets("datasheet").Shapes.Range(Array("myLogo"))


With ActiveSheet.PageSetup
     .RightHeader = "&G"
End With

can anyone help me to figure it out?

thanks in advance for your support

John_w · Mar 17th 2016

Re: excel vba picture in cell to header

Save the shape as an image file and assign that file as the header picture:

Code

Public Sub Setup_Page_Header()


    Dim printWorksheet As Worksheet
    Dim logoShape As Shape
    Dim tempImageFile As String
    
    'The worksheet on which the print page setup will apply
    
    Set printWorksheet = ThisWorkbook.ActiveSheet
    
    'The sheet location and name of shape to be used in page setup
    
    Set logoShape = ThisWorkbook.Sheets("datasheet").Shapes("myLogo")


    'Save the shape as a temporary image
    
    tempImageFile = Environ("temp") & "\image.jpg"
    Save_Object_As_Picture logoShape, tempImageFile
    
    With printWorksheet.PageSetup
        .RightHeaderPicture.Filename = tempImageFile
        .RightHeader = "&G"
    End With


    Kill tempImageFile
    
End Sub




'Based on http://www.jpsoftwaretech.com/export-excel-range-to-a-picture-file/


Private Sub Save_Object_As_Picture(saveObject As Object, imageFileName As String)


    'Save a picture of an object as a JPG/JPEG/GIF/PNG file
    
    'Arguments
    'saveObject     - any object in the CopyPicture method's 'Applies To' list, for example a Range or Shape
    'imageFileName  - the .gif, .jpg, or .png file name (including folder path if required) the picture will be saved as
    
    Dim temporaryChart As ChartObject
     
    Application.ScreenUpdating = False
    
    saveObject.CopyPicture xlScreen, xlPicture
    
    Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width + 1, saveObject.Height + 1)
    With temporaryChart
        .Border.LineStyle = xlLineStyleNone      'No border
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With
    
    Application.ScreenUpdating = True
    
    Set temporaryChart = Nothing
    
End Sub

Display More

lepaka · Mar 18th 2016

Re: excel vba picture in cell to header

What can I say :cheers:

It works perfectly :music:

thanks a lot, I was a bit lost on the way, just needed to think out the box to find the right way

thanks for you kind support

:ufo:

lepaka · Mar 18th 2016

Re: excel vba picture in cell to header

Just an additional note,

the Whorksheet as to be UNPROTECTED, if it is protected then will not work, nothing that can not be easly solved, just add in the code:

Code

Sheets("datasheet").Unprotect Password:=xlwin


    'code itself


    Sheets("datasheet").Protect Password:=xlwin

Timmmmm · Sep 24th 2017

Re: excel vba picture in cell to header

Hello everyone!

I hope it is ok to activate this old thread. I have the same problem as lepaka and the answer from John_w is working fine but only if I go through step by step. When the macro is running the picture does not appear in the header. What I know so far is that I have to give the macro some time to process the picture. I have tried to use DoEvents and Application.Wait but it did not work. Even when I tried both after each line! I am quite frustrated. Does anybody have a tip how the macro will work without me going through it step by step? Every help is appreciated a lot.

:sheep:

John_w · Sep 25th 2017

Re: excel vba picture in cell to header

Welcome to Ozgrid forums.

I have several workbooks which use the Save_Object_As_Picture routine, and the code worked correctly when running Excel 2007 on a relatively slow Windows XP machine. However since upgrading to Excel 2016 and a fast (Intel i7 6700) Windows 10 machine I've found that the image/picture created by the code is empty.

The solution is to Activate the temporary chart, like this:

Code

With temporaryChart
        .Activate                                'Required, otherwise image is blank with Excel 2016 or fast CPU (?)
        .Border.LineStyle = xlLineStyleNone      'No border
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With

If you tell us which version of Excel and Windows you're using and your computer's CPU this will help to pinpoint the reason why the Activate is needed.

Timmmmm · Sep 26th 2017

Re: excel vba picture in cell to header

Seriously, you are a legend! :congrats:

I am using the latest Excel and got an Intel i5. The Activate did the magic and the picture finally appears.
Thank you so much! :cheers:

Kannie · May 21st 2020

Hi. I am a new user and even newer to Excel and I'm hooked. I require exactly what you described a vba picture header from a logo in the same workbook without having to refer to an external location. I just can't figure out how to make it work for me. The logo I want in my header is inserted on a sheet named "Tables" in my workbook. How do I change the code to fit my scenario. I know this is an old thread but any assistance would go a long way. Thanks.

**royUK** · May 22nd 2020

Kannie, welcome to Ozgrid forum. You need to read the Forum Rules, one of which asks you to not "hijack" other threads with your question. Start your own Thread, refer to this one if you think it might help

excel vba picture in cell to header

Participate now!

Share