I have a pipe-delimited text file that I need to read, turn into a pre-formatted Excel document - complete with separate pivot table sheet - and save.
Reading the file and saving it as an Excel document are solved, but I can't seem to get the pivot table to work. I've found a variety of tutorials online, but none are actually working for me.
I've reordered the arguments in the order they present, but I get the exact same late binding error.
When VS complains and states: "Option Strict On disallows late binding", it is telling you that you are either attempting to reference some method or property on a object that it only can identify as type System.Object or perform an invalid implicit cast.
Now let's look at the problem code.
Dim ptTable As Excel.PivotTable = xlPivotSheet.PivotTables.Add(ptCache, ptLocation, "Pivot")
You previous defined:
Dim xlPivotSheet As Excel.Worksheet
Therefore, xlPivotSheet is not the culprit as VS knows it is type Excel.Worksheet.
The next logical suspect is what PivotTables returns. While you were keying in that code, you should have seen something similar to this:
Did you notice that nice little help that Intellisense gave you when you entered the code? You did enter the code, right? Or did you copy-paste an example from some internet landmine site that posts code with Option Strict Off?
Regardless, you now know that you should cast the result of xlPivotSheet.PivotTables to the Excel.PivotTables interface to enable early binding and thus use the Add Method without that nasty error.
Dim ptTable As Excel.PivotTable = DirectCast(xlPivotSheet.PivotTables,Excel.PivotTables).Add( ....
Recommended Reading: Using IntelliSense
I ended up accomplishing this by changing the problem line to the below.
Dim Table As Excel.PivotTable = xlPivotSheet.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase,
Range,Location, "Title Here",True, True, True, False)