The error says "String cannot be empty."
Here is the code that I've write,
- Code: Select all
Public Sub CreatePivot()
Dim workbook As New Workbook()
workbook.LoadFromFile("1.XLSX")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Pending Invoice" 'The error says that ---->Name of worksheet must be unique in a workbook. but that is the name of my worksheet.
Dim sheet2 As Worksheet = workbook.CreateEmptySheet()
sheet2.Name = "Pivot Table"
Dim dataRange As CellRange = sheet.Range("A1:AJ25")
Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange) 'the error is ----------> "String cannot be empty."
Dim pt As PivotTable = sheet2.PivotTables.Add("Pivot Table", sheet.Range("A1"), cache)
Dim r1 = pt.PivotFields("Project")
r1.Axis = AxisTypes.Row
pt.Options.RowHeaderCaption = "Project"
Dim r2 = pt.PivotFields("Aging Date")
r2.Axis = AxisTypes.Row
Dim r3 = pt.PivotFields("Del No")
r3.Axis = AxisTypes.Row
Dim r4 = pt.PivotFields("Ship Qty")
r4.Axis = AxisTypes.Row
Dim r5 = pt.PivotFields("Ext Cost")
r5.Axis = AxisTypes.Row
pt.DataFields.Add(pt.PivotFields("Aging Date"), "Aging Date", SubtotalTypes.Sum)
pt.DataFields.Add(pt.PivotFields("Del No"), "Total of Pending Invoice", SubtotalTypes.Sum)
pt.DataFields.Add(pt.PivotFields("Ship Qty"), "Pending Qty", SubtotalTypes.Sum)
pt.DataFields.Add(pt.PivotFields("Ext Cost"), "Pending Amount (USD)", SubtotalTypes.Sum)
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12
workbook.SaveToFile("1.XLSX", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("1.XLSX")
