Consuming Web Service in Excel

mrQQ

Hello,

in one of MS demos they showed how you could use Excel to list navision table data, and then have the changes appear in Nav. I think it was done using web services.

Does anyone remember how they did it?

nunomaia

In Excel you could use obtain external datasource. I teach customer to use that excel feature to use pivotals directly to Navision tables.

kine

mrQQ wrote:

Hello,

in one of MS demos they showed how you could use Excel to list navision table data, and then have the changes appear in Nav. I think it was done using web services.

Does anyone remember how they did it?

Yes, it is possible, but had no time to try it yet...

mrQQ

kine wrote:

mrQQ wrote:

Hello,

in one of MS demos they showed how you could use Excel to list navision table data, and then have the changes appear in Nav. I think it was done using web services.

Does anyone remember how they did it?

Yes, it is possible, but had no time to try it yet...

can you remember how did they connect?

nunomaia

If you want do use WS directly you can use Data -> From Web.

Insert WS URL and parameters,

David_Singleton

kine wrote:

mrQQ wrote:

Hello,

in one of MS demos they showed how you could use Excel to list navision table data, and then have the changes appear in Nav. I think it was done using web services.

Does anyone remember how they did it?

Yes, it is possible, but had no time to try it yet...

My understanding was that this feature was not in the marketing beta?

kine

David Singleton wrote:

kine wrote:

mrQQ wrote:

Hello,

in one of MS demos they showed how you could use Excel to list navision table data, and then have the changes appear in Nav. I think it was done using web services.

Does anyone remember how they did it?

Yes, it is possible, but had no time to try it yet...

My understanding was that this feature was not in the marketing beta?

No, it is not there, but Webservices are there and you can use them. There is no example or som part of the application using it...

claband

Excel doesn't natively connect to Web Services. Web data is really HTML tables that can be imported into Excel. For that to work you'd have to write a small wrapper that creates a HTML Table around the data that comes from NAV.
Another way for Excel to consume Web Services is to create some VBA code that connects to NAV Web Services and populates Excel

/Claus

mrQQ

i just wonder how they did it in that demo..

demy75

The answer is explained here:
http://demiliani.com/blog/archive/2008/10/23/6516.aspx

freddy.dk

The Excel demo (using Web Services) was created in order to see whether I could consume WebServices loosely coupled.
Typically all the demos we have been doing on webservices have included a way of adding a reference to a page or a codeunit in Visual Studio and then work on this webservice proxy class.
The Excel demo is connecting via XMLHTTP to the web service page, reading the metadata and based on this - creating the spreadsheet.

I have promised people who have been asking that I will publish the source for the WebService demo to the community once 2009 is released and I will keep that promise.

I will probably create a simple version (for editing a specific page - using a proxy class though) and publish this too, stay tuned.

/Freddy

freddy.dk

Oh - I totally forgot to write how I did the demo.
VSTO is the secret - it allows you to write .net code behind your spreadsheet - and you have a number of triggers in the document.
High level here is how it goes:

1. NAV calls out to a client side automation object with information about applied filter and page to use
2. Automation object creates an excel template (based on an excel template template with VSTO code inside it)
3. Automation object launches a new spreadsheet based on this template and deletes the template
4. Excel starts up - connects to web services and read data (URL, filter and page is given by the automation object)
5. When saving - Excel connects to web services and write data back.

I do think there are a million opportunities with VSTO, Web Services and ERP.

freddy.dk

I just completed the last post on this subject to my blog

http://blogs.msdn.com/freddyk/archive/2008/11/09/edit-in-excel-part-4-out-of-4.aspx

This one has links to Part 1, 2 and 3 - and they are really done in sequential order.
I hope that people can use this stuff - there are (as I said) a ton of opportunities using Office and NAV 2009 integration, and I will for sure explore other stuff - and post this as well.

arvidnormann

Hi Freddy.

Nice example, you have created. Better than those on partner source.

I have some problem with your code.

I have started the VS project on the NAV 2009 CTP4.

I have published Vendor, Item and Customer from Navision. Why do you have these 3 web reference. You only use Customer.

When I run the code I get the customer list. But when i edit, delete or add lines and click save changes on the ribon. It just reloads the data and no data is updated.

Where I use the debugger on this code and inserts a breakpoint on "service.Delete(((CustomerRef.Customer)obj).Key);" it is not run where I delete a record and click save changes on the ribon.

internal void Save()
{
// Initialize the Service Connection
CustomerRef.Customer_Service service = new CustomerRef.Customer_Service();
service.UseDefaultCredentials = true;

// Run through records marked for deletion and delete those
DataView dv = new DataView(this.dataTable, "", "", DataViewRowState.Deleted);
foreach (DataRowView drv in dv)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{


service.Delete(((CustomerRef.Customer)obj).Key);
}
}

What is the problem??

freddy.dk

It looks like you are using the sample from part 2 (I probably forgot to remove the web references - I was going to use them in part 3)
Part 4 adds error handling - and I also explain in Part 4 that if any error happens, excel swallows the exception and nothing ever happens.

Did you expose the Customer Card - page 21 or the Customer List - it needs to be the Customer Card.
You can also try to right click the reference and update the Web Reference to see whether that helps.

/Freddy

arvidnormann

Hi

Thanks for the quick response.

I am running part 4. There are 3 webreferences in the project.

i have exposed

page 21, 30 and 26 which all is card. I have updatede the webreference but it is still not working.

I only see 5 records where i run the project in excel.
Update: removed the filter from the code

Waldo

I just did the entire tutorial, and managed to get it working. Thanks, Freddy!

The only think I was struggling with was the fact that it errored out when called from the RTC client. Reason was unknown, so I removed the errorhandling (try..catch) in the com class (EditInExcel). Then, the error popped out properly: I changed the language to Dutch(Belgium), and it doesn't handle that very well when working with Excel... (I had to install the language pack, which I didn't). Anyway, it works for now. If I understand it fully, that's another question .


When I was debugging, I came accross the same issue as arvid (not from RTC or running the project, but by executing the copied excel file directly from the directory).
But after using the right language ... everything worked.

freddy.dk

Arvid - the code you list in your sample doesn't exist in the NAVTemplate_Final.zip file.
There is only one reference to the Customer WS and that is from the Sheet1 startup.

Also - I did not know about language issues - Waldo - maybe you could add a comment explaining what you did?

Thanks

/Freddy

Waldo

Will do ...

arvidnormann

have just downloaded the file again from http://www.freddy.dk/NAVTemplate_Final.zip

there are these web reference in code on sheet1.cs

private void Sheet1_Startup(object sender, System.EventArgs e)
{
if (string.IsNullOrEmpty(this.page))
{
// Test mode
this.page = "Customer";
this.view = "SORTING(No.) WHERE(Balance (LCY)=FILTER(>10,000))";
}

switch (this.page)
{
case "Customer":
this.service = new CustomerRef.Customer_Service();
break;
case "Vendor":
this.service = new VendorRef.Vendor_Service();
break;
case "Item":
this.service = new ItemRef.Item_Service();
break;
default:
MessageBox.Show(string.Format("Page {0} is not setup for Edit In Excel. Please contact your system administrator", this.page), "Microsoft Dynamics NAV", MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
}
if (this.service != null)
{
this.service.UseDefaultCredentials = true;
Load();
}
}

arvidnormann

Have just run the code from part 4 with same result with the debugger. see attached file

"if (!service.Delete((string)drv[0]))"

freddy.dk

That surely looks like the part 4 code.
So, if you update a phone number on a customer and save - it doesn't write anything back?
and it doesn't invoke the update method?

have you tried singlestepping through the loop and see whether it doesn't detect the changes (DataView would be empty) or whether it detects the changes and cannot fine the objects in the object list - or where in the code it decides to abort the update.

Thanks

/Freddy

freddy.dk

Oh - BTW, have you tried to do what Waldo did?

/Freddy

Waldo

I think his problem occurs when deleting a line.

but I tried deleting and inserting .. and all works fine .

arvidnormann

The problem is insert modify and delete.



Looks at it when i have eaten

arvidnormann

Hi.

Have done some more testing.

Insert works.
Delete and edit does not work.

// Run through records marked for delete, create or modify
DataView dv = new DataView(this.dataTable, "", "", DataViewRowState.Deleted | DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
foreach (DataRowView drv in dv)
{
bool retry;
do

only finds records for insert not for edit and delete

freddy.dk

What Office version are you running?
What Visual Studio version are you running?

arvidnormann

i am am using CTP4 Navision 2009.

visual studio 2008 and office 2007 is installed on the virtual pc.

freddy.dk

Sorry, but I don't have a clue why you don't get changes and deletions in the DataView.
Only long shot would be to ask whether you have VS2008 SP1 installed? - but I know it is a long shot.

It really should be simple, and I guess once you figure out why you don't see those in the DataView (if :? ) - please let us know, other people might run into the same.

Thanks

/Freddy

arvidnormann

i have found a way to get it to work.

Unzipped ctp4 from partner source again. Had changed the language, on the ctp4 that I was running before.

And now it is working.

But can still not figure out why it is not working on the old VPC.

Thanks for all your help. Super example,

I am really looking forward to the release of dynamicsNAV 2009. Super new stuff.

I am currently developing a SharePoint site using web service from Navision. Instead of using Employee Portal. (Looking forward to dynamicsNAV 2009 SP1)

arvidnormann

New problem.

on the customer table in Navision there is the following code onvalidate


IF ("Search Name" = UPPERCASE(xRec.Name)) OR ("Search Name" = '') THEN
"Search Name" := Name;

if I change a customer name in excel the onvalidate trigger is not executed corectly everytime.

when I insert it works fine, but not when I edit, it is not executed correctly where there is something in "Search Name" before.

In which order is the onvalidate triggers executed.