I have found the following two VBA codes that individually address parts of my problem, but I need help merging the two. The first code takes an XL stored image (separate sheet) and inserts it in the footer. I need this because if I simply reference a file on the computer, when I copy the XL to another computer, the new computer doesn't have the file.
Sub AddPicsToFooter()
Dim strPicPath As String
Dim strImageName As String
Dim strImageSheetName As String
' change this to match your image control name
strImageName = "Image1"
' change this to match the name of the sheet with the image on
strImageSheetName = "SheetName"
strPicPath = ThisWorkbook.Path & Application.PathSeparator & "temp.bmp"
' delete pic file if it already exists
If Dir(strPicPath) <> vbNullString Then Kill strPicPath
' save picture to file
SavePicture ThisWorkbook.Worksheets(strImageSheetName).OLEObjects(strImageName).Object.Picture, strPicPath
' now assign to active sheet footer
With ActiveSheet.PageSetup
.CenterFooterPicture.Filename = strPicPath
.CenterFooter = "&G"
End With
' remove temporary file
Kill strPicPath
End Sub
The second code sets one image for a standard repeated footer and another image for a unique last page footer.
Sub test()
Dim TotPages As Long
TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.CenterFooter = "Image1 (Repeated Footer)"
ActiveSheet.PrintOut From:=1, To:=TotPages - 1
.CenterFooter = "Image2 (Last Page Footer)"
ActiveSheet.PrintOut From:=TotPages, To:=TotPages
End With
End Sub
Bookmarks