VBA: Copy Chart to PDF form

[underz]

Hello,

I have a current working script that takes cells in excel and pastes them into their matching cells in a PDF form. I would like to modify it slightly to include a chart that will be made, I've tried to manipulate the code a bit but I'm not clear with vba. I believe putting the chart from excel into image field in the PDF should work, but maybe that is also an issue.

Full code is here, the lines to look at I think are the two below. The "TMPhoneNumber" is referencing a named cell and then the same cell in PDF, so I just tried to name the chart and see if it worked and it does not.

sFileFields = sFileFields & "<</T(TMPhoneNumber)/V(" & Range("TMPhoneNumber").Value & ")>>" & vbCrLf
sFileFields = sFileFields & "<</T(Chart1)/V(" & Charts("Chart1").Select & ")>>" & vbCrLf

Option Explicit
#If VBA7 And Win64 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
 
Sub Main()
    Dim mainPDF As String
     
    Select Case LCase(Worksheets("Statement").Range("Language").Value)
    Case "english"
        mainPDF = "english.pdf"
    Case "French"
        mainPDF = "french.pdf"
    Case Else
        mainPDF = "error.pdf"
    End Select
    
         
    If Len(Dir(ThisWorkbook.Path & "\" & mainPDF)) = 0 Then
        MsgBox ThisWorkbook.Path & "\" & mainPDF, vbCritical, "Missing File - Macro Ending"
        Exit Sub
    End If
     
    MakeFDF mainPDF
End Sub
 
Public Sub MakeFDF(Optional PDF_FILE As String = "error.pdf")
    Dim sFileHeader As String
    Dim sFileFooter As String
    Dim sFileFields As String
    Dim sFileName As String
    Dim sTmp As String
    Dim lngFileNum As Long
     
     ' Builds string for contents of FDF file and then writes file to workbook folder.
    On Error GoTo ErrorHandler
     
    sFileHeader = "%FDF-1.2" & vbCrLf & _
    "%âãÏÓ" & vbCrLf & _
    "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
    "endobj" & vbCrLf & _
    "2 0 obj[" & vbCrLf
     
    sFileFooter = "]" & vbCrLf & _
    "endobj" & vbCrLf & _
    "trailer" & vbCrLf & _
    "<</Root 1 0 R>>" & vbCrLf & _
    "%%EO"
     
    sFileFields = sFileFields & "<</T(TM)/V(" & Range("TM").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Clinic)/V(" & Range("Clinic").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(ABP)/V(" & Range("ABP").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Date)/V(" & Range("Date").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(AspireLevel)/V(" & Range("AspireLevel").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(BaseRewards)/V(" & Range("BaseRewards").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(YTDPurchases)/V(" & Range("YTDPurchases").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(SigniaPurchases)/V(" & Range("SigniaPurchases").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(WidexPurchases)/V(" & Range("WidexPurchases").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(YTDBatteryPurchases)/V(" & Range("YTDBatteryPurchases").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(PurchasesRequiredToMaintainAspireLevel)/V(" & Range("PurchasesRequiredToMaintainAspireLevel").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(TrendforNextQualifyingYear)/V(" & Range("TrendforNextQualifyingYear").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(PurchasesRequiredtoAchieveNextAspireLevel)/V(" & Range("PurchasesRequiredtoAchieveNextAspireLevel").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(DaysRemainingInQualifyingYear)/V(" & Range("DaysRemainingInQualifyingYear").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(DaysRemainingInQuarter)/V(" & Range("DaysRemainingInQuarter").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Q1Reward)/V(" & Range("Q1Reward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Q2Reward)/V(" & Range("Q2Reward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Q3Reward)/V(" & Range("Q3Reward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(Q4Reward)/V(" & Range("Q4Reward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(QTDPurchases)/V(" & Range("QTDPurchases").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(PotentialQuarterlyReward)/V(" & Range("PotentialQuarterlyReward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(RemainingPurchasestoObtainQuarterlyReward)/V(" & Range("RemainingPurchasestoObtainQuarterlyReward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(RemainingUnitstoObtainQuarterlyReward)/V(" & Range("RemainingUnitstoObtainQuarterlyReward").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(TMName)/V(" & Range("TMName").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(TMEmail)/V(" & Range("TMEmail").Value & ")>>" & vbCrLf
    sFileFields = sFileFields & "<</T(TMPhoneNumber)/V(" & Range("TMPhoneNumber").Value & ")>>" & vbCrLf
    'sFileFields = sFileFields & "<</T(Chart1)/V(" & Charts("Chart1").Select & ")>>" & vbCrLf
'
    
   
    sTmp = sFileHeader & sFileFields & sFileFooter
     
     

     ' Write FDF file to disk
    If Len(Range("TM").Value) Then
        sFileName = Range("TM").Value
    Else: sFileName = "FDF_DEMOTEST"
    End If
    sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
    lngFileNum = FreeFile
    Open sFileName For Output As lngFileNum
    Print #lngFileNum, sTmp
    Close #lngFileNum
    DoEvents
     
     
     ' Open FDF file as PDF
    Shell "cmd /c " & """" & sFileName & """", vbHide
     
    Exit Sub
ErrorHandler:
    MsgBox "MakeFDF Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
End Sub

Appreciate your time and expertise!