John Paul
John Paul

Reputation: 11

"Object reference not set to an instance of an object"- while creating pivot in Excel

I'm trying to create a pivot table by opening an Excel file from my drive.

But I'm receiving the error saying "Object reference not set to an instance of an object"

I have attached my code below

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim excelApplication As Microsoft.Office.Interop.Excel.Application = Nothing
        Dim excelWorkBook As Workbook = Nothing
        Dim targetSheet As Worksheet = Nothing
        Dim pivotTable As PivotTable = Nothing
        Dim pivotData As Ranges = Nothing
        Dim pivotDestination As Range = Nothing
        Dim co As PivotField = Nothing
        Dim pivotTableName As String = "Complexity"
        excelApplication.Workbooks.Open("E:\John\ccc.xlsx")
        pivotData = targetSheet.Range("Data!R1C1:R11460C9")
        pivotDestination = targetSheet.Range("Data!R1C11")
        excelWorkBook.PivotTableWizard( _
            XlPivotTableSourceType.xlDatabase, _
            pivotData, pivotDestination, pivotTableName, True, True, _
           True, True, , , False, False, XlOrder.xlDownThenOver, 0)
        With excelApplication.ActiveSheet.PivotTables("Complexity").PivotFields("Com")
        End With
        excelApplication.ActiveSheet.PivotTables("Complexity").AddDataField(excelApplication.ActiveSheet.PivotTables( _
            "Complexity").PivotFields("Per gram"), "Sum of Per gram", XlConsolidationFunction.xlSum)
        excelApplication.ActiveSheet.PivotTables("Complexity").AddDataField(excelApplication.ActiveSheet.PivotTables( _
            "Complexity").PivotFields("Oracle Per gram"), "Sum of Oracle Per gram", XlConsolidationFunction.xlSum)
        excelApplication.ActiveWorkbook.Close()
        excelApplication.Quit()
    End Sub

Upvotes: 1

Views: 3604

Answers (1)

Samy Vilar
Samy Vilar

Reputation: 11100

Dim excelApplication As Microsoft.Office.Interop.Excel.Application = Nothing ,excelApplication is Nothing ...

try this:

excelApplication = New Microsoft.Office.Interop.Excel.Application`
excelWorkBook = excelApplication.Workbooks.Open("E:\John\ccc.xlsx")
targetSheet = excelWorkBook.Sheets(0) 
' assuming that the sheet you want to work with is the first one.

Upvotes: 4

Related Questions