User Power Pivot to get date from Salesforce.com

sullisnyc44,

Salesforce.com provides an Excel/Word Add-in called "Connect for Office".  It is located in SFDC at...  At the top of the main page, Click you name / Setup.   Under Personal Setup click "Desktop Integration" and then "Connect for Office".   The Add-in adds a new menu that allows you to login to Salesforce from within Excel, run an existing report from your SFDC reports library and it downloads the output to your Excel file across the web.   The downloaded data from your report can be used for your Pivot Tables.  The report update process allows you to update just the report or the report and all associated Pivot tables.

The bad news is that the Add-in supports Excel 2000 through 2007.   I use this process every day with an Office 2007 system.    I have installed the Add-in on my 2010 system and even though the install ends successfully the Add-in does not show up in Excel.

I think you have two options; the first is to revert back to a 2007 Excel and use the Add-in.  The second is to run your report from SFDC and export it as a CSV or Excel file and copy/paste the data into the workbook with your Power Pivots.   Either method works but the automated Add-in is the most desirable.

I do not believe there is another automated solution because SFDC has a multi-layer security system.  To log into SFDC from an external system such as Excel or Word requires a 24 character SFDC generate security token that looks like this....  4XxQpYIpay97R5UOShU4t1Tv.  

Hope this is helpful...

we have 2010 - I guess they could revert back to 2007 if necessary.

BUT couldnt I create something with REST API, token and all, and then connect with PowerPivot?

 

sullisnyc44,

I have not worked with the REST API so I am not the best person to answer that question.   I think your biggest hurdle would be establishing a connection with SFDC.   If you could make the connection you might be able to use REST to extract the data.   The information you need would have to come from a SFDC report but as long as you know the URL of the results of the report and the position on the page you should be able to extract it.

The Connect for Office Add-in has two unique steps.  The first is logging on to SFDC.  The second is identifying and retrieving the data.   Making the connection is key.   I am sorry that I can't help you more on this.

Find a 2007 PC that you can load the you can load the Add-in on and work with it a bit so you can see the process.  That may give you an some ideas.  I just searched the answer forums of SFDC and do not see an estimated 2010 release date.   Search App-Exchage apps, there may be an application that will work with 2010.

Thanks,
ProdOps

I don't have any experience with that either, just seemed like it might be a good fit.

Downgrading to excel 2007 is not really a forward moving strategy for my organization...

I completely understand not downgrading.   As I mentioned above, running the report and exporting the results is an option until SFDC updates the Add-in for Excel 2010.

BTW, I don't think Excel will be able to connect to the REST API directly.

it almost seems like it something should e able to work though, shouldn't it? <sigh>

So I have Excel 64bit of course. Would it work if I ran the excel 32.exe?

Well, if you have the 64-bit Office installed, the add-in simply can't get loaded into Excel.  SFDC Connect is a 32-bit add-in, which is compiled differently than the 64-bit kind needed.  You could remove 64-bit Excel and install the 32-bit one.  I guess that's still better than reverting to 2007, as you at least get to keep Power Pivot.