Hi,
Since I couldn't get much help on the net, I had no option but to debug it on my own, I realized there would be a ton of people who might face the same problem. Hence, m posting the solution that worked for me.
The second line of code
Excel.Range oRange2 = (Excel.Range)oSheet1.Range["A3:G10"];
this sets the range of the pivot table in the worksheet, hence it can be better given as under
Excel.Range oRange2 = (Excel.Range)oSheet1.Range["Sheet2!$A:$K"];
considering sheet 1 would have the data present.
Once I finished that and gave the pivot a definite number of columns it worked for me.
There is another way of going about it,
Excel.PivotTable oPivotTable = oPivotCache.CreatePivotTable(oRange2);
This also gives no error in case your range defined is correct.
I hope this helps someone out there :)
Thanks