Assign image for footer to excel file and not an external path

Question

I am currently creating a template with a data validation to have a drop down with 5 different location name. On selection the appropriate address image is inserted into the excel footer. I plan to distribute the template but i need to save the images to the excel file because the current file path wont work for other users.

any help much appreciated

ActiveSheet.PageSetup.LeftFooterPicture.Filename = _
        "X:\Adrian\19.0 Templates\19.1 Test\UK Footer address"

Answer 1

You have several options instead of using local file paths:

1. Host the images on a shared network drive to which all users can be expected to have access. In this case, you'll need to use the UNC path name and not the local drive mapping.
2. Host the images on a publicly accessible website like imgur. This assumes the user will have internet access, but of course you could trap errors if needed. The syntax for adding an image this way is exactly the same, you just use the image URL for the Filename property.

    Sub f()
    With Sheet1.PageSetup.LeftFooterPicture
        .Filename = "https://i.stack.imgur.com/wc4Ew.jpg"
    End With
    Sheet1.PageSetup.LeftFooter = "&G"
    End Sub

3. A third alternative would be to use images embedded in the workbook, but this approach more complicated.

I'd recommend against option #3 in favor of one of the easier options above, but if you're inclined to pursue it then this is what I think you need to do.

1. Locate the picture within the workbook/worksheet (this shouldn't be too difficult, relying on its Name or other custom properties/metadata).
2. Copy the picture to the Clipboard (e.g., picture.Select: picture.Copy.
3. Dump the Clipboard contents to a location on the user's machine which is not as easy as it sounds, and will require some use of WinAPI calls (probably -- here is an older and quite verbose way of doing this and here is at least one easier, if not a little hacky way of doing this by exploing the ChartObject class). Further complexity abounds because you can't always assume write-access, presence/location of special folders, etc., and generally you also want to clean-up/remove the images afterwards so as to not clutter up the user's disk.

Answer 2

Like others have said, getting a picture into a header without a file path is tricky. Here's one I got to work:

Sub ExcelPicToWord()

    'optional - to set the height of the logo in inches
    Const logoHeight As Single = 0.5

    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")

    WordApp.Documents.Add
    
    With WordApp.ActiveDocument
    
        'copy from excel - wherever your logo is
        Sheet1.Shapes("footerLogo").Copy
        
        'paste into text body first
        .Range.Paste
        
        'select the pasted picture - i just have the one picture
        .InlineShapes(.InlineShapes.Count).Select
        
        WordApp.Selection.Cut
        
        With .Sections(1).Headers(wdHeaderFooterPrimary)
            
            'needs to be visible for header paste to work. otherwise, crashes
            WordApp.Visible = True
            
            'this finally pastes the pic into the header
            .Range.Paste
            
            WordApp.Visible = False
            
            'optional - sets the height of the logo, keeping proportions
            With .Range.InlineShapes(.Range.InlineShapes.Count)
            
                .width = Application.InchesToPoints(logoHeight) * (.width / .Height)

                .Height = Application.InchesToPoints(logoHeight)
            
            End With
        
        End With
        
        'because Application.CutCopyMode = False doesn't work here
        ClearClipBoard
    
    End With
    
    WordApp.Visible = True

End Sub

Private Sub ClearClipBoard()
    Dim oData   As New DataObject 'object to use the clipboard

    oData.SetText text:=Empty 'Clear
    oData.PutInClipboard 'take in the clipboard to empty it
End Sub

I got the ClearClipboard() code here.

Answer 3

I misunderstood the question originally, but leaving this answer in case it's useful for others in the future. For reference, I misunderstood the problem statement to be like:

I'm worried that if I create Header/Footer images from local file paths, that those images won't render when I distribute the file to someone else.

This is not how Header/Footer data works, here's a lengthy explanation.

I tested by adding a picture from a file path, and then deleting the picture from that location ("c:\debug\Leftfooter.png"). In other words, no file exists at the original file path. This should be essentially the same case as a co-worker with different drive mappings or permissions, etc.

Upon re-opening the file, the footer image is still visible to me, even though it no longer exists on my hard drive:

If the file no longer exists, how does the image persist in Excel Footer?

Now, the image file exists within the XLSX's .ZIP archive as xl\media\picture1.png. So the file has been incorporated as part of the Excel Workbook, and not as a reference to an external file object.

I observe that upon querying Sheet1.PageSetup.LeftFooterPicture.Filename, the return value is now simply "Leftfooter" (the original filename, without the path qualifier).

And digging a little deeper in to the underlying xml, the relationship appears to be mapped within the xl\drawings\_rels\vmlDrawing1.vml.rels which is connected to the xl\worksheets\sheet1.xml by the `"rID1" Relationship Id.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId1" 
      Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" 
      Target="../media/image1.png"/>