Task tracker disallowed by Job tracker - Cloudera CDH 4.5 VM

December 20, 2013

When I loaded Cloudera's VM to explore hadoop, I was unable to start Tasktracker. The following error showed up.

Task tracker disallowed by Job tracker
org.apache.hadoop.filecache.TrackerDistributedCacheManager:Cleanup .. Java.lang.InterruptedExcpetion: sleep interrupted

The problem is gone by restarting JobTracker first and then starting tasktracker. Even if you are still seeing the issue, please follow the below discussion. The solution suggested in this thread is editing the /etc/hosts file.

An easier and cleaner work around is to fix your /etc/hosts file
Using VMWARE fusion and Centos 6.2 x64, I noticed the hosts file lists localhost first.   locahost localhost.localdomain localhost4 localhost4.localdomain4 
Rename it to this   localhost.localdomain localhost4 localhost4.localdomain4 localhost!topic/cdh-user/PRy7GynUI6U


Useful GIT & Nano Commands

November 05, 2013

Git commands

$ git branch
* master

$ git branch -a
* master

$ git branch -r

$ git remote show origin
* remote origin
  URL: git://
  Remote branch merged with 'git pull' while on branch master
  Tracked remote branches
    1-2-stable 2-0-stable 2-1-stable 2-2-stable 3-0-unstable master

$ git ls-remote --heads origin
5b3f7563ae1b4a7160fda7fe34240d40c5777dcd  refs/heads/1-2-stable
71926912a127da29530520d435c83c48778ac2b2  refs/heads/2-0-stable
2b158543247a150e8ec568becf360e7376f8ab84  refs/heads/2-1-stable
b0792a3e7be88e3060af19bab01cd3d26d347e4c  refs/heads/2-2-stable
d6b9f8410c990b3d68d1970f1461a1d385d098d7  refs/heads/3-0-unstable
f04346d8b999476113d5e5a30661e07899e3ff80  refs/heads/master

Set up Git repository
    git init
    git remote add origin
    git push -f origin master

Rewrite History (use Caution)
#go back a commit and force push to repo

git reset --hard HEAD^
git push -f

Abort Merge
git merge --abort

A good list of Nano Editor Commands

Cheat Sheet


How to create a chart using Open XML SDK in Word 2010

October 30, 2013

Creating a Chart in Open involves two steps, as the data is stored at two locations. 
  1. Chart Cache
  2. Embedded Package - This is nothing but an Excel Sheet
Thus, populating the data in Cache is not enough. We should also create corresponding Excel Sheet. Below is the sample code. The code is verbose as I was using Open XML productivity tool to arrive at this. Full source code can be found at 

Main Class:

   ChartPart chartPart2 = mainDocumentPart1.AddNewPart<ChartPart>("rId7");  
       //Used to generate a dummy excel sheet out of a predefined blob - Look at "private string embeddedPackagePart2Data"  
       EmbeddedPackagePart embeddedPackagePart2 = chartPart2.AddNewPart<EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "rId1");  
       //Alter the default embeddedPackage(Excel Sheet)  
       using (Stream str = embeddedPackagePart2.GetStream())  
       using (MemoryStream ms = new MemoryStream())  
         CopyStream(str, ms);  
         using (SpreadsheetDocument spreadsheetDoc =  
           SpreadsheetDocument.Open(ms, true))  
           // Update data in spreadsheet  
           // Find first worksheet  
           ss.Sheet ws = (ss.Sheet)spreadsheetDoc.WorkbookPart  
           string sheetId = ws.Id;  
           WorksheetPart wsp = (WorksheetPart)spreadsheetDoc  
             .Where(pt => pt.RelationshipId == sheetId)  
           ss.SheetData sd = wsp  
           foreach (ss.Row tsd in sd.Elements<ss.Row>())  
             if (tsd.Elements<ss.Cell>().Count()> 1)  
               ss.Cell cell1 = tsd.Elements<ss.Cell>()  
               if (cell1 != null)  
                 ss.CellValue cell1value = cell1.Elements<ss.CellValue>().FirstOrDefault();  
                 if (cell1value != null && !cell1value.InnerText.Equals("0"))  
                   //Data Goes Here  
                   cell1value.Text = "25";  
         // Write the modified memory stream back  
         // into the embedded package part.  
           embeddedPackagePart2Data = Getbase64String(ms);                   
       //Regenerate EmbeddedPackage  

Helper Functions:

 // Generates content of chartPart2.  
     private void GenerateChartPart2Content(ChartPart chartPart2)  
       C.ChartSpace chartSpace2 = new C.ChartSpace();  
       chartSpace2.AddNamespaceDeclaration("c", "");  
       chartSpace2.AddNamespaceDeclaration("a", "");  
       chartSpace2.AddNamespaceDeclaration("r", "");  
       C.Date1904 date19042 = new C.Date1904() { Val = false };  
       C.EditingLanguage editingLanguage2 = new C.EditingLanguage() { Val = "en-US" };  
       C.RoundedCorners roundedCorners2 = new C.RoundedCorners() { Val = false };  
       AlternateContent alternateContent2 = new AlternateContent();  
       alternateContent2.AddNamespaceDeclaration("mc", "");  
       AlternateContentChoice alternateContentChoice2 = new AlternateContentChoice() { Requires = "c14" };  
       alternateContentChoice2.AddNamespaceDeclaration("c14", "");  
       C14.Style style3 = new C14.Style() { Val = 102 };  
       AlternateContentFallback alternateContentFallback2 = new AlternateContentFallback();  
       C.Style style4 = new C.Style() { Val = 2 };  
       C.Chart chart2 = new C.Chart();  
       C.Title title1 = new C.Title();  
       C.Overlay overlay2 = new C.Overlay() { Val = false };  
       C.AutoTitleDeleted autoTitleDeleted2 = new C.AutoTitleDeleted() { Val = false };  
       C.PlotArea plotArea2 = new C.PlotArea();  
       C.Layout layout2 = new C.Layout();  
       C.PieChart pieChart1 = new C.PieChart();  
       C.VaryColors varyColors2 = new C.VaryColors() { Val = true };  
       C.PieChartSeries pieChartSeries1 = new C.PieChartSeries();  
       C.Index index4 = new C.Index() { Val = (UInt32Value)0U };  
       C.Order order4 = new C.Order() { Val = (UInt32Value)0U };  
       C.SeriesText seriesText4 = new C.SeriesText();  
       C.StringReference stringReference7 = new C.StringReference();  
       C.Formula formula10 = new C.Formula();  
       formula10.Text = "Sheet1!$B$1";  
       C.StringCache stringCache7 = new C.StringCache();  
       C.PointCount pointCount10 = new C.PointCount() { Val = (UInt32Value)1U };  
       C.StringPoint stringPoint16 = new C.StringPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue28 = new C.NumericValue();  
       numericValue28.Text = "Cases By Aging";  
       C.CategoryAxisData categoryAxisData4 = new C.CategoryAxisData();  
       C.StringReference stringReference8 = new C.StringReference();  
       C.Formula formula11 = new C.Formula();  
       formula11.Text = "Sheet1!$A$2:$A$5";  
       C.StringCache stringCache8 = new C.StringCache();  
       C.PointCount pointCount11 = new C.PointCount() { Val = (UInt32Value)4U };  
       C.StringPoint stringPoint17 = new C.StringPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue29 = new C.NumericValue();  
       numericValue29.Text = "1st Qtr";  
       C.StringPoint stringPoint18 = new C.StringPoint() { Index = (UInt32Value)1U };  
       C.NumericValue numericValue30 = new C.NumericValue();  
       numericValue30.Text = "2nd Qtr";  
       C.StringPoint stringPoint19 = new C.StringPoint() { Index = (UInt32Value)2U };  
       C.NumericValue numericValue31 = new C.NumericValue();  
       numericValue31.Text = "3rd Qtr";  
       C.StringPoint stringPoint20 = new C.StringPoint() { Index = (UInt32Value)3U };  
       C.NumericValue numericValue32 = new C.NumericValue();  
       numericValue32.Text = "4th Qtr";  
       C.Values values4 = new C.Values();  
       C.NumberReference numberReference4 = new C.NumberReference();  
       C.Formula formula12 = new C.Formula();  
       formula12.Text = "Sheet1!$B$2:$B$5";  
       C.NumberingCache numberingCache4 = new C.NumberingCache();  
       C.FormatCode formatCode4 = new C.FormatCode();  
       formatCode4.Text = "General";  
       C.PointCount pointCount12 = new C.PointCount() { Val = (UInt32Value)4U };  
       for (int i = 0; i <= 3; i++)  
         C.NumericPoint numericPoint13 = new C.NumericPoint() { Index = UInt32Value.FromUInt32((uint)i) };  
         C.NumericValue numericValue33 = new C.NumericValue();  
         //numericValue33.Text = (10*(i+1)).ToString();  
         //Data Goes Here  
         numericValue33.Text = "25";  
       /* C.NumericPoint numericPoint13 = new C.NumericPoint() { Index = (UInt32Value)0U };  
       C.NumericValue numericValue33 = new C.NumericValue();  
       numericValue33.Text = "99";  
       C.NumericPoint numericPoint14 = new C.NumericPoint() { Index = (UInt32Value)1U };  
       C.NumericValue numericValue34 = new C.NumericValue();  
       numericValue34.Text = "3.2";  
       C.NumericPoint numericPoint15 = new C.NumericPoint() { Index = (UInt32Value)2U };  
       C.NumericValue numericValue35 = new C.NumericValue();  
       numericValue35.Text = "1.4";  
       C.NumericPoint numericPoint16 = new C.NumericPoint() { Index = (UInt32Value)3U };  
       C.NumericValue numericValue36 = new C.NumericValue();  
       numericValue36.Text = "1.2";  
       numberingCache4.Append(numericPoint16); */  
       C.DataLabels dataLabels2 = new C.DataLabels();  
       C.ShowLegendKey showLegendKey2 = new C.ShowLegendKey() { Val = false };  
       C.ShowValue showValue2 = new C.ShowValue() { Val = false };  
       C.ShowCategoryName showCategoryName2 = new C.ShowCategoryName() { Val = false };  
       C.ShowSeriesName showSeriesName2 = new C.ShowSeriesName() { Val = false };  
       C.ShowPercent showPercent2 = new C.ShowPercent() { Val = false };  
       C.ShowBubbleSize showBubbleSize2 = new C.ShowBubbleSize() { Val = false };  
       C.ShowLeaderLines showLeaderLines1 = new C.ShowLeaderLines() { Val = true };  
       C.FirstSliceAngle firstSliceAngle1 = new C.FirstSliceAngle() { Val = (UInt16Value)0U };  
       C.Legend legend2 = new C.Legend();  
       C.LegendPosition legendPosition2 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };  
       C.Overlay overlay3 = new C.Overlay() { Val = false };  
       C.PlotVisibleOnly plotVisibleOnly2 = new C.PlotVisibleOnly() { Val = true };  
       C.DisplayBlanksAs displayBlanksAs2 = new C.DisplayBlanksAs() { Val = C.DisplayBlanksAsValues.Gap };  
       C.ShowDataLabelsOverMaximum showDataLabelsOverMaximum2 = new C.ShowDataLabelsOverMaximum() { Val = false };  
       C.ExternalData externalData2 = new C.ExternalData() { Id = "rId1" };  
       C.AutoUpdate autoUpdate2 = new C.AutoUpdate() { Val = false };  
       chartPart2.ChartSpace = chartSpace2;  
     // Generates content of embeddedPackagePart2.  
     private void GenerateEmbeddedPackagePart2Content(EmbeddedPackagePart embeddedPackagePart2)  
       System.IO.Stream data = GetBinaryDataStream(embeddedPackagePart2Data);  


Chatter Unfollow Test Class Failures

October 29, 2013

Recently, we noticed all test failures in one of our test classes. The test class was written in 19.0. The problem being SOQL's were not returning records and all the assertions were failing. 

Assertion Failed: Expected: 2, Actual: 0

The fix turned out to be very simple. We fixed the error by updating the APEX class version to 29.0


Android SalesforceSDK - Make Mobile Layout More Responsive By Using Load Spinner

September 18, 2013

I noticed that layout-xlarge in Android Salesforce SDK has a nice loading spinner and header bar. So I have changed the layout appropriately. As part of this effort, I have modified or copied drawables,colors etc. from xlarge resources. There are no code changes required. I am attaching the resources folder for the same. Replace the res @ ....forcedroid\native\SalesforceSDK with the contents of the following zip 


Chrome Extension - Interactive Scraper

July 17, 2013

This is my attempt to write an Interactive Scraper.

See the Robot Icon In Browser Action Icons

How to use?

1. From browser, you can choose the DOM element to scraper time to time. 
Click On Capture detail
2. Data is persisted in Indexed DB of Background Page. 
3. Data can be viewed or deleted by clicking "View" link from Add-In. This is implemented using Data Tables (table plug-in for jQuery )

4. Once configured, you can run the scraper by clicking on "Roll" button. It will take you through all the URL's and collect text from DOM path and capture a screen shot for your future reference. 

Development Challenges: 

I have faced a few development challenges while developing this. 

1. Chrome Synchronous Message Passing: I faced this problem when trying to load the URL's in a tab one after another. After the URL is loaded, the scraper should collect the text from the DOM and update it in database. There are many answers available in the internet. Nothing has solved my problem. The problem was 

Port error: Could not establish connection. Receiving end does not exist. 

I am using chrome.tabs.onUpdated.addListener to understand if the the new URL is loaded. Once the page is loaded, scroll to the DOM element, highlight it and extract text. Final step is to send the collected text back to background page to store it in indexedDB. But, there was no easy way to control. I used chrome.tabs.executeScript to scroll to DOM element and collect the text. 

I tried the following approaches. 

1. In the callback of chrome.tabs.executeScriptcall chrome.tabs.sendMessage to request the content script to send the collected text back to background page. It didn't work as the content script's listener is not ready even though the page is loaded. 

2. In chrome.tabs.onUpdated.addListener once the page is loadedcall chrome.tabs.sendMessage in one of the call backs to request the content script to send the collected text back to background page.

3. Use results of chrome.tabs.executeScript capture the collected text. It didn't work as the page is moving on to next URL before collecting text. 

Finally, I encountered the following post on Stack Overflow.

Using the above technique, we could only initiate our actions on the loaded page only after the content script is loaded. I used the same variable to make this whole process synchronous. 

1. Once the tab is loaded use chrome.tabs.executeScript and execute the following 

script chrome.tabs.executeScript(tabid, {
code: "var EnhanceLibIsLoaded=false;chrome.extension.sendMessage({ loaded: EnhanceLibIsLoaded || false });"

2. In chrome.runtime.onMessage.addListener of background page, you will receive request.loaded. This is the proof that content script is loaded. 

if request.loaded is false, collect the text, set EnhanceLibIsLoaded to true and call chrome.tabs.update.

Yes, you can't do synchronous messaging in chrome extensions. But, using this approach you can control the flow and achieve the synchronous flow of data. 

2. Persisting Data in Chrome Extension Using Indexed DB:
As long as the extension is active, background page retains its contents. So, I used Indexed DB to store the data persistently in chrome extension. The tricky part of indexedDB is to keeping transactions active. 

Following post is really helpful.

3. Data Tables:  Please see the code for more details (dTable.cs). 

Source code can be found at 


Native Vs Hybrid

July 09, 2013

There are three ways to deliver an application on Mobiles –
1. Native – Built on Native Mobile Platform
2. HTML 5 – Works in browser
3. Hybrid – Built with HTML5, Can access native features
It is clear that user spend 80% of their time using Apps on mobile. That is four times more than the time they spend in browsing. Hence, browser based HTML 5 application is not the preferred way to deliver your mobile application. We have two options left before us. One, build a native application using the mobile OS platform (ex: Android or iOS). Second, use your HTML5 skills to quickly build an application and make it work as a native app on all platform using build frameworks such as PhoneGap.
Many have tried to compare and contrast Native and Hybrid. In this article I will summarize the analysis I have done over this subject matter.  After going through lot of articles and presentations, a presentation from propelics appealed to me. It is unbiased and to the point. Below are the pros and cons for each approach.
Cross platform deployment cost
Update Speed
Pixel Perfect UX
Availability of Skill Sets
APIs to Platform Specific Features
Support Multiple Platforms
Extensive Offline Support
Quicker Learning Curve
App Monetization
No App Store Approvals Needed
Inconsistent Browser Support

Mix Web Code with Native Wrapper
Performance – Browser Dependent
Less Code to Support Multi-Platform
Requires More Specialized Dev. Skills
App Store Experience
Native Apps that Don’t Look Native
APIs to Access Device Features
Risk of App Store Rejection
Make Changes w/o Resubmit to App Store
Extensive Offline Support

Inconsistent Browser Support

Rich User Interface
More difficult to Support / Maintain
Best App Performance
Increased Time to Update/ Distribute
Most Secure
Skills Can be Hard to Find
Hooks into all the device APIs
Limited App Portability

General verdict on HYBRID vs. NATIVE is – “Let your business requirement drive the decision”. I completely agree with the conclusion. But, some of the above points are not so evident. I would like to elaborate on some of the above aspects based on my research and experience.
One misconception about Hybrid platform is “Write Once – Deploy Everywhere”.  But, it is not completely true. In Hybrid platform, you are developing an HTML5 app (using CSS and JavaScript). Then the website wrapped using Native API with Hybrid mobile development platform (such as PhoneGap) for a specific device. The UI of the app is nothing but a web browser instance (For Apple devices, it’s Safari) with 100% height and width. And, JavaScript is used to access device specific features.
An important point to note here is, in Hybrid we are trying to make a website look like a native application with the help of a third party framework. Thus, we can carry Model and Controller part of the code to other devices. But, View should be specific to devices. If developing an application for Android, the web page should be able to mimic native UI features such as animations, scroll bounce, hide scrollbars, buttons, lists etc. (See case study of Exfm). I think this is one of the reasons why most of the PhoneGap applications are released on one or two platforms only.
We have one webpage and DOM of the page is manipulated using JavaScript. Of course, we have frameworks to manage this part.
Bootstrap.js – Twitter JS Library, for fluid UI across platforms. It doesn’t quite work for complex applications
Jquery Mobile – Great DOM manipulation library, easy to learn, works great for simple demos and performance of animations is an issue. Recommended for simple projects
Snecha Touch – Based on ExtJs framework. Good animations. Learning takes some effort. It’s recommended for real projects.
Lungo -
Controller and Model
Backbone.js –gives structure to web applications by providing models with key-value binding and custom events, collections with a rich API of enumerable functions, views with declarative event handling, and connects it all to your existing API over a RESTful JSON interface.
Angular.js – MVC frame work by Google
Ember.js - Ember is a JavaScript framework for creating ambitious web applications that eliminates boilerplate and provides standard application architecture.
KnockOut.js - Simplify dynamic JavaScript UIs by applying the Model-View-View Model (MVVM) pattern
Look at to download a sample TODO list app across all possible MV* platforms.
A decent comparison and factors to consider for choosing a framework can be found here.
If you have already noticed by now, JavaScript is the main ingredient for Hybrid app development.  And, you need specialized development skill to make the app look and behave like native one.
With all great power comes great responsibility. We can’t develop any HTML app and expect it to work on mobile smoothly. Generally, Hybrid apps need more response time than Native counterparts as their calls have to pass through an intermediate layer (PhonGap). Below articles give some tips and tricks to build better performing apps.
*People generally refer to Weinre for debugging PhoneGap applications. Complete list of debugging tools can be found here.
Security & Offline Support:
PhoneGap has a very good write up about security for each platform.  Reverse engineering is a concern for many people since once can simple open the binary and look at JavaScript code. PhoneGap recommends downloading JavaScript at runtime. Some people felt that App store approval may become difficult task as we are loading JS at runtime, if we follow this recommendation.
Offline file security is mostly device driven. And, it has little to do with whether you are developing a hybrid or native application. Recommended defenses are
1.       Lock screen
2.       Internal Storage
3.       Full – Disk Encryption
After applying all the above, the mobile data is still vulnerable. Please refer this talk from AnDevCon IV for all offences and defenses.  There is a need for a secure data store on Mobile (SQL Cipher?).
Tom Gersic advice goes like this – “If you know what the devise you are going with, if you are rolling out a 1000 IPADS in the enterprises it’s going to be a lot quicker to develop for just one platform, a native application. The tool set is better. It’s going to be more performance better experience for the users. If you are rolling out a mobile website, rolling out to android, rolling out to tablets and phones, rolling out to iPads iPhones, if you are thinking in the back of your head about windows phone…. that’s when you start looking at … Hybrid development…. One codebase and quicker way to go to market
Bottom line is if you know the platform and a smoothly performing application use Native. If you want one code base and reach out to multiple platforms choose Hybrid.
Sales force Mobile Services:
Salesforce has done a good job in helping the developers to build enterprise mobile applications on the platform of their choice.
1.       Authentication model is already setup. Salesforce uses OAuth 2.0 to authenticate
2.       Smart Store - encrypted data store is available for both hybrid and native platforms
3.       Developer mobile packs to quickly start with app development
4.       Salesforce Mobile SDK – REST APIs and JavaScript Remoting give access to functionality and data
5.       VF components are available for quick HTML5 application development
6.       Mobile push notifications are in the roadmap



Sample: Posting a Comment with a File Attachment

July 09, 2013

In SFDC Apex Code Developer, a sample to post a comment with file attachment using Chatter Apex (ConnectAPI) is given. But, the code is not copy paste-able. You may see the following error for messageInput.messageSegments.add(textSegment);

System.NullPointerException: Attempt to de-reference a null object

The correct code looks as below. 

String communityId = null;
String feedItemId = '0D5x0000000azYd';
ConnectApi.CommentInput input = new ConnectApi.CommentInput();

ConnectApi.MessageBodyInput messageInput = new ConnectApi.MessageBodyInput();
messageInput.messageSegments = new List<ConnectApi.MessageSegmentInput>();

ConnectApi.TextSegmentInput textSegment;
textSegment = new ConnectApi.TextSegmentInput();
textSegment.text = 'Comment Text Body';
input.body = messageInput;
ConnectApi.NewFileAttachmentInput attachmentInput = new ConnectApi.NewFileAttachmentInput();
attachmentInput.description = 'The description of the file';
attachmentInput.title = 'contentFile.txt';
input.attachment = attachmentInput;
String fileContents = 'This is the content of the file.';
Blob fileBlob = Blob.valueOf(fileContents);
ConnectApi.BinaryInput binaryInput = new ConnectApi.BinaryInput(fileBlob, 'text/plain',

ConnectApi.Comment commentRep = ConnectApi.ChatterFeeds.postComment(communityId, feedItemId, input, binaryInput);


Why my custom prerequisite is saying it is corrupt?

July 05, 2013

Verifying file hash
Error: Setup has detected that the file '.....XXXXX.msi' has either changed since it was initially published or may be corrupt.
Option 1: Uninstall .NET 4.5 and work with .NET 4.0 only
Option 2: Sign your package files with a code-signing certificate to avoid a defect introduced in .NET 4.5



Updating SourceData/ Data Source of the Pivot Table

July 05, 2013


1. Run time error '1004' Cannot Open PivotTable source file

2. Data source reference is not valid

3. Can not use web data source as pivot data

4. Run-time error '-2147024809 (80070057)' Item with specified name wasn't found

5. Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’

6. Exception from HRESULT: 0x800A03EC   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)


We have spend a day searching around for the solution. We get this error when we open the excel sheet in Internet Explorer. 

We see this error  "Run time error '1004' Cannot Open PivotTable source file" when 
the following statement is run in VBA. 


As we can observe the sheet name is appended with URI. The is incorrect. In order to restore the SourceData to original value, we have to remove the URI from the datasheet.

This is not possible until file is READ-WRITE. Thus, on workbook open we have saved the file and made it writable in Workbook_Open Event.

private void Application_WorkbookOpen(Excel.Workbook xlb){

if (Globals.ThisAddIn.Application.ActiveWorkbook.FullName.Contains("servlet.FileDownload"))

                            Application.DisplayAlerts = false;                       
                            String str = Globals.ThisAddIn.Application.ActiveWorkbook.FullName;
                            int index = str.IndexOf("file=");
                            String tempId = str.Substring(index);
                            xlb.SaveAs("servlet.FileDownload" + tempId);
                            Application.DisplayAlerts = true;

By doing this we are ensuring that the data source of the file is editable.  But it doesn't change the source of pivot tables. 

Next step is to update the SourceData. Immediately, we tried 


With Sheets("TargetSheetName").PivotTables("PivotTableName").PivotCache

.SourceData = Sheets("SourceSheetName").Range("a16:CI51").Address(True, True, xlR1C1, True)
*TargetSheetName is where pivot table resides 

We met with an exception  HRESULT: 0x800A03EC

We worked around the problem by using the following

.ChangePivotCache(xlb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "PivotSheetName!A14:U51"));                            


Sub Update_PTSource()
    With ActiveSheet
        .PivotTables("PivotSheetName").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="'" & .Name & "'!PTsource")
    End With
End Sub



Popular Posts