1

Was using this but it became quickly apparent today that expecting the picture to be in the same directory all the time is not a good plan.

With ActiveSheet.PageSetup.LeftHeaderPicture
    .filename = ThisWorkbook.path + "\pic.jpg"
    .Height = 275.25
    .Width = 195
End With

While the picture never changes, the text in the header changes daily so this needs to be generated automatically hence the need for inserting it with VBA as I'm putting the picture in the header as well as other text every time the relevant data changes.

4
  • Little confused, what is your question?
    – 0m3r
    Mar 16, 2016 at 3:55
  • Put the picture in the excel file, fixed, without the vba: you said it never changes...
    – Hastur
    Mar 16, 2016 at 5:43
  • @Om3r, I'd like to store a picture on a spare worksheet and insert it into the header with VBA.
    – click here
    Mar 16, 2016 at 11:58
  • @Hastur, that won't work. The header isn't just a picture. It's dynamically generated text too.
    – click here
    Mar 16, 2016 at 20:26

1 Answer 1

2

You already have code to import an external picture file and import it into a header. Since you have an embedded picture, we can export it and then use the code you have. Say we have:

enter image description here

This code will:

  • create a Chart Object on the worksheet
  • copy the picture to the chart
  • export the chart to an external file
  • delete the chart

The code:

Sub SaveTheBaby()
    Dim chrt As Chart, MyPic As Shape
    Set MyPic = ActiveSheet.Shapes(1)

    ActiveSheet.Shapes.AddChart
    Set chrt = ActiveSheet.ChartObjects(1).Chart

    MyPic.Select
    MyPic.Copy
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Paste
    chrt.Export Filename:="baby.jpg"
    ActiveSheet.ChartObjects(1).Delete
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.