Export to Excel Error

[tmcrouse]

I am getting an error where my export to graph button on the Excel portion of my code cannot find the pivot table property in my Excel sheet. The error is run-time 1004 and it says unable to get the pivottables property of the worksheet class. Here is the code I have and I commented where the error is.

Private Sub cmdExport_Click()
Dim mydlg As Office.FileDialog
Set mydlg = Application.FileDialog(msoFileDialogSaveAs)
Dim myfile As String
With mydlg
    .AllowMultiSelect = False
    '.Filters.Clear
    .InitialFileName = CurrentProject.path
If msgbox("Do you want to export to Excel?", _
vbQuestion + vbYesNo) = vbYes Then
    Dim mytemp As String
    mytemp = Environ$("USERPROFILE") & "\Documents\QualityReport.xlsx"
    
    If Dir(mytemp) = "" Then
        msgbox "Template excel file is not present in the My Documents folder. So Export is not possible", vbOKOnly + vbInformation, "Excel"
        Set mydlg = Nothing
        Exit Sub
    End If
    
    If .Show = -1 Then
        myfile = .SelectedItems(1)
    Else
        msgbox "You must select a file to export", vbOKOnly + vbInformation, "Excel"
        Set mydlg = Nothing
        Exit Sub
    End If
    Dim myxls As New Excel.Application
    Set myxls = New Excel.Application
    Dim mybook As Excel.Workbook
    Set mybook = myxls.Workbooks.Open(mytemp)
    Dim mysheet As Excel.Worksheet
    Set mysheet = mybook.Worksheets(1)
    mysheet.Activate
    mysheet.Range("A2:T65000").Select
    myxls.Selection.ClearContents
    Dim k As Long, m As Long
    k = 1
    Dim myset As DAO.Recordset
    Set myset = CurrentDb.OpenRecordset("qryResults", dbOpenSnapshot)
    Dim myfld As DAO.Field
    With mysheet
        For m = 0 To myset.Fields.Count - 1
            .Cells(k, m + 1) = myset.Fields(m).name
        Next
        k = 2
    Do Until myset.EOF
       For m = 0 To myset.Fields.Count - 1
            .Cells(k, m + 1) = myset.Fields(m)
        Next
        .Rows(k).RowHeight = 18.75
        k = k + 1
        myset.MoveNext
    Loop
    End With
    If Dir(myfile) <> "" Then Kill myfile
    mybook.Sheets("PivotChart").Select
    mybook.ActiveSheet.ChartObjects("Chart 1").Activate
    With mybook.ActiveSheet.PivotTables("PivotTable1").PivotFields("PROG_NM")'ERRORS HERE
        .PivotItems("(blank)").Visible = False
    End With
    mybook.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    mybook.SaveAs myfile
    Set mysheet = Nothing
    myset.close
    Set myset = Nothing
    
    Set myfld = Nothing
    myxls.Visible = True
    Set myxls = Nothing
    
    
Else
    '.Filters.Add "PDF File", "*PDF"
    If .Show = -1 Then
        myfile = .SelectedItems(1)
    Else
        msgbox "You must select a file to export", vbOKOnly + vbInformation, "PDF"
        Set mydlg = Nothing
        Exit Sub
    End If
    
    DoCmd.OutputTo acOutputReport, "rptSearchQuality", acFormatPDF, myfile, True

End If
Set mydlg = Nothing
End With


End Sub