none
Pivot table not working

    Question

  • Seems like my code creates an error when gathering data and putting into the pivot table cache. not sure what needs to be done. I am trying to put the values from sheet1 to a sheet called PIVOTTABLE. I tried adding data to a new sheet, then copying it.
     ' first get range of cells from sheet 1 that will be used by pivot
            Dim xlRange As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:M" & rowLength)
    
            ' create second sheet
            If oExcel.Application.Sheets.Count() < 2 Then
                oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
            Else
                oSheet = oExcel.Worksheets(2)
            End If
            oSheet.Name = "Pivot Table"
    
            ' specify first cell for pivot table on the second sheet
            Dim xlRange2 As Excel.Range = Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")
    
            ' Create pivot cache and table
            Dim ptCache As Excel.PivotCache = Globals.ThisAddIn.Application.Sheets("Sheet1").PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
            Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Summary")
    
            ' create Pivot Field, note that pivot field name is the same as column name in sheet 1
            Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                .Function = Excel.XlConsolidationFunction.xlSum
                .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it - 
                ' this field name cannot be the same as therefore that space
                ' also it cannot be empty
    
                '' add another field
                'ptField = ptTable.PivotFields("Year")
                'With ptField
                '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                '    .Function = Excel.XlConsolidationFunction.xlMax
                '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
                'End With
    
                ' add column
                ptField = ptTable.PivotFields("First Name")
                With ptField
                    .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                    .Name = " "
                End With
    
            End With
            ' add grouping - again I don't need this in my example, this is just to show how to do it
            'oSheet.Range("C5").Group(1, 20, 40)

    .
    Monday, March 05, 2018 5:13 PM

Answers

  • Hello jbrotto,

    You create a new Excel Application instance and open a new workbook and put the data in the new workbook as data source.

    However, since first workbook and second workbook are not in same Excel Application instance, you could not create pivot table in first workbook based on the data in second workbook

    If you insist on using second workbook as data source, please not create new excel application instance but create create the second workbook in current excel application instance.

    oExcel = CreateObject("Excel.Application")
    >>
    oExcel = Globals.ThisAddIn.Application

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jbrotto Thursday, July 19, 2018 7:40 PM
    Thursday, March 15, 2018 8:33 AM
    Moderator

All replies

  • Hello jbrotto,

    >>oSheet.Name = "Pivot Table"

    >>Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")

    Although excel sheet name does not case sensitive, you could not leave out the space character of it. There is no sheet named "PIVOTTABLE". Please change "PIVOTTABLE" to "PIVOT TABLE" or change "Pivot Table" to "PivotTable".

    Besides, if it still has other issue, please share the source file so we could try to use it to reproduce your issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 06, 2018 2:34 AM
    Moderator
  • When I try to create the pivot table I get a System.MisingMemberException.

    The full source code for the project:

    Imports Microsoft.Office.Tools.Ribbon
    
    Public Class SunriseRibbon
        Dim NumberOfRow As Integer = 0
        Dim rowLength As Integer = 1
        Dim testForNewBlank As String = "1"
        Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
    
    
            'Add the sheets for the data
            Dim rng As Excel.Sheets = Globals.ThisAddIn.Application.Sheets
            rng.Add(, Globals.ThisAddIn.Application.ActiveSheet, 3, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)
    
            'rename the sheets for the data
            'under the assumption that there is only Sheet1 from sunrise we add the rest
            Try
                Dim sheetRename As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
                sheetRename.Name = "INV1"
                Dim sheetRename2 As Excel.Worksheet = Globals.ThisAddIn.Application.Sheets("Sheet2")
                sheetRename2.Name = "OINV"
                Dim sheetRename3 As Excel.Worksheet = Globals.ThisAddIn.Application.Sheets("Sheet3")
                sheetRename3.Name = "PIVOTTABLE"
    
            Catch ex As Exception
                tabCrashed()
            End Try
    
            sheet1Stuff()
    
            sheet2Stuff()
    
            sheet3Stuff()
    
            sheet4Stuff()
            instructionMessage()
            'insert instructions
        End Sub
        Sub instructionMessage()
            'Message with instructions
            MsgBox("Sheet1 is source data" & vbNewLine & "OINV is data for oinv table or invoice header details" & vbNewLine & "INV1 is data for inv1 table or invoice line details" & vbNewLine & "Sheet4 will be summary details.")
        End Sub
        Sub sheet1Stuff()
            Dim storeNum, scanUPCs, scanUnits As Integer
            Dim storeSubTotal, storeTax, storeTotal As Double
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:A1")
    
            'Delete the top two useless rows
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A2:M2")
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
            'Rename the new top row to have labels
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1")
            Sheet1Rng.Value2 = "Random Text"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("B1")
            Sheet1Rng.Value2 = "Some Number"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("C1")
            Sheet1Rng.Value2 = "Date"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1")
            Sheet1Rng.Value2 = "Store Number"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("E1")
            Sheet1Rng.Value2 = "UPC"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("F1")
            Sheet1Rng.Value2 = "Category"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("G1")
            Sheet1Rng.Value2 = "Quantity"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("H1")
            Sheet1Rng.Value2 = "Retail"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("I1")
            Sheet1Rng.Value2 = "Wholesale"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("J1")
            Sheet1Rng.Value2 = "Sold"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("K1")
            Sheet1Rng.Value2 = "Taxes"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("L1")
            Sheet1Rng.Value2 = "Total"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("M1")
            Sheet1Rng.Value2 = "Description"
    
            'sort the table
            'Dim sortSheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1")
            'sortSheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1").Sort(1, Excel.XlSortOrder.xlAscending, 2, Excel.XlSortOrder.xlAscending, 3, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes,,,,,,,,)
            'To fix and work on 
    
            'sort method in sheet1
            Dim maxrange As Integer = 0
            Dim testForBlank As String = "1"
            Dim testRange As String = "A1"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1")
            Try
                Do Until testForBlank = ""
                    maxrange = maxrange + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testRange)
                    testForBlank = Sheet1Rng.Value2.ToString
                    testRange = "A" & maxrange.ToString
                Loop
            Catch ex As Exception
                tabCrashed()
            End Try
    
            Dim maxSortRange As String = "M" & (maxrange - 1).ToString
            Dim Fruits As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1", maxSortRange)
            Fruits.Sort(
                Key1:=Fruits.Columns(4), Order1:=Excel.XlSortOrder.xlAscending,
                Key2:=Fruits.Columns(4), Order2:=Excel.XlSortOrder.xlAscending,
                Orientation:=Excel.XlSortOrientation.xlSortColumns,
                Header:=Excel.XlYesNoGuess.xlYes,
                SortMethod:=Excel.XlSortMethod.xlPinYin,
                DataOption1:=Excel.XlSortDataOption.xlSortNormal,
                DataOption2:=Excel.XlSortDataOption.xlSortNormal,
                DataOption3:=Excel.XlSortDataOption.xlSortNormal)
    
            Label1.Label = storeNum
    
            'Delete the top sorted useless rows
            'declare its own variables
            Dim topRowToDelete As String
            Dim topNumberToDelete As Integer
            Dim testForNotBlank As String = "ABCDEFG"
            'E2 is used as will look for blanks
            Dim testNotBlankRange As String = "E2"
            Dim newTestRange As String = "A2"
            maxrange = 1
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNotBlankRange)
            'make a loop to find a non blank row starting at 2nd row going downwards
            Try
                Do Until testForNotBlank = ""
                    maxrange = maxrange + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNotBlankRange)
                    testForNotBlank = Sheet1Rng.Value2.ToString
                    testNotBlankRange = "E" & maxrange.ToString
                Loop
            Catch ex As Exception
    
            End Try
            'correction on number of row to delete as started on A2
            topNumberToDelete = maxrange - 1
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A2:M" & topNumberToDelete.ToString)
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
    
            'buggy range as it goes up? and row to delete is 0
            'declare variables for this one as to fix bug?
            Dim startRowToDelete, endRowToDelete As Integer
            Dim newMaxRange As Integer = 0
    
            Dim testForNewBlank As String = "1"
            Dim testNewRange As String = "C1"
    
            Try
                Do Until testForNewBlank = ""
                    startRowToDelete = startRowToDelete + 1
                    testNewRange = "C" & (startRowToDelete).ToString
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNewRange)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
    
                tabCrashed()
            Catch ex As Exception
    
            End Try
    
            testForNewBlank = 1
            testNewRange = "A1"
            Try
    
                Do Until testForNewBlank = ""
                    endRowToDelete = endRowToDelete + 1
                    testNewRange = "A" & (endRowToDelete).ToString
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNewRange)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
            Catch ex As Exception
    
            End Try
    
            'rows at the end to delete
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A" & startRowToDelete.ToString() & ":M" & endRowToDelete.ToString())
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
        End Sub
        Sub sheet2Stuff()
            Dim Sheet2Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("OINV").Range("A1:A1")
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("A1")
            With Sheet2Rng
                .Value2 = "CardCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("A2")
            With Sheet2Rng
                .Value2 = "CardCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("B1")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("B2")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C1")
            With Sheet2Rng
                .Value2 = "NumAtCard"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C2")
            With Sheet2Rng
                .Value2 = "NumAtCard"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D1")
            With Sheet2Rng
                .Value2 = "DOCDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D2")
            With Sheet2Rng
                .Value2 = "DOCDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'dates
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D3")
            'Sheet2Rng.Value2 = 
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E1")
            With Sheet2Rng
                .Value2 = "DOCDUEDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E2")
            With Sheet2Rng
                .Value2 = "DOCDUEDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'store # information before inserted into sheet
            Dim storeList As New List(Of String)()
            Dim StoreRowLength As Integer = 1
            Dim testForNewBlankStore As String = "1"
            'store # copy from sheet1
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
            Try
    
                Do Until testForNewBlankStore = ""
                    StoreRowLength = StoreRowLength + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D" & StoreRowLength)
                    testForNewBlankStore = Sheet1Rng.Value2.ToString
                    storeList.Add(testForNewBlankStore.ToString)
                    'For Each number As String In storeList.Distinct
                    '    MsgBox(number, MsgBoxStyle.MsgBoxHelp, "test add")
                    'Next
    
                Loop
            Catch ex As Exception
    
            End Try
    
    
    
            'Create PO loop
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C3")
            'create a class for this dialog box
            Dim someDialog As New SunriseDialog
            Dim POdetails As String
            'show the dialog box
            someDialog.ShowDialog()
            POdetails = someDialog.TextBox1.Text.ToString
            If someDialog.DialogResult = Windows.Forms.DialogResult.OK Then
                POdetails = someDialog.TextBox1.Text
                Me.Label1.Label = POdetails
            End If
            With Sheet2Rng
                .Value2 = POdetails.Trim.ToString
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'loop with date in ISO format in vb
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D3")
            Sheet2Rng.Value2 = Date.Now().Year() & Date.Now().Month() & Date.Now().Day()
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E3")
            Sheet2Rng.Value2 = Date.Now().Year() & Date.Now().Month() & Date.Now().Day()
        End Sub
        Public Sub sheet3Stuff()
            Dim Sheet2Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("INV1").Range("A1:A1")
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A1")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A2")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B1")
            With Sheet2Rng
                .Value2 = "ItemCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B2")
            With Sheet2Rng
                .Value2 = "ItemCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C1")
            With Sheet2Rng
                .Value2 = "Quantity"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C2")
            With Sheet2Rng
                .Value2 = "Quantity"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
    
            'variables for gather row data
            rowLength = 1
            Dim testForNewBlank As String = "1"
            'store # copy from sheet1
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
            Try
    
                Do Until testForNewBlank = ""
                    rowLength = rowLength + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D" & rowLength)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
            Catch ex As Exception
    
            End Try
    
            Try
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2:D" & rowLength + 1)
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
                Sheet1Rng.Copy(Sheet2Rng)
    
                'upc copy from sheet1
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B3")
    
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("E2:E" & rowLength + 1)
                Sheet1Rng.Copy(Sheet2Rng)
                'quantity copy from sheet1
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C3")
    
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("G2:G" & rowLength + 1)
                Sheet1Rng.Copy(Sheet2Rng)
            Catch ex As Exception
    
            End Try
    
        End Sub
        Sub sheet4Stuff()
            '        Grab the raw data
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:M" & rowLength)
            Dim Sheet4Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")
    
            Dim oExcel As Object
            oExcel = CreateObject("Excel.Application")
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
    
            oBook = oExcel.Workbooks.Add
            oSheet = oExcel.Worksheets(1)
    
            oSheet.Name = "PIVOTTABLE"
            oSheet.Range("A1").Value = "First Name"
            oSheet.Range("B1").Value = "Year"
            oSheet.Range("C1").Value = "Salary"
    
            oSheet.Range("A2").Value = "Frank"
            oSheet.Range("B2").Value = "2012"
            oSheet.Range("C2").Value = "30000"
    
            oSheet.Range("A3").Value = "Frank"
            oSheet.Range("B3").Value = "2011"
            oSheet.Range("C3").Value = "25000"
    
            oSheet.Range("A4").Value = "Ann"
            oSheet.Range("B4").Value = "2011"
            oSheet.Range("C4").Value = "55000"
    
            oSheet.Range("A5").Value = "Ann"
            oSheet.Range("B5").Value = "2012"
            oSheet.Range("C5").Value = "35000"
    
            oSheet.Range("A6").Value = "Ann"
            oSheet.Range("B6").Value = "2010"
            oSheet.Range("C6").Value = "35000"
    
            ' OK, at this point we have Excel file with 1 sheet with data
            ' Now let's create pivot table
    
            ' first get range of cells from sheet 1 that will be used by pivot
            Dim xlRange As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:M" & rowLength)
    
            ' create second sheet
            If oExcel.Application.Sheets.Count() < 2 Then
                oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
            Else
                oSheet = oExcel.Worksheets(2)
            End If
            oSheet.Name = "Pivot Table"
    
            ' specify first cell for pivot table on the second sheet
            Dim xlRange2 As Excel.Range = Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")
    
            ' Create pivot cache and table
            Dim ptCache As Excel.PivotCache = Globals.ThisAddIn.Application.Sheets("Sheet1").PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
            'Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
    
            Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Summary")
    
            ' create Pivot Field, note that pivot field name is the same as column name in sheet 1
            Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                .Function = Excel.XlConsolidationFunction.xlSum
                .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it - 
                ' this field name cannot be the same as therefore that space
                ' also it cannot be empty
    
                '' add another field
                'ptField = ptTable.PivotFields("Year")
                'With ptField
                '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                '    .Function = Excel.XlConsolidationFunction.xlMax
                '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
                'End With
    
                ' add column
                ptField = ptTable.PivotFields("First Name")
                With ptField
                    .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                    .Name = " "
                End With
    
            End With
            ' add grouping - again I don't need this in my example, this is just to show how to do it
            'oSheet.Range("C5").Group(1, 20, 40)
    
    
    
        End Sub
        Sub tabCrashed()
            MsgBox("This tab has performed an operation that made it crash in mid execution. Please perform task manually or contact Jonathan Brotto", MsgBoxStyle.Information, "Crashed")
        End Sub
    End Class
    

    Tuesday, March 06, 2018 4:54 PM
  • Hello jbrotto,

    It is not good idea to just put some code to let help you.

    What's your purpose? What's the issue you got? Which line? I could not even compile your code since there is no SunriseDialog class in our project.

    I thought the code could generate some data for testing. However, after commenting out the code related SunriseDialog and forcing code to run, I could not see any data in Sheet1. We could not use empty data to generate a pivot table. If you have a source workbook, please share it.

    Anyway, for the error System.MisingMemberException, PivotCache is not a member of WorkSheet but  a member of Workbook. So you need change adding pivot cache like this.

    Dim ptCache As Excel.PivotCache = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
           

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, March 07, 2018 9:18 AM
    Moderator
  • Thanks. It seems there is progress but now the issue with the creation of the pivot table. I get this error. 'The remote procedure call failed'

    Same source code. Seems I can't attach the file

    Wednesday, March 07, 2018 2:39 PM
  • Hello jbrotto,

    You could share the file via Cloud Storage, such as One Drive, and then put the link address here.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 08, 2018 1:55 AM
    Moderator
  • Hello jbrotto,

    >>Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Summary")

    We assume that you are operating WorkbookA("Q-CMR VENDOR REPORT - NO XUNIDIS - February 25 to March 3  2018.xls" and then in sheet4Stuff sub, you opened another WorkbookB(oBook).

    ptCache and xlRange2 are both objects created in WorkbookA. However you used them to add pivottable in WorkbookB, so it get error. I would suggest you use the object with once voice.

    Here is a simply code to do all the work in WorkbookB.

      Sub sheet4Stuff()
            '        Grab the raw data
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:M" & rowLength)
            Dim Sheet4Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")
    
            Dim oExcel As Object
            oExcel = CreateObject("Excel.Application")
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
    
            oBook = oExcel.Workbooks.Add
            oSheet = oExcel.Worksheets(1)
    
            oSheet.Name = "PIVOTTABLE"
            oSheet.Range("A1").Value = "First Name"
            oSheet.Range("B1").Value = "Year"
            oSheet.Range("C1").Value = "Salary"
    
            oSheet.Range("A2").Value = "Frank"
            oSheet.Range("B2").Value = "2012"
            oSheet.Range("C2").Value = "30000"
    
            oSheet.Range("A3").Value = "Frank"
            oSheet.Range("B3").Value = "2011"
            oSheet.Range("C3").Value = "25000"
    
            oSheet.Range("A4").Value = "Ann"
            oSheet.Range("B4").Value = "2011"
            oSheet.Range("C4").Value = "55000"
    
            oSheet.Range("A5").Value = "Ann"
            oSheet.Range("B5").Value = "2012"
            oSheet.Range("C5").Value = "35000"
    
            oSheet.Range("A6").Value = "Ann"
            oSheet.Range("B6").Value = "2010"
            oSheet.Range("C6").Value = "35000"
    
            oExcel.Visible = True
            Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, oSheet.Range("A1:C6"))
    
            If oExcel.Application.Sheets.Count() < 2 Then
                oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
            Else
                oSheet = oExcel.Worksheets(2)
            End If
            oSheet.Name = "Pivot Table"
    
            Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=oSheet.Range("A1"))
    
            Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                .Function = Excel.XlConsolidationFunction.xlSum
                .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it - 
                ' this field name cannot be the same as therefore that space
                ' also it cannot be empty
    
                '' add another field
                'ptField = ptTable.PivotFields("Year")
                'With ptField
                '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                '    .Function = Excel.XlConsolidationFunction.xlMax
                '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
                'End With
    
                ' add column
                ptField = ptTable.PivotFields("First Name")
                With ptField
                    .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                    .Name = " "
                End With
    
            End With
            ' add grouping - again I don't need this in my example, this is just to show how to do it
            'oSheet.Range("C5").Group(1, 20, 40)
    
    
    
        End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 09, 2018 2:48 AM
    Moderator
  • Hello jbrotto,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 5:48 AM
    Moderator
  • Thanks is possible to have everything in the first workbook?
    Wednesday, March 14, 2018 8:56 PM
  • Hello jbrotto,

    You create a new Excel Application instance and open a new workbook and put the data in the new workbook as data source.

    However, since first workbook and second workbook are not in same Excel Application instance, you could not create pivot table in first workbook based on the data in second workbook

    If you insist on using second workbook as data source, please not create new excel application instance but create create the second workbook in current excel application instance.

    oExcel = CreateObject("Excel.Application")
    >>
    oExcel = Globals.ThisAddIn.Application

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jbrotto Thursday, July 19, 2018 7:40 PM
    Thursday, March 15, 2018 8:33 AM
    Moderator
  • Thanks but it seems I am still making a new workbook and I only want one.
    Imports Microsoft.Office.Tools.Ribbon
    
    Public Class SunriseRibbon
        Dim NumberOfRow As Integer = 0
        Dim rowLength As Integer = 1
        Dim testForNewBlank As String = "1"
        Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
    
    
            'Add the sheets for the data
            Dim rng As Excel.Sheets = Globals.ThisAddIn.Application.Sheets
            rng.Add(, Globals.ThisAddIn.Application.ActiveSheet, 3, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)
    
            'rename the sheets for the data
            'under the assumption that there is only Sheet1 from sunrise we add the rest
            Try
                Dim sheetRename As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
                sheetRename.Name = "INV1"
                Dim sheetRename2 As Excel.Worksheet = Globals.ThisAddIn.Application.Sheets("Sheet2")
                sheetRename2.Name = "OINV"
                Dim sheetRename3 As Excel.Worksheet = Globals.ThisAddIn.Application.Sheets("Sheet3")
                sheetRename3.Name = "PIVOTTABLE"
    
            Catch ex As Exception
                tabCrashed()
            End Try
    
            sheet1Stuff()
    
            sheet2Stuff()
    
            sheet3Stuff()
    
            sheet4Stuff()
            instructionMessage()
            'insert instructions
        End Sub
        Sub instructionMessage()
            'Message with instructions
            MsgBox("Sheet1 is source data" & vbNewLine & "OINV is data for oinv table or invoice header details" & vbNewLine & "INV1 is data for inv1 table or invoice line details" & vbNewLine & "Sheet4 will be summary details.")
        End Sub
        Sub sheet1Stuff()
            Dim storeNum, scanUPCs, scanUnits As Integer
            Dim storeSubTotal, storeTax, storeTotal As Double
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:A1")
    
            'Delete the top two useless rows
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A2:M2")
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
            'Rename the new top row to have labels
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1")
            Sheet1Rng.Value2 = "Random Text"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("B1")
            Sheet1Rng.Value2 = "Some Number"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("C1")
            Sheet1Rng.Value2 = "Date"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1")
            Sheet1Rng.Value2 = "Store Number"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("E1")
            Sheet1Rng.Value2 = "UPC"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("F1")
            Sheet1Rng.Value2 = "Category"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("G1")
            Sheet1Rng.Value2 = "Quantity"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("H1")
            Sheet1Rng.Value2 = "Retail"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("I1")
            Sheet1Rng.Value2 = "Wholesale"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("J1")
            Sheet1Rng.Value2 = "Sold"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("K1")
            Sheet1Rng.Value2 = "Taxes"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("L1")
            Sheet1Rng.Value2 = "Total"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("M1")
            Sheet1Rng.Value2 = "Description"
    
            'sort the table
            'Dim sortSheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1")
            'sortSheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D1").Sort(1, Excel.XlSortOrder.xlAscending, 2, Excel.XlSortOrder.xlAscending, 3, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes,,,,,,,,)
            'To fix and work on 
    
            'sort method in sheet1
            Dim maxrange As Integer = 0
            Dim testForBlank As String = "1"
            Dim testRange As String = "A1"
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1")
            Try
                Do Until testForBlank = ""
                    maxrange = maxrange + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testRange)
                    testForBlank = Sheet1Rng.Value2.ToString
                    testRange = "A" & maxrange.ToString
                Loop
            Catch ex As Exception
                tabCrashed()
            End Try
    
            Dim maxSortRange As String = "M" & (maxrange - 1).ToString
            Dim Fruits As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1", maxSortRange)
            Fruits.Sort(
                Key1:=Fruits.Columns(4), Order1:=Excel.XlSortOrder.xlAscending,
                Key2:=Fruits.Columns(4), Order2:=Excel.XlSortOrder.xlAscending,
                Orientation:=Excel.XlSortOrientation.xlSortColumns,
                Header:=Excel.XlYesNoGuess.xlYes,
                SortMethod:=Excel.XlSortMethod.xlPinYin,
                DataOption1:=Excel.XlSortDataOption.xlSortNormal,
                DataOption2:=Excel.XlSortDataOption.xlSortNormal,
                DataOption3:=Excel.XlSortDataOption.xlSortNormal)
    
            Label1.Label = storeNum
    
            'Delete the top sorted useless rows
            'declare its own variables
            Dim topRowToDelete As String
            Dim topNumberToDelete As Integer
            Dim testForNotBlank As String = "ABCDEFG"
            'E2 is used as will look for blanks
            Dim testNotBlankRange As String = "E2"
            Dim newTestRange As String = "A2"
            maxrange = 1
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNotBlankRange)
            'make a loop to find a non blank row starting at 2nd row going downwards
            Try
                Do Until testForNotBlank = ""
                    maxrange = maxrange + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNotBlankRange)
                    testForNotBlank = Sheet1Rng.Value2.ToString
                    testNotBlankRange = "E" & maxrange.ToString
                Loop
            Catch ex As Exception
    
            End Try
            'correction on number of row to delete as started on A2
            topNumberToDelete = maxrange - 1
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A2:M" & topNumberToDelete.ToString)
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
    
            'buggy range as it goes up? and row to delete is 0
            'declare variables for this one as to fix bug?
            Dim startRowToDelete, endRowToDelete As Integer
            Dim newMaxRange As Integer = 0
    
            Dim testForNewBlank As String = "1"
            Dim testNewRange As String = "C1"
    
            Try
                Do Until testForNewBlank = ""
                    startRowToDelete = startRowToDelete + 1
                    testNewRange = "C" & (startRowToDelete).ToString
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNewRange)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
    
                tabCrashed()
            Catch ex As Exception
    
            End Try
    
            testForNewBlank = 1
            testNewRange = "A1"
            Try
    
                Do Until testForNewBlank = ""
                    endRowToDelete = endRowToDelete + 1
                    testNewRange = "A" & (endRowToDelete).ToString
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range(testNewRange)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
            Catch ex As Exception
    
            End Try
    
            'rows at the end to delete
    
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A" & startRowToDelete.ToString() & ":M" & endRowToDelete.ToString())
            Sheet1Rng.EntireRow.Delete(Excel.XlDirection.xlUp)
        End Sub
        Sub sheet2Stuff()
            Dim Sheet2Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("OINV").Range("A1:A1")
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("A1")
            With Sheet2Rng
                .Value2 = "CardCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("A2")
            With Sheet2Rng
                .Value2 = "CardCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("B1")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("B2")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C1")
            With Sheet2Rng
                .Value2 = "NumAtCard"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C2")
            With Sheet2Rng
                .Value2 = "NumAtCard"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D1")
            With Sheet2Rng
                .Value2 = "DOCDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D2")
            With Sheet2Rng
                .Value2 = "DOCDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'dates
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D3")
            'Sheet2Rng.Value2 = 
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E1")
            With Sheet2Rng
                .Value2 = "DOCDUEDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E2")
            With Sheet2Rng
                .Value2 = "DOCDUEDATE"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'store # information before inserted into sheet
            Dim storeList As New List(Of String)()
            Dim StoreRowLength As Integer = 1
            Dim testForNewBlankStore As String = "1"
            'store # copy from sheet1
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
            Try
    
                Do Until testForNewBlankStore = ""
                    StoreRowLength = StoreRowLength + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D" & StoreRowLength)
                    testForNewBlankStore = Sheet1Rng.Value2.ToString
                    storeList.Add(testForNewBlankStore.ToString)
                    'For Each number As String In storeList.Distinct
                    '    MsgBox(number, MsgBoxStyle.MsgBoxHelp, "test add")
                    'Next
    
                Loop
            Catch ex As Exception
    
            End Try
    
    
    
            'Create PO loop
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("C3")
            'create a class for this dialog box
            Dim someDialog As New SunriseDialog
            Dim POdetails As String
            'show the dialog box
            someDialog.ShowDialog()
            POdetails = someDialog.TextBox1.Text.ToString
            If someDialog.DialogResult = Windows.Forms.DialogResult.OK Then
                POdetails = someDialog.TextBox1.Text
                Me.Label1.Label = POdetails
            End If
            With Sheet2Rng
                .Value2 = POdetails.Trim.ToString
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            'loop with date in ISO format in vb
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("D3")
            Sheet2Rng.Value2 = Date.Now().Year() & Date.Now().Month() & Date.Now().Day()
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("OINV").Range("E3")
            Sheet2Rng.Value2 = Date.Now().Year() & Date.Now().Month() & Date.Now().Day()
        End Sub
        Public Sub sheet3Stuff()
            Dim Sheet2Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("INV1").Range("A1:A1")
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A1")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A2")
            With Sheet2Rng
                .Value2 = "DocNum"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B1")
            With Sheet2Rng
                .Value2 = "ItemCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B2")
            With Sheet2Rng
                .Value2 = "ItemCode"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C1")
            With Sheet2Rng
                .Value2 = "Quantity"
                .Font.Size = "12"
                .Font.Bold = True
            End With
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C2")
            With Sheet2Rng
                .Value2 = "Quantity"
                .Font.Size = "12"
                .Font.Bold = True
            End With
    
    
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
    
            'variables for gather row data
            rowLength = 1
            Dim testForNewBlank As String = "1"
            'store # copy from sheet1
            Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
            Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2")
            Try
    
                Do Until testForNewBlank = ""
                    rowLength = rowLength + 1
                    Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D" & rowLength)
                    testForNewBlank = Sheet1Rng.Value2.ToString
                Loop
            Catch ex As Exception
    
            End Try
    
            Try
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("D2:D" & rowLength + 1)
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("A3")
                Sheet1Rng.Copy(Sheet2Rng)
    
                'upc copy from sheet1
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("B3")
    
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("E2:E" & rowLength + 1)
                Sheet1Rng.Copy(Sheet2Rng)
                'quantity copy from sheet1
                Sheet2Rng = Globals.ThisAddIn.Application.Sheets("INV1").Range("C3")
    
                Sheet1Rng = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("G2:G" & rowLength + 1)
                Sheet1Rng.Copy(Sheet2Rng)
            Catch ex As Exception
    
            End Try
    
        End Sub
        Sub sheet4Stuff()
            '        Grab the raw data
            Dim Sheet1Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet1").Range("A1:M" & rowLength)
            Dim Sheet4Rng As Excel.Range = Globals.ThisAddIn.Application.Sheets("PIVOTTABLE").Range("A1")
    
            Dim oExcel As Object
            oExcel = Globals.ThisAddIn.Application
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
    
            oBook = oExcel.Workbooks.Add
            oSheet = oExcel.Worksheets(1)
    
            oSheet.Name = "PIVOTTABLE"
            oSheet.Range("A1").Value = "First Name"
            oSheet.Range("B1").Value = "Year"
            oSheet.Range("C1").Value = "Salary"
    
            oSheet.Range("A2").Value = "Frank"
            oSheet.Range("B2").Value = "2012"
            oSheet.Range("C2").Value = "30000"
    
            oSheet.Range("A3").Value = "Frank"
            oSheet.Range("B3").Value = "2011"
            oSheet.Range("C3").Value = "25000"
    
            oSheet.Range("A4").Value = "Ann"
            oSheet.Range("B4").Value = "2011"
            oSheet.Range("C4").Value = "55000"
    
            oSheet.Range("A5").Value = "Ann"
            oSheet.Range("B5").Value = "2012"
            oSheet.Range("C5").Value = "35000"
    
            oSheet.Range("A6").Value = "Ann"
            oSheet.Range("B6").Value = "2010"
            oSheet.Range("C6").Value = "35000"
    
            oExcel.Visible = True
            Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, oSheet.Range("A1:C6"))
    
            If oExcel.Application.Sheets.Count() < 2 Then
                oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
            Else
                oSheet = oExcel.Worksheets(1)
            End If
            oSheet.Name = "Pivot Table"
    
            Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=oSheet.Range("A1"))
    
            Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                .Function = Excel.XlConsolidationFunction.xlSum
                .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it - 
                ' this field name cannot be the same as therefore that space
                ' also it cannot be empty
    
                '' add another field
                'ptField = ptTable.PivotFields("Year")
                'With ptField
                '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                '    .Function = Excel.XlConsolidationFunction.xlMax
                '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
                'End With
    
                ' add column
                ptField = ptTable.PivotFields("First Name")
                With ptField
                    .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                    .Name = " "
                End With
    
            End With
            ' add grouping - again I don't need this in my example, this is just to show how to do it
            'oSheet.Range("C5").Group(1, 20, 40)
    
    
        End Sub
        Sub tabCrashed()
            MsgBox("This tab has performed an operation that made it crash in mid execution. Please perform task manually or contact Jonathan Brotto", MsgBoxStyle.Information, "Crashed")
        End Sub
    End Class
    

    Thursday, March 15, 2018 3:34 PM