Context: I'm writing a list excel doc that has two sheets (Equipment List and List Inputs). I want the user to be able to input the general document info (name and date etc.) on the Inputs sheet, and this data to be captured as a snapshot and inserted into the left, centre and right header boxes. It has two different headers - one for the first page and one for every page after.
I wrote the code on excel 2013 32-bit (and it worked), only to realise that it doesn't work on any 64-bit machines. When I say it doesn't work I mean the images generated don't appear in the print preview in the header.
I'm a VBA novice and I really don't know where this code is failing, however I think it might be at the .Chart.Paste step.
I have looked into other threads on 32 to 64 bit conversion but they all reference PtrSafe which I don't think is relevant to my code.
How can I update my code to run on 64-bit?
Any advice is much appreciated. Thanks.
Workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Module1.AddHeaderToAll_FromCurrentSheet
End Sub
Private Sub Workbook_Open()
End Sub
General (Module 1) Save_Object_As_Picture:
Sub AddHeaderToAll_FromCurrentSheet()
Dim ws As Worksheet
Dim tempFilePath As String
Dim tempPFilePath As String
Dim tempTBFilePath As String
Dim tempPic As Shape
Dim tempPrimeroPic As Shape
Dim tempTiBlkPic As Shape
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set tempPic = ThisWorkbook.Sheets("List Inputs").Shapes("RevBlkPic")
Set tempPrimeroPic = ThisWorkbook.Sheets("List Inputs").Shapes("PrimeroPic")
Set tempTiBlkPic = ThisWorkbook.Sheets("List Inputs").Shapes("TiBlkPic")
tempFilePath = Environ("temp") & "\image.jpg"
Save_Object_As_Picture tempPic, tempFilePath
tempPFilePath = Environ("temp") & "\image2.jpg"
Save_Object_As_Picture tempPrimeroPic, tempPFilePath
tempTBFilePath = Environ("temp") & "\image3.jpg"
Save_Object_As_Picture tempTiBlkPic, tempTBFilePath
For Each ws In ActiveWorkbook.Worksheets
'ws.PageSetup.FirstPage.CenterHeaderPicture
'With ActiveSheet.PageSetup.DifferentFirstPageHeaderFooter = True
'First Page Headers
ws.PageSetup.DifferentFirstPageHeaderFooter = True
ws.PageSetup.FirstPage.CenterHeader.Picture.Filename = tempFilePath
ws.PageSetup.FirstPage.CenterHeader.Text = "&G"
ws.PageSetup.FirstPage.RightHeader.Picture.Filename = tempPFilePath
ws.PageSetup.FirstPage.RightHeader.Text = "&G"
ws.PageSetup.FirstPage.LeftHeader.Picture.Filename = tempTBFilePath
ws.PageSetup.FirstPage.LeftHeader.Text = "&G"
'Different Page Headers
ws.PageSetup.RightHeaderPicture.Filename = tempPFilePath
ws.PageSetup.RightHeader = "&G"
ws.PageSetup.LeftHeaderPicture.Filename = tempTBFilePath
ws.PageSetup.LeftHeader = "&G"
ws.PageSetup.CenterHeaderPicture.Filename = tempFilePath
ws.PageSetup.CenterHeader = ""
' ws.PageSetup.RightHeaderPicture.Filename = tempPFilePath
' ws.PageSetup.RightHeader = "&G"
' ws.PageSetup.LeftHeaderPicture.Filename = tempTBFilePath
' ws.PageSetup.LeftHeader = "&G"
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
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