0

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
Thomas
  • 1
  • 1
  • Do `temporaryChart.Activate` before `With temporaryChart`. See https://stackoverflow.com/questions/42091390/vba-range-to-jpg-picture/42092375#42092375. – Axel Richter Sep 12 '18 at 06:24
  • @AxelRichter Thanks a million, you've solved my problem! – Thomas Sep 12 '18 at 06:47

0 Answers0