On completion of the update, the session needs to be closed.This requires yet another Call Web Service call, this time calling the Close Workbook method to close the session we have open. The reason this was put into a UDA, is that I had requirements for a project where I needed to update a single cell in multiple places in my workflow.This allowed me to reuse this functionality in different locations in my workflow, but also throughout other workflows.

pivot table filter list not updating-83pivot table filter list not updating-90

As long as you have a credential with that name, the workflow/uda should work fine. cheers, Vadim Hi Vadim, thanks a lot for this great post! Vadim Hi Vadim, it is an Excel file with Macros - I just wanted to set a cell via workflow and use that cell as part of the Header.

If you have a different named constant, just update the UDA and republish it an the workflow. I was unable to download the UDA and import it but tried to recreat it and i'm getting this error on the second Call Web Service. Error returned from server: Hi Mike, in order to import the UDA, go to Manage User Defined Actions, click on the New or Create button and that will open the designer. That should let you import the file that is in the Downloads section on this post. It helped me a lot to configure the workflow to work with Excel. The set cell works but if you set the Header (and as far as I know it's only possible with a macro) you have to save the file as - and then my Problem starts :-) Markus Hi Vadim, Thanks for all your excellent posts! I have been struggling a long time to get exactly what you do with your UDA work with Nintex Workflow for Office 365 using the Web Request action.

If it's convenient, could you please help with getting this worked. I don't have the administration access to the site – but need to know what to ask the support team. Is it possible to have a workflow open an Excel file and refresh pivot data? I believe this must be because the session ID is wrong.

Especially - how to correctly set the Hi, Vadim, Are farm-level credentials required to make this work? There is a setting in Excel that will automatically refresh if the file is opened, so all I need the workflow to do is open the file and then close it again. Hi Melanie, Although I haven't done much work with pivot tables, I would say that it's probably not do-able with out of the box Workflow. If the pivot data refreshes when you open the file anyway, why do you need a workflow to do it, since it'll refresh the next time a user opens the file? I need a workflow because we want to display the pivot table in a webpart that the user sees. We are using a Nintex form that the user data enters in the data. The list is exported to Excel The workbook is stored in a library. In your UDA, I see you are using the whole string in I assume the Session ID should be only the “session Id” part of it?

But I'm trying to use this in more efficient way and to update multiple cells in one call to service using method set Range. I am able to call the Open Workbook For Editing method successfully, and it returns a session ID as it is supposed to.

Unfortunately I haven't got any success here - constantly getting an error "The size of the provided array does not match the size and shape of the destination range", so seems like I couldn't set the array of values in SOAP correctly. Excel Server Api Exception: You mentioned that it might be related to the Excel Services configuration. The problem is that the next call (for testing I only do Close Workbook for now, but have tried others as well) fails with status 500 and says “Missing required soap: Body element”.

We are opening a session, checking for errors, updating a cell, checking for errors and finally closing the session.

Although in the screenshot, I'm doing something naughty, in that I'm not labeling my actions, in the downloaded UDA file below, they have been labeled and should make it easier for you to understand what it is doing.

This is then used through future calls to the web service, until you finally close your session. In order to update a spreadsheet, firstly, open up a session to the spreadsheet so that it can be edited.

The web method to call is Open Workflow For Editing.

I am using 'Query Excel Service' to read excel data , my data can be n (1 to 5000) rows in excel .