Tag Archives: Automation

Running Code on the Client without deploying COM objects

Posted on by
Reply

Yes, it can be done!

No, it isn’t .net code – nor AL code.

Why?

It started out as me being a little too fast when stating that you could easily download a file to the Client and attach it to Outlook without any user interaction – and as you might know that is true, but you might also know that if you go the recommended route:

FILE.DOWNLOAD(FileName, ”, ‘<TEMP>’,”, ToFile);
Mail.NewMessage(”,”,Name,”,ToFile,TRUE);
FILE.ERASE(FileName);

Then you will get an e-mail that looks like this:

image

People might not assume that this actually is Invoice no. 10103 in PDF format. What you of course want to have is:

image

So, how do we get there.

I actually did respond to a post on mibuso a while back (http://www.mibuso.com/forum/viewtopic.php?f=32&t=29806) about how this could be done, but that would involve a COM object deployed to all clients and not everybody wants that (although I have posted a method on how to do this automatically).

The problem here is, that file.download always places the file in a temporary directory with a temporary filename – and there is (to my knowledge) no other way to copy a file to the Client.

Assuming that this is correct, how do we then rename a file on the client without having to deploy COM objects?

I said without deploying COM objects, not without USING COM objects

As you know, we can run COM objects on the server or on the Client and one of the COM objects, which ships with Windows can come in handy here. The Windows Script Host – if we instantiate this COM object we can actually give the component a VB Script to execute in the context of the COM component (which would be either on the Server or on the Client).

Windows Script Host

Yes, WSH is legacy – but it is widely used and it is included on all Windows versions from Windows XP and up. I am not going to make this a tutorial on VBScript and WSH – for that you can find a number of good posts on the internet – or start by reading msdn

http://msdn.microsoft.com/en-us/library/ms950396.aspx

Creating a script function / method

The method AddCode on the Windows Script Host COM object is used to add sourcecode to the component.

Note, that you need to add an entire function / method in one call and note, that each line needs to be terminated by a CR.

You also need to specify what language you use, the control supports JScript and VBScript.

A VBScript function which returns Hello <name> could look like this:

function Hello(who)
Hello = “Hello “&who
end function

Creating this function in a Client side COM component could look like:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;
objScript.AddCode(
‘function Hello(who)’+CR+
‘  Hello = “Hello “&who’+CR+
‘end function’);
END;

The way I write this is, that I try to maintain the structure of the VBScript even though it is inside a string in NAV, maybe I am fooling myself, but I think it is more readable.

Invoking a script function / method

There are two ways of invoking a script method:

Eval – used to invoke a function, and get a return value back.

The above function could be called using

MESSAGE(FORMAT(objScript.Eval(‘Hello(“Freddy”)’)));

Note – when calling functions, VBScript wants your parameters embraced by parentheses.

ExecuteStatement – used to invoke a method which doesn’t return anything

Let’s rewrite the above function to a method and have the method show a MessageBox:

The VBScript could look like:

sub Hello(who)
MsgBox “Hello “&who, 0, “Title”
end sub

and creating this function in a COM object and calling the method could look like:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;
objScript.AddCode(
‘sub Hello(who)’+CR+
‘  MsgBox “Hello “&who, 0, “Test”‘+CR+
‘end sub’);
objScript.ExecuteStatement(‘Hello “Freddy”‘);
END;

Note – when calling methods (or sub’s) VBScript does NOT want the parameters embraced by parentheses.

Some sample scripts

Rename a temporary file

function RenameTempFile(fromFile, toFile)
set fso = createobject(“Scripting.FileSystemObject”)
set x = createobject(“Scriptlet.TypeLib”)
path = fso.getparentfoldername(fromFile)
toPath = path+””+left(x.GUID,38)
fso.CreateFolder toPath
fso.MoveFile fromFile, toPath+””+toFile
RenameTempFile = toPath
end function

As you can see, I am doing exactly what I responded on the mibuso thread here – just in VBScript instead – which then requires no client side install.

BTW this function is actually used in ClausL’s post about sending e-mail with PDF attachments, which proves that we do talk with our colleagues at Microsoft:-). Note that there is no good way of creating a GUID from VBScript – I (mis)use the fact that every instance of Scriptlet.TypeLib gets assigned a new GUID.

Get Machine name

function GetComputerName()
set net = createobject(“wscript.network”)
GetComputerName = net.ComputerName
end function

I know, that you also can read an environment variable – but this way you can actually get all kind of information on the network though this.

Launch an application

sub Notepad()
set shell = createobject(“WScript.Shell”)
shell.Run “notepad.exe”
end sub

Yes, you can do this by using the Shell object directly in NAV, like:

Shell       Automation       ‘Microsoft Shell Controls And Automation’.Shell

CREATE(objShell,True,true);
objShell.Open(‘c:\windows\system32\notepad.exe’);

I just wanted to show that you that stuff like this can be done in VBScript too, and note, that the Shell object in VBScript and in NAV is not the same.

Asking a simple question

function Input(question, title, default_answer)
Input = InputBox(question, title, default_answer)
end function

A couple of partners have told me, that they are unhappy with the discontinuation of INPUT from NAV and having to create pages for even the simplest questions. Running the following code:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;

  objScript.AddCode(
‘function Input(question, title, default_answer)’+CR+
‘  Input = InputBox(question, title, default_answer)’+CR+
‘end function’);

  s := objScript.Eval(‘Input(“How old are you?”, “A simple question”, “”)’);
MESSAGE(s);
END;

Brings up this dialog on my machine:

image

Who knows, maybe somebody can use this as an alternative to INPUT.

Read the RoleTailored Client configuration file

function ReadConfigFile()
set shell = CreateObject(“WScript.Shell”)
folder = shell.ExpandEnvironmentStrings(“%LOCALAPPDATA%”)
if folder = “” then folder = shell.ExpandEnvironmentStrings(“%USERPROFILE%”)&”Local SettingsApplication Data”
  filename = folder&”MicrosoftMicrosoft Dynamics NAVClientUserSettings.config”
set fso = createobject(“Scripting.FileSystemObject”)
set file = fso.OpenTextFile(filename, 1)
ReadConfigFile = file.ReadAll()
end function

Note that I have NOT tested this function under Windows XP – I know that LOCALAPPDATA is not defined on Windows XP and I think the line:

  if folder = “” then folder = shell.ExpandEnvironmentStrings(“%USERPROFILE%”)&”Local SettingsApplication Data”

should take care of finding the right folder – if anybody can confirm that, then add that as a comment to this post.

Bringing up a MESSAGE with the outcome of this function on my machine gives me this dialog:

image

I don’t know whether that could come in handy, but maybe it can spawn off some good ideas.

Wrapping up

As you can see, you can do a lot of things in VB Script on the Client (or on the Server). There are a number of scripts you can find on the internet to work with the A/D (create, delete and enumerate users).

Of course there a limitations as to what you can do in VBScript and it isn’t a real alternative to writing a COM component, but for something it is easy and straightforward – and it doesn’t require any client side installation of components and this works in both Classic and RTC.

You can download the rename function from ClausL’s post about sending e-mail with PDF attachments. You will need to do copy, paste and maybe modify the other samples in order to use them.

 

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel R2 – Part 1 (out of 2)

Posted on by
Reply

This post assumes that you have read the 4 step walkthrough of how to build the Edit In Excel demo from November 2008. You can find the parts here: Part 1, Part 2, Part 3, Part 4 and the Bug Fix.

In this post I will talk about what is needed in order to be able to save an Excel spreadsheet on a local disc, edit it offline and then submit your changes later.

My first assumption was that this was just a few small changes, but I should learn more…

Goal

The success scenario is the one where a NAV user decides to take the customer table offline in Excel to do modifications. The Excel spreadsheet is saved to a local hard drive and edited while not connected to the network.

When the user reconnects to the network, he can submit his changes, which will be send to NAV through Web Services and validated according to business rules.

The location of NAVTemplate.vsto

Let’s just try to save the spreadsheet in our Documents folder and see what happens if we open the spreadsheet. Not surprisingly we get an error telling us that it was unable to locate NAVTemplate.vsto

image

An immediate solution is to save the spreadsheet next to the .vsto and then it seems to work better, but the spreadsheet is not attached to NAV anymore, we cannot save changes and reload crashes with an exception.

The .vsto file is our deployment manifest, which is our managed code extension to the spreadsheet and of course this is needed in order for the spreadsheet to work.

You can read more about the architecture of Document-Level Customizations here:

http://msdn.microsoft.com/en-us/library/zcfbd2sk.aspx

Looking at the ServerDocument interface,which we use in the NAVEditInExcel solution, it has a property called DeploymentManifestUri, which is the location of the .vsto file. Adding the following line to the NAVEditInExcel project

serverDoc.DeploymentManifestUrl = new Uri(System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), “NAVTemplate.vsto”));

will cause the document to be created with an absolute reference to the NAVTemplate.vsto, and this will solve the problem with the spreadsheet not being able to locate the .vsto. In fact if this is a network location, it should even be possible to send the Excel spreadsheet to somebody else who should be able to modify the document and post the changes.

When doing this I decided to make another change in the NAVEditInExcel project. As you know it creates a temporary template in the same location as the DLL and the VSTO – and then it deletes it again. This is really not the preferred location of temporary files – we should create temporary files in the Temp folder so we change the line setting the template name to:

string template = System.IO.Path.Combine(System.IO.Path.GetTempPath(), page + “.xltx”);

since we don’t need to have the .xltx next to the .vsto anymore.

What do we need to save?

Members of the Sheet1 class marked with [Cached] will be saved together with the document – we know that from the way we transfer the page name and the current view to the spreadsheet. Thinking about it, the only things we need to save together with the document is the dataTable (which at any time contains the changes made by the user) and the objects collection (which are the objects returned from the Web Service).

The DataTable class implements IXmlSerializable and the objects collection is (as we know) an array of objects returned from a web Service provider and since these objects where send over the wire from a Web Service they of course also implements IXmlSerializable.

The fields collection cannot be saved, since the NAVFieldInfo class uses the PropertyInfo class, which cannot be serialized. The Service connection of course cannot be serialized either – nor can the datalist class – as the matter of fact, the datalist class shouldn’t be a member at all, it should be moved to the AddDataToExcel method as a local variable.

Problem now is, that if we just mark the dataTable and objects members with [Cached] we need to initialize them in the NAVEditInExcel project and there is no way we can instantiate the objects collection at that time with the right type.

A little more on cached data objects in Office documents

http://msdn.microsoft.com/en-us/library/ms178808(VS.80).aspx

and then how to programmatically add members to the cached objects

http://msdn.microsoft.com/en-us/library/48b7eyf3(VS.80).aspx

using this knowledge gives us the following two lines, we want to add after we have instantiated the dataTable and the objects array.

// Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);

Now we just need to determine that the spreadsheet was loaded from a file (and not started from NAV) and then act differently.

Refactoring some code

We need to do a little refactoring of code in order to make things work. In the existing solution the PopulateFieldsCollection method creates the fields collection, but it also creates an empty dataTable class. Since we now store the dataTable class and not the fields collection we need as the first thing in the new spreadsheet to create the fields collection (a lot of things is depending on this). This is the new PopulateFieldsCollection (and AddField):

/// <summary>
/// Populate Fields Collection with NAVFieldInfo for all properties in the record
/// Should works with any NAV 2009 Page exposed as WebService
/// </summary>
/// <param name=”objType”>Type of Object (typeof(Customer), typeof(Vendor), …)</param>
/// <param name=”fieldsType”>Type of the Enum holding the property names</param>
private void PopulateFieldsCollection(Type objType, Type fieldsType)
{
this.fields = new List<NAVFieldInfo>();

    // Key property is not part of the Enum
// Add it manually as the first field
AddField(“Key”, objType);

    // Run through the enum and add all fields
foreach (string field in Enum.GetNames(fieldsType))
{
AddField(field, objType);
}
}

/// <summary>
/// Create a NAVFieldInfo for a field
/// </summary>
/// <param name=”field”>Field name</param>
/// <param name=”objType”>Type of Object in which the field is (typeof(Customer), typeof(Vendor), …)</param>
private void AddField(string field, Type objType)
{
field = NAVFilterHelper.VSName(field);
PropertyInfo pi = objType.GetProperty(field, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
if (pi != null)
{
NAVFieldInfo nfi = NAVFieldInfo.CreateNAVFieldInfo(field, pi, objType.Namespace);
if (nfi != null)
{
// If we encounter unknown Field Types, they are just ignored
this.fields.Add(nfi);
}
}
}

The Load function in the old spreadsheet does a number of things. It populates the Fields collection, loads the data, populates the dataTable and add the dataTable to the spreadsheet.

We will remove and create one function called LoadDataTable, which will create a dataTable (based on the fields collection), load the data from Web Services and populate the dataTable.

/// <summary>
/// Load Records from NAV via Web Services
/// </summary>
private void LoadDataTable()
{
// Create Data Table object based on fields collection
this.dataTable = new DataTable(this.page);
foreach (NAVFieldInfo nfi in this.fields)
{
this.dataTable.Columns.Add(new DataColumn(nfi.field, nfi.fieldType));
}

    SetFilters(this.view);
this.objects = this.service.ReadMultiple();
// Populate dataTable with data
foreach (object obj in this.objects)
{
DataRow dataRow = this.dataTable.NewRow();
foreach (NAVFieldInfo nfi in this.fields)
{
dataRow[nfi.field] = nfi.GetValue(obj);
}
this.dataTable.Rows.Add(dataRow);
}
this.dataTable.AcceptChanges();
}

As you can see, this is pieces of the PopulateFieldsCollection, Load and PopulateDataTable functions – as the matter of fact, you can delete the PopulateDataTable function as well. BTW the AcceptChanges call was moved from AddDataToExcel it needs to be together with the code populating the dataTable.

Right now my startup code in my spreadsheet has changed to

if (this.service != null)
{
this.service.UseDefaultCredentials = true;

    // Create Fields collection
this.PopulateFieldsCollection(this.service.GetObjectType(), this.service.GetFieldsType());

    Application.ScreenUpdating = false;

    bool loadData = this.ListObjects.Count == 0;
if (!loadData)
{
MessageBox.Show(“Spreadsheet loaded from disc”);
}
if (loadData)
{
// Load Data into dataTable from Web Services
this.LoadDataTable();
// Add dataTable to Excel Spreadsheet
this.AddDataTableToExcel();

        // Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);
}

    Application.ScreenUpdating = true;
}

from just invoking Load() before.

So we populate the fields collection and then we check whether or not there is a ListObject in the document (remember the Controls collection was empty). If this is the case we must do something (for now we just display a messagebox).

If we are called from NAV (loadData becomes true) we will load the data and call AddDataTableToExcel (renamed from AddDataToExcel) and that should work.

If we try to compile now, we will see that the Reload() method uses Load() as well. We need to change Reload to

/// <summary>
/// Reload data from NAV (delete old dataTable, and load new data)
/// </summary>
internal void Reload()
{
Application.ScreenUpdating = false;

    // Remove List Object
if (this.dataTable != null)
this.Controls.RemoveAt(0);
else
this.ListObjects[1].Delete();

    // Load Data into dataTable from Web Services
this.LoadDataTable();
// Add dataTable to Excel Spreadsheet
this.AddDataTableToExcel();

    // If this reload was in fact a reattach of the spreadsheet, start caching dataTable and objects again
if (!this.IsCached(“dataTable”))
{
// Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);
}

    Application.ScreenUpdating = true;
}

Note that we remove the old listobject in two different ways based on whether or not dataTable is set. dataTable is null if the spreadsheet has been detached from NAV – I will touch more upon that later. This is also the reason why we restart Caching the dataTable and objects if in fact this was a reattach.

The solution should work as before now – the only major difference is, that if you save the spreadsheet on the disc and try to load it again it should not give an exception telling you, that you cannot overlap a table with another table, instead it should give you something like:

image

This is of course not the final solution, but it shows us that we are on the right track.

Restoring the “State” of the spreadsheet

To make a long story short, the lines we need in order to restore the managed list object are:

// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Create a new VSTO ListObject – data bound
this.AddDataTableToExcel();

Meaning that we remove the unmanaged ListObject and we add the managed ListObject to Excel, seems pretty easy. But what if the document is saved on disc and you add a field to the Customer Page (+ update your web reference and recompile your NAVTemplate) then the managed extension assembly doesn’t match the saved spreadsheet anymore and the above logic wouldn’t work.

In many cases we could just say that we don’t care – but given the ability to save spreadsheets that are connected to Web Services and reload data adds another dimension to the entire Excel thing. You can have spreadsheets that contain a lot of other things than your dataTable and you might not be pleased with the fact that you loose the NAV Web Service connection if this happens.

I decided to build in a way of determining this and give the user a couple of options:

// If the spreadsheet was detached already – just ignore
if (this.IsCached(“dataTable”))
{
// We have loaded a saved spreadsheet with data
// Check that the VSTO assembly (fields) matches the spreadsheet
bool fieldsOK = this.dataTable.Columns.Count == this.fields.Count;
if (fieldsOK)
{
for (int i = 0; i < this.fields.Count; i++)
if (this.dataTable.Columns[i].Caption != this.fields[i].field)
fieldsOK = false;
}
if (!fieldsOK)
{
// Schema mismatch – cannot link back to NAV
switch (MessageBox.Show(“Customer Card definition has changed since this spreadsheet was save. Do you want to re-establish link, reload data and loose changes?”, “Error”, MessageBoxButtons.YesNoCancel))
{
case DialogResult.Cancel:
// Quit
Application.Quit();
break;
case DialogResult.Yes:
// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Signal reload data and reestablish link
loadData = true;
                this.StopCaching(“dataTable”);
this.StopCaching(“objects”);
                break;
case DialogResult.No:
// Detach spreadsheet from NAV
              this.dataTable = null;
              this.objects = null;
              this.StopCaching(“dataTable”);
              this.StopCaching(“objects”);
                break;
}

    }
else
{
// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Create a new VSTO ListObject – data bound
this.AddDataTableToExcel();
}
}

3 options – Cancel quits the spreadsheet open command and no harm has been done, Yes removes the old table and sets the loadData to true (meaning that the spreadsheet will reload data as if it was opened from NAV) and No will detatch the spreadsheet from NAV (setting the dataTable to null and stop caching dataTable and objects). Note that if you ever press Reload it will re-attach the spreadsheet and load data from NAV again (the reason for checking whether the dataTable was null in Reload).

Yes or No will of course not touch the original spreadsheet and you can always save in a new name.

BTW – we need one simple check in Save() as well

/// <summary>
/// Save Changes to NAV via Web Service
/// </summary>
internal void Save()
{
if (this.dataTable == null)
{
MessageBox.Show(“Spreadsheet was detached from NAV, cannot perform Save!”);
}
else if (DoSave())
{
Reload();
}
}

The only extra tihing you need is to add the following line right before the return statement in DoSave():

this.dataTable.AcceptChanges();

Without this line you cannot edit the data, save changes to NAV, edit more data and then save locally.

Done

We are done – we now have a template you can use for modifying NAV data. You can save the spreadsheet locally and modify it while you are offline and later, when you come back online you can push your changes to NAV and this is when validation happens.

As usual you can download the project here http://www.freddy.dk/NAVTemplateR2.zip – there are no changes to what’s needed in NAV, so you should use the objects from the original Edit In Excel walkthrough inside C/AL.

BTW. If you decide to use the source, you probably need to right-click the Web References and invoke Update Web Reference to make sure that the Web Reference matches your Customer, Item and Vendor Card Pages.

Part 2?

So why is this a part 1 out of  2 you might ask yourself right now?

Reason is that I want to create a better conflict resolution in the spreadsheet. With the solution we have build now, any change to a record by anybody will cause you to loose your changes in Excel. Wouldn’t it be nice if we were able to determine that we only changed the location code in Excel, so just because somebody changed the credit limit on a couple of customers, we should still be able to re-apply our changes without having to type them in again.

This is what R2 step 2 is all about – a better conflict resolution mechanism.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 4 (out of 4)

Posted on by
Reply

If you haven’t read part 3, part 2 (and part 1), you should do so before continuing here.

We have seen how to put code inside Excel, using VSTO and connect to NAV 2009 Web Services. We have seen how to add this to a table inside Excel and how to write data back to NAV through Web Services. We can delete, add and modify records in Excel and we can even do so with both Customers, Vendors and Items. We have added support for NAV filters, error handling and the only thing we are missing to have a very good experience is integrating the whole thing into NAV.

So we better do that now!

Disclaimer

Most of the people reading this are probably superior AL coders compared to me. I really only started coding in AL last year and I am still struggling to understand how things works. So, if I do something stupid in AL – just tell me in a comment how to do it smarter – thanks.

Actions in NAV 2009

What we want to do is

image

Add an action to the menu, which launches Excel, reads the same data as we have in the list place and allows the user to modify that data.

But we need to start in a different area – we need a COM object, which our action can invoke.

Creating a COM object

First of all we will create a COM object, which contains one function.

public void EditInExcel(string page, string view)

I do think there are a number of tutorials that explains how to do this, so I will run over the steps very quickly.

  1. In the same solution as the NAVTemplate, create a new project – type Class Library and name the project NAVEditInExcel
  2. Rename class1.cs to NAVEditInExcel.cs – and say Yes to the question whether you want to rename the class as well.
  3. Select Properties on the project (not the solution)
    1. On the Build tab, set the output path to ..NAVTemplatebinDebug in order to share the output path the the Excel Spreadsheet
    2. On the Build events tab, we need to register the COM object to make it visible to NAV. Add the following Post Build Event: C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm NAVEditInExcel.dll /codebase /tlb
    3. On the Signing tab, check the Sign the Assembly checkbox and select New key in the combo box, name the key and protect it with a password if you fancy.
  4. Open the AssemblyInfo.cs (under Properties in the Solution Explorer)
    1. Add using system; to the using statements
    2. Add [assembly: CLSCompliant(true)] under the line with [assembly: ComVisible(false)].
  5. Open the source for the NavEditInExcel.cs
    1. Add using System.Runtime.InteropServices; to the using statements
    2. Create an Interface and change the class to be:

[ComVisible(true)]
[Guid(“A2C51FC8-671E-4135-AD27-48EDC491E76E”), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVEditInExcel
{
void EditInExcel(string page, string view);
}

[ComVisible(true)]
[Guid(“233E0C7F-2276-4142-929C-D6BA8725D7B4”), ClassInterface(ClassInterfaceType.None)]
public class NAVEditInExcel : INAVEditInExcel
{
public void EditInExcel(string page, string view)
{

        // Code goes here…
    }
}

Now you should be able to build the COM object and see it inside NAV when adding a variable of type automation.

Adding the Action in NAV

Open up the Classic Client and design the Customer List Place.

Insert an Action on the Customer List Place called Edit In Excel and edit the code for that (btw. the Image Name for the Excel icon is Excel)

In the code for that Action – create a local variable called NAVEditInExcel of type Automation and select the NAVEditInExcel.NAVEditInExcel COM object to use and add the following code:

CREATE(NAVEditInExcel, TRUE, TRUE);
NAVEditInExcel.EditInExcel(TABLENAME, GETVIEW(TRUE));

That’s it on the NAV side, but of course we didn’t make all the code necessary in the COM object yet.

If you try to launch the Action you will be met by the security dialog

image

Which you of course want to hit always allow to – else you will get this dialog every time you say Edit In Excel.

BTW – If you hit Never allow – you will never be allowed to Edit in Excel – not until you have deleted your PersonalizationStore.xml at least.

Completing the COM object

Having that hooked up we really just need to launch that damn spreadsheet with the right parameters.

We need to add 3 .NET references to the COM object:

  • System.Windows.Forms
  • Microsoft.Office.Interop.Excel
  • Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0

and the following 3 using statements:

using Microsoft.VisualStudio.Tools.Applications;
using System.Windows.Forms;
using System.Reflection;

and last but not least, add the following EditInExcel method:

public void EditInExcel(string page, string view)
{
try
{
// Copy the original template to a new template using the page name as name!
string originalTemplate = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), “NAVTemplate.xltx”);
if (!System.IO.File.Exists(originalTemplate))
{
MessageBox.Show(string.Format(“The template: ‘{0}’ cannot be found!”, originalTemplate), “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string template = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), page + “.xltx”);
while (System.IO.File.Exists(template))
{
try
{
System.IO.File.Delete(template);
}
catch (System.IO.IOException)
{
if (MessageBox.Show(string.Format(“The template: ‘{0}’ is locked, cannot open spreadsheet”, template), “Error”, MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) != DialogResult.Retry)
{
return;
}
}
}
System.IO.File.Copy(originalTemplate, template);

        // Open the new template and set parameters
ServerDocument serverDoc = new ServerDocument(template);
CachedDataHostItem host = serverDoc.CachedData.HostItems[0];
host.CachedData[“page”].SerializeDataInstance(page);
host.CachedData[“view”].SerializeDataInstance(view);
serverDoc.Save();
serverDoc.Close();

        // Create a new spreadsheet based on the new template
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
excelApp.Workbooks.Add(template);

        // Erase template
System.IO.File.Delete(template);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, “Critical error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

This method really does 4 things:

  1. Copy the NAVTemplate.xltx to a new template called Customer.xltx (that is if the page name is customer) which is a temporary template
  2. Open the template as a ServerDocument and set the parameters
  3. Ask Excel to create a new spreadsheet based on this template
  4. Erase the template

That was easy!

Oh – there is one things I forgot to say, you need to specify in the Excel Spreadsheet that the page and view variables are cached data (meaning their value are saved with Excel) – this is done by adding an attribute to the variables:

[Cached]
public string page;

[Cached]
public string view;

Having done this, you can open the spreadsheet as a Serverdocument, get and set the value of these parameters and save the document again, pretty sweet way of communicating parameters to Excel or Word – and this will definitely come in handy later.

Adding the action other pages

Having verified that we can edit customers in Excel we can now add the same action as above to the Vendor and the Item List Places.

You can either follow the same steps as above – or you can copy the action and paste it on the other List Places.

Note that you cannot build the Visual Studio solution while you have NAV 2009 open. When NAV loads the COM object, it keeps a reference to it until you close NAV.

Last but not least – this should work from the classic client as well – if you want to add the functionality there – I haven’t tried it.

That’s it folks

That completes the Edit In Excel in Part 1 through 4

As always, there is absolutely no warranty that this code works for the purpose you need it to, but these posts show how to do some things and feel free to use pieces of this or use it as a base to build your own solution using Excel – the code is free – a referral to my blog is always a good way of acknowledgement.

I hope you can make it work, that it is useful and you can download the final solution here: http://www.freddy.dk/NAVTemplate_Final.zip

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Transferring binary data to/from WebServices (and to/from COM (Automation) objects)

Posted on by
Reply

A number of people have asked for guidance on how to transfer data to/from COM and WebServices in NAV 2009.

In the following I will go through how to get and set a picture on an item in NAV through a Web Service Connection.

During this scenario we will run into a number of obstacles – and I will describe how to get around these.

First of all – we want to create a Codeunit, which needs to be exposed to WebServices. Our Codeunit will contain 2 functions: GetItemPicture and SetItemPicture – but what is the data type of the Picture and how do we return that value from a WebService function?

The only data type (supported by Web Services) that can hold a picture is the BigText data type.

We need to create the following two functions:

GetItemPicture(No : Code[20];VAR Picture : BigText)
SetItemPicture(No : Code[20]; Picture : BigText);

BigText is capable if holding binary data (including null terminals) up to any size. On the WSDL side these functions will have the following signature:

image

As you can see BigText becomes string – and strings in .net are capable of any size and any content.

The next problem we will face is, that pictures typically contains all kinds of characters, and unfortunately when transferring strings to/from WebServices there are a number of special characters that are not handled in the WebServices protocol.

(Now you wonder whether you can have <> in your text – but that isn’t the problem:-)

The problem is LF, CR, NULL and other characters like that.

So we need to base64 (or something like that) encode our picture when returning it from Web Services. Unfortunately I couldn’t find any out-of-the-box COM objects that was able to do base64 encoding and decoding – but we can of course make one ourselves.

Lets assume for a second that we have a base64 COM object – then this would be our functions in AL:

GetItemPicture(No : Code[20];VAR Picture : BigText)
CLEAR(Picture);
Item.SETRANGE(Item.”No.”, No, No);
IF (Item.FINDFIRST()) THEN
BEGIN
  Item.CALCFIELDS(Item.Picture);
// Get Temp FileName
TempFile.CREATETEMPFILE;
FileName := TempFile.NAME;
TempFile.CLOSE;

  // Export picture to Temp File
Item.Picture.EXPORT(FileName);

  // Get a base64 encoded picture into a string
CREATE(base64);
Picture.ADDTEXT(base64.encodeFromFile(FileName));

  // Erase Temp File
FILE.ERASE(FileName);
END;

SetItemPicture(No : Code[20];Picture : BigText)
Item.SETRANGE(Item.”No.”, No, No);
IF (Item.FINDFIRST()) THEN
BEGIN
// Get Temp FileName
TempFile.CREATETEMPFILE;
FileName := TempFile.NAME;
TempFile.CLOSE;

  // Decode the bas64 encoded image into the Temp File
CREATE(base64);
base64.decodeToFile(Picture, FileName);

  // Import picture from Temp File
Item.Picture.IMPORT(FileName);
Item.Modify();
// Erase Temp File
FILE.ERASE(FileName);
END;

A couple of comments to the source:

  • The way we get a temporary filename in NAV2009 is by creating a temporary file, reading its name and closing it. CREATETEMPFILE will always create new GUID based temporary file names – and the Service Tier will not have access to write files in e.g. the C:\ root folder and a lot of other places.
  • The base64 automation object is loaded on the Service Tier (else it should be CREATE(base64, TRUE, TRUE);) and this is the right location, since the exported file we just stored is located on the Service Tier.
  • The base64.encodeFromFile reads the file and returns a very large string which is the picture base64 encoded.
  • The ADDTEXT method is capable of adding these very large strings and add them to a BigText (BTW – that will NOT work in the classic client).
  • We do the cleanup afterwards – environmental protection:-)

So, why does the ADDTEXT support large strings?

As you probably know, the ADDTEXT takes a TEXT and a position as parameter – and a TEXT doesn’t allow large strings, but what happens here is, that TEXT in C# becomes string – and the length-checking of TEXT variables are done when assigning variables or transferring parameters to functions and the ADDTEXT doesn’t check for any specific length (which comes in handy in our case).

The two lines in question in C# looks like:

base64.Create(DataError.ThrowError);
picture.Value = NavBigText.ALAddText(picture.Value, base64.InvokeMethod(@”encodeFromFile”, fileName));

Note also that the base64.decodeToFile function gets a BigText directly as parameter. As you will see, that function just takes an object as a parameter – and you can transfer whatever to that function (BigText, Text, Code etc.). You actually also could give the function a decimal variable in which case the function would throw an exception (str as string would return NULL).

So now you also know how to transfer large strings to and from COM objects:

  1. To the COM object, you just transfer a BigText variable directly to an object parameter and cast it to a string.
  2. From the COM object to add the string return value to a BigText using ADDTEXT.
  3. You cannot use BigText as parameter to a by-ref (VAR) parameter in COM.

In my WebService consumer project I use the following code to test my WebService:

// Initialize Service
CodeUnitPicture service = new CodeUnitPicture();
service.UseDefaultCredentials = true;

// Set the Image for Item 1100
service.SetItemPicture(“1100″, encodeFromFile(@”c:\MandalayBay.jpg”));

// Get and show the Image for Item 1001
string p = “”;
service.GetItemPicture(“1001″, ref p);
decodeToFile(p, @”c:\pic.jpg”);
System.Diagnostics.Process.Start(@”c:\pic.jpg”);

and BTW – the source code for the two functions in the base64 COM object are here:

public string encodeFromFile(string filename)
{
FileStream fs = File.OpenRead(filename);
BinaryReader br = new BinaryReader(fs);
int len = (int)fs.Length;
byte[] buffer = new byte[len];
br.Read(buffer, 0, len);
br.Close();
fs.Close();
return System.Convert.ToBase64String(buffer);
}

public void decodeToFile(object str, string filename)
{
FileStream fs = File.Create(filename);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(Convert.FromBase64String(str as string));
bw.Close();
fs.Close();
}

If you whish to download and try it out for yourself – you can download the sources here:

The two Visual Studio solutions can be downloaded from http://www.freddy.dk/VSDemo.zip (the base64 COM object and the VSDemo test project)

The NAV codeunit with the two functions above can be downloaded from http://www.freddy.dk/VSDemoObjects.fob.

Remember that after importing the CodeUnit you would have to expose it as a WebService in the WebService table:

image

And…. – remember to start the Web Service listener (if you are running with an unchanged Demo installation).

The code shown in this post comes with no warranty – and is only intended for showing how to do things. The code can be reused, changed and incorporated in any project without any further notice.

Comments or questions are welcome.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV