I get Run time error:Type mismatch for the following code:

Dim Field
Field="Gen8"
     '''' ============================================================================== EXCEL Sheet
     '==============Errors -Stacked Chart by Year and Week --ALL WEEKS
      ''''===================================================
     objExcel.ActiveWorkbook.Worksheets.Add
     SheetNumber = SheetNumber ' add adds in front so sheetnumber stays 1
     objExcel.Sheets(SheetNumber).Select
     objExcel.Sheets(SheetNumber).Activate

     objExcel.Sheets(SheetNumber).Name = "YRWk"
     SheetName = "SYS_Product_YRWeeks"
     '==============



     strSQLCustomers = "select isnull(AB.Week,D.Week_Num) AS YRWk,ISNULL(AB.UnCorrectable,0) as UE," & _
                        "isnull(AB.Correctable,0) as CE, isnull(AB.SYS_Product,'" & Field & "'" & _
                        ") as SYS_Product from AHS_Dates D Left Join (select * from P_tot where " & _
                        "SYS_Product = '" & Field & "'" & _
                        " ) AB on AB.Year_=D.Year_ and AB.Week=D.Week_Num order by YRWk"

     FetchData2.Open strSQLCustomers, openConnection, adOpenStatic, adLockReadOnly
     If FetchData2.RecordCount > 0 Then


         **objExcel.ActiveWorkbook.Connections.Add SheetName, "", _
             Array(Array( _
             "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=" & sServerIP & ";TimeOut=5000000; Trusted_Connection=Yes;Integrated Security=SSPI;" _
             ), Array("DATABASE=" & sDataBaseName & ";")), Array(strSQLCustomers), 2**

         objExcel.ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
             objExcel.ActiveWorkbook.Connections(SheetName), Version:= _
             xlPivotTableVersion14).CreatePivotTable TableDestination:=objExcel.Sheets(SheetNumber).Name & "!R3C7", _
             TableName:="PivotTable" & SheetNumber, DefaultVersion:=xlPivotTableVersion14

         Set ws = objExcel.ActiveWorkbook.Worksheets(objExcel.Sheets(SheetNumber).Name)

         objExcel.Cells(3, 7).Select
         ws.Shapes.AddChart.Select
         objExcel.ActiveWorkbook.ActiveChart.ChartType = xlAreaStacked
         objExcel.ActiveWorkbook.ActiveChart.SetSourceData Source:=ws.Range(objExcel.Sheets(SheetNumber).Name & "!$G$3:$I$20")

         With ws.PivotTables("PivotTable1").PivotFields("SYS_PRoduct")
             .Orientation = xlColumnField
             .Position = 1
         End With

         With ws.PivotTables("PivotTable1").PivotFields("YRWk")
             .Orientation = xlRowField
             .Position = 1
         End With
      '   With ws.PivotTables("PivotTable1").PivotFields("Year_")
       '    .Orientation = xlRowField
        '    .Position = 2
        ' End With

         objExcel.ActiveWorkbook.ActiveChart.ChartTitle.Text = " Errors by Week and Year -ALLWEEKS"


         ws.PivotTables("PivotTable1").AddDataField ws.PivotTables( _
             "PivotTable1").PivotFields("UE"), "Sum of UnCorrectable", xlSum
         ws.PivotTables("PivotTable1").AddDataField ws.PivotTables( _
             "PivotTable1").PivotFields("CE"), "Sum of Correctable", xlSum


     End If
     ''MsgBox (FetchData2.RecordCount)
     FetchData2.Close

I have used the same pivot chart + table in other slides.

The problem I think is the query length

My question: 1.Is there a better way for me to access the query results. Would appreciate the steps if any. 2.If I can make it a procedure how do I modify the pivot chart/table creation.

Thanks.

The query results with all 52 weeks:

Week       UE       CE       SYS_Product(or Field)
1        0        0        Gen8
2        0        0        Gen8
3        0        0        Gen8
4        0        0        Gen8
5        0        0        Gen8
6        0        0        Gen8