How can I transfer an image from a cell in Excel to the Word header (left) VBA-CodePudding

how to insert a picture which is in an Excel Celle (C1) by VBA in a new created Word document in the header without formatting(no cell color)?

logo.copy

Set objHeader = myDoc.Sections(1).Headers(1).Range
objHeader.Paste

thank you!

CodePudding user response：

Please, try the next way:

Sub InsertHeaderPict()
  'copy picture from Excel (open session, active sheet):
  Dim appExcel As Excel.Application, ws As Excel.Worksheet
  Set appExcel = GetObject(, "Excel.Application")
   Set ws = appExcel.ActiveWorkbook.ActiveSheet
   ws.Shapes("Picture 1").CopyPicture xlScreen, xlBitmap 'use here your real picture name

    'create a table of a row, 3 columns and paste the copied picture in its first cell:
    Dim oSec As Word.Section, rng As Range
     Set oSec = ActiveDocument.Sections(1)
     Set rng = oSec.Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).Range
     With rng
          .Tables.Add Range:=rng, NumRows:=1, NumColumns:=3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitWindow
          With .Tables(1)
              .Borders.InsideLineStyle = wdLineStyleNone
              .Borders.OutsideLineStyle = wdLineStyleNone
              .Rows.SetLeftIndent LeftIndent:=-37, RulerStyle:=wdAdjustNone
              .Cell(1, 1).Range.PasteSpecial
          End With
    End With
End Sub

There must be a picture in the active sheet of the Excel open session. Use this real picture name instead of "Picture 1" and run the code.

CodePudding user response：

I believe you have the right idea, just have to use copypicture and pastespecial

This is a snippet of my code that does basically the same thing I'm just using the shape object instead of a range.

Set reportHeader = masterReport.Sections.Item(1).Headers(wdHeaderFooterPrimary).Range
masterWorkbook.Worksheets("Template").Shapes("LogoSmall").CopyPicture
reportHeader.PasteSpecial

Thanks again to this post where I originally found this answer.