Logo 
Search:

Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

update an excel pivot table in sharepoint

  Asked By: Amrita    Date: Jan 19    Category: Sharepoint    Views: 2617

My developer is having some trouble updating a pivot table in a
SharePoint site. Below is his comments. Can anyone help him?
Liz


* This error seems like a microsoft generic error that states
something is wrong with the com request. The request is to Open an excel
file from sharepoint location, update a pivot table after the SQL table
has been created. Save the excel spreadsheet, and then publish the
sheets to a document library with Excel Services.
The current code is as follows:


private bool XLOps()
{
bool success = false;

Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbooks xlWBs;
Microsoft.Office.Interop.Excel.Workbook xlWB;
Microsoft.Office.Interop.Excel.Sheets xlSheets;
Microsoft.Office.Interop.Excel.Worksheet xlWS;
Microsoft.Office.Interop.Excel.PivotTable xlPT;

xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
xlWBs = xlApp.Workbooks;
if
(xlWBs.CanCheckOut(@"http://appsf/PMO/PP/Daily%20Production%20Reporting/
PPProductionBuild.xlsx"))
{
xlWB =
xlWBs.Open(@"http://appsf/PMO/PP/Daily%20Production%20Reporting/PPProduc
tionBuild.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);

//
xlWBs.CheckOut(@"http://appsf/PMO/PP/Daily%20Production%20Reporting/PPPr
oductionBuild.xlsx");

xlWB = xlWBs[1];
xlSheets = xlWB.Worksheets;
xlWS = (Worksheet)xlSheets[1];

xlPT = (PivotTable)xlWS.PivotTables(1);
success = xlPT.RefreshTable();
xlPT.Update();

// if (xlWB.CanCheckIn())
// {
// string comment = String.Format("thru {0} {1}",
DateTime.Now.ToString("MMM"), DateTime.Now.ToString("dd"));
// xlWB.CheckIn(true, comment, true);
// }
xlWB.Save();
string SPDest = @"http://appsf/PMO/PP/test/PPTest1.xlsx";
Microsoft.Office.Interop.Excel.PublishObjects po =
xlWB.PublishObjects;
for(int sx = 1; sx <= xlSheets.Count; ++sx)
{
Worksheet pub_Sheet = (Worksheet)xlSheets[sx];

po.Add(XlSourceType.xlSourceSheet, SPDest, pub_Sheet,
pub_Sheet.Name, XlHtmlType.xlHtmlStatic, pub_Sheet.Name, "Phong Phu
Finished Production Test");
}

po.Publish();

string msg = String.Empty;
try
{
xlApp.Workbooks.Close();
xlApp.Quit();
xlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
msg = String.Empty;
}
catch (Exception ex)
{
msg = ex.Message;
}

}
return success;
}

At the po.Add() method is where I am having this issue. I have found
very little information on doing the publish from the internet.

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on update an excel pivot table in sharepoint Or get search suggestion and latest updates.




Tagged: