Type Mismatch using VBScript to create Pivot Table/Chart
- by Rodricks
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
