Advertisements

Converting Office Files into PDF from Navision

Today in this post i will walkthrough how we can convert any MS Office File into PDF.

It have been many time required to convert any attached file with the record in Navision to PDF format.

To perform this action we will require a tool which can be downloaded from:

OfficeToPDF download link :- http://officetopdf.codeplex.com/releases/view/118190

Extract and save the OfficeToPDF.exe file to some Folder.

For this Walkthrough i have created a Field in Customer Table to save the File Path of the Office File which we want to convert into PDF.

PDFConvMerge-1

You can add Dialog box to select the File from the specified Folder.

For this demo i have kept it simple Text Field where you can add your File Path and Office File Name. It is assumed that only Office File will be attached here.

For Setup purpose i have created one Setup Table & Page where we will store our other required Folder paths.

PDFConvMerge-2

Here we have given provision to save the Path of the Office to PDF Convertor Tool file Path & Temporay working Folder.

I have used the shared folder for all the paths so that it is not system specific, also make sure you give required permission on this folder for all the operating accounts.

Now our Next task will be to write a function which will perform this conversion part.

PDFConvMerge-3

Above Function Takes the File Name from the Customer Card we stored above on Customer Card. Takes the Path of the Office To PDF Convertor Utility and Tmp working Folder.

Prepares the PDF file Name along with Path stored in Setup.

Prepares the command to invoke Convert Utility using WsShell and returns the PDF file File Name along with path as defined in the Setup.

Now you can use this File for futher purpose as required.

To keep it simple no additional code of test and other required routine task is not added.

Now you know the Logic you can tweak the functionality as per your requirement.

I will comeup with more information in my up comming posts, till then keep exploring and learning.

 

Advertisements

Configuring Single Sign-On using Office 365 on Navision 2016

Today we will see how we can enable Single Sign-On using Office 365 for Navision 2016.

Before we start we should have Virtual Machine ready on Azure with Navision 2016 installed, Certificates and Nav User is configured. Endpoints are open so that web client can be used from outside Virtual Machine on internet.

It should be hosted on Public IP, then only we will be able to configure it.

For details regarding Azure Subscription you can see my earlier post here.

For details regarding Creating Virtual Machines you can see my earlier post here.

You also need Office 365 Subscription ready for use and you can use the AD of same to configure your Single Sign-On.

Let us start with Admin console of office 365. Click on Azure AD this will create Management Console to work with AD.

SingleSignOn-1

Enter your details and click on Next.

SingleSignOn-2

Enter your details and click on Next, Finally Sign Up.

SingleSignOn-3

It will take few minutes to configure and make it available for you.

SingleSignOn-4

Once your Subscription is ready click on Start Managing my Service.

SingleSignOn-5

From Management Console click on Virtual Machine, if you dont have already to create one for you.

SingleSignOn-6

Select the Virtual Machine in Search for Microsoft Dynamics Nav 2016.

SingleSignOn-7

Select the approprite Virtual Machine from the List.

SingleSignOn-8

Enter your details and Create. For more details see the link provided on top of the post.

SingleSignOn-9

Let us return to our AD, Click on Applications. This will List all you Apps currently available with your AD or office 365.

SingleSignOn-10

Click on Add to configure your Application.

Give a Name and Select Web Application/or Web API, as we are configuring for Web Client today. Click on Next.

SingleSignOn-11

Enter your Web Client URL and Office portal AD URL.  Click on OK.

SingleSignOn-12

Your New application gets registered with your AD.

Select the created Application and Click on View Endpoints.

SingleSignOn-13

Copy the Federation MetaData URL and keep it handy at safe place we will require this in our next step. Close the window we are not performing any changes here.

SingleSignOn-14

Open your Navision User Card.In Office 365 Authentication enter your Office portal user email id which you will be using for Single Sign-On.

SingleSignOn-15

Open your Navision Service Instance configuration and enter Federal MetaData URL which we copied in our previous step.

SingleSignOn-16

Restart you NAV Service.

SingleSignOn-17

Open the Web Client Folder under your IIS. Edit your Web.config file using prefered Editor.

SingleSignOn-18

Enter you Client Service Credentials as AccessControlService.

SingleSignOn-19

Change value of ACS URI as below. You have full explaination of same in the file itself.

SingleSignOn-20

Thats all, Now you open your web client and you will be redirected to Office 365 Login Page, enter your valid office 365 e-mail user id and password you will be authenticated and Web Client is opened.

Thats all for today, I will come up with moredetails in my upcoming posts.

Till then keep exploring and learning.

 

First update of Project “Madeira” is released – June 2016

What does this update includes?

 

Filter on unprocessed incoming documents

 

The list of incoming documents is now filtered to show only entries that have not been used to create posted documents with.

When documents are posted, the processed flag is set to Yes, such that incoming documents that have been processed into posted documents are filtered out.

You can choose to view all incoming documents in the list if desired, using the show Show All action.

You can also manually switch the processing flag.

 

Office Suite notifications are now enabled in Project “Madeira”

 

In the top right corner, next to the Settings menu, you will notice the bell symbol that you probably already know well from other Office products: That’s the Notifications menu.

Here you will receive notifications such as new mail and calendar reminders directly while working in Project “Madeira”.

 

New extensions, such as Sana Commerce for Project “Madeira” and ChargeLogic Payments that provides payment and credit card processing capabilities.

 

In Project “Madeira”, the Extension Management window lists all available extensions. Some extensions are provided by Microsoft, and other extensions are provided by other companies.

With the first update of Project “Madeira”, extensions have been added by other providers, and Madeira refer to the websites that these companies provide for more information.

Sana Commerce is the integrated B2B e-commerce platform for Project “Madeira” that helps wholesale, distribution and manufacturing companies efficiently run their business, improve their customer service and ultimately generate more revenue through easy online order processing and a 24/7 online access of product information. See the Sana Commerce website.

With ChargeLogic Notify, you can automatically deliver customized, targeted emails directly from Project “Madeira” for invoices, orders, shipments, returns, and sales quotes. Clearly provide the exact information you want with the exact look you want to increase customer satisfaction, promote your brand, and drive business. Using a powerful template system and business rules, you’ll be able to create personalized content and custom designs in an email format and send them to all parties associated with a communication. See the ChargeLogic website. .

 

For further details checkout Link and this Link.

 

 

Assisted Setup (Office Add-Ins) in Madeira

In this post we will discuss about how to setup Office Add-Ins.

Select Assisted Setup & Tasks from the ribbon of Action Tab.

SetupOfficeAddIn-1

Select Setup Office Add-Ins from the list.

SetupOfficeAddIn-2

Click on Next Button on Welcome Screen.

SetupOfficeAddIn-3

Select Setup for (Organization or you only) and click on Next Button.

SetupOfficeAddIn-4

Enter your E-mail id and Password, click on Next to continue with next step.

SetupOfficeAddIn-5

If you wish to receive few sample mails in your Mail box click on the check box or leave it blank, click on Next Button.

SetupOfficeAddIn-6

That’s it. click on Finish to complete the setup.

SetupOfficeAddIn-7

SetupOfficeAddIn-8

That’s all for this post.

Will come with more details in my upcoming posts.

Till then keep exploring & learning.

 

Assisted Setup (Email) in Madeira

In this post we will discuss how to setup Email.

Its very easy and single user intervention required.

SetupEmail-1

From Action Tab click on Assisted Setup & Task from the ribbon.

SetupEmail-2

On Welcome screen click on Next Button.

SetupEmail-3

Select the Email Provider and click on Next to continue.

SetupEmail-4

Enter your Email id and password, click on Next Button.

SetupEmail-5

That’s it, click on Finish.

SetupEmail-6

 

 

SetupEmail-7

 

Just required to enter Email id and password, setup is done.

Will come with other option in my next post.

Till then keep exploring and learning.

 

 

Microsoft Power BI – Part X

Continuing from my previous post. Today we will downloading the dataset into Excel from Power BI Online for analysis.

In case you have missed my previous posts here I present the link to all previous posts below.

 

Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel

 

Login to Power BI using your credentials.

PowerBI-111

Select the Dataset which you wish to analyse, click the three dots on right and from appearing menu choose ANALYZE IN EXCEL.

PowerBI-112

You will be prompted for Analyse in Excel (preview). If you are running first time please install it.

At the same time you will be prompted for (.odc) MS Office Data Connection file to save/open.

Save and then open the File in Excel.

PowerBI-113

On opening the file you will be prompted for security concern Enable to allow it.

PowerBI-114

You will be able to see Pivot Table Fields, containing all of the Tables available in the Dataset.

PowerBI-115

Now you can play with your data to analyse and create Pivot, Charts and share with others or you can Pin back your result to Power BI Dashboards using concept we used in our previous post.

 

That’s all for today, I will come up with more features in my future posts.

Till then keep practicing & Learning.

 

Using Automation to Create a Graph in Microsoft Excel

In this walkthrough, you will transfer data for top 10 Customers Sales Contribution to Microsoft Excel and create a graph.

This example shows how to handle enumerations by creating a graph in Excel that shows the distribution of Sales by Customer.

ExcelChart-1

You will run the codeunit directly from Object Designer. In a real application, you would call it from an appropriate place, such as from a menu or any other window.

About This Walkthrough

This walkthrough illustrates the following tasks:

  • Creating a codeunit that declares the Automation variables that are required for using Excel Automation.
  • Adding a function to calculate Top 10 Customers Sales Contribution.
  • Adding C/AL code to the codeunit to run the Automation object that opens Excel.
  • Adding C/AL code to the Automation codeunit to transfer data from a table record to Excel.
  • Adding C/AL code that creates a graph in Excel. 

Prerequisites 

To complete this walkthrough, you will need:

  • Microsoft Dynamics NAV 2015 with a developer license.
  • The CRONUS International Ltd. demo data company.
  • Microsoft Excel 2013 or Microsoft Excel 2010.

Creating the Codeunit and Declaring Variables

To create the codeunit and declare variables

  • To implement Automation in a codeunit, you define the Automation variables. To define an Automation variable, you specify an Automation server and the Automation object.
  • The language in the regional settings of your computer matches the language version of Microsoft Excel.
  • In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
  • On the View menu, choose C/AL Globals.
  • On the Variables tab, add the following variables:

Note

For the Automation data type variables, the subtype Microsoft Excel 15.0/14.0 Object Library defines the Automation server, and the class specifies the Automation object of the Microsoft Excel 15.0/14.0 Object Library.

ExcelChart-2

Name DataType Subtype Length
xlApp Automation ‘Microsoft Excel 15.0 Object Library’.Application
xlBook Automation ‘Microsoft Excel 15.0 Object Library’.Workbook
xlSheet Automation ‘Microsoft Excel 15.0 Object Library’.Worksheet
xlChart Automation ‘Microsoft Excel 15.0 Object Library’.Chart
xlRange Automation ‘Microsoft Excel 15.0 Object Library’.Range
Cust Record Customer
Window Dialog
CustAmount Record Customer Amount
CustFilter Text
CustDateFilter Text 30
ShowType Option  [Sales (LCY),Balance (LCY)]
NoOfRecordsToPrint Integer
CustSalesLCY Decimal
CustBalanceLCY Decimal
MaxAmount Decimal
BarText Text 50
i Integer
TotalSales Decimal
TotalBalance Decimal
ChartType Option  [Bar chart,Pie chart]
ChartTypeNo Integer
ShowTypeNo Integer
ChartTypeVisible Boolean
Integer Record Integer
Customer Record Customer
CellNo1 Text 5
CellNo2 Text 5
  • Close the C/AL Globals window.

Adding the Code

Now you add the code for the codeunit.

To add the code

  • Add a Function to calculate Top 10 Customers Sales Contribution as:

ExcelChart-3
Add code to it as:

I have not used all the values, just shown this way also you can think of while you design any such code & functions.

Window.OPEN(Text000);

i := 0;

Cust.RESET;

IF Cust.FINDSET THEN

REPEAT

Window.UPDATE(1,Cust.”No.”);

Cust.CALCFIELDS(“Sales (LCY)”,”Balance (LCY)”);

IF (Cust.”Sales (LCY)” <> 0) OR (Cust.”Balance (LCY)” <> 0) THEN

BEGIN

CustAmount.INIT;

CustAmount.”Customer No.” := Cust.”No.”;

IF ShowType = ShowType::”Sales (LCY)” THEN BEGIN

CustAmount.”Amount (LCY)” := -Cust.”Sales (LCY)”;

CustAmount.”Amount 2 (LCY)” := -Cust.”Balance (LCY)”;

END ELSE BEGIN

CustAmount.”Amount (LCY)” := -Cust.”Balance (LCY)”;

CustAmount.”Amount 2 (LCY)” := -Cust.”Sales (LCY)”;

END;

CustAmount.INSERT;

IF (NoOfRecordsToPrint = 0) OR (i < NoOfRecordsToPrint) THEN

i := i + 1

ELSE BEGIN

CustAmount.FIND(‘+’);

CustAmount.DELETE;

END;

TotalSales += Cust.”Sales (LCY)”;

TotalBalance += Cust.”Balance (LCY)”;

ChartTypeNo := ChartType;

ShowTypeNo := ShowType;

END;

UNTIL Cust.NEXT = 0;

CustSalesLCY := Cust.”Sales (LCY)”;

CustBalanceLCY := Cust.”Balance (LCY)”;

Window.CLOSE;

IF CustAmount.FIND(‘-‘) THEN

REPEAT

CustAmount.”Amount (LCY)” := -CustAmount.”Amount (LCY)”;

Customer.GET(CustAmount.”Customer No.”);

Customer.CALCFIELDS(“Sales (LCY)”,”Balance (LCY)”);

IF MaxAmount = 0 THEN

MaxAmount := CustAmount.”Amount (LCY)”;

CustAmount.”Amount (LCY)” := -CustAmount.”Amount (LCY)”;

UNTIL CustAmount.NEXT = 0;

  • In the C/AL Editor, make call to above function by adding the following code to the OnRun trigger.

CustAmount.DELETEALL;

TopTenCustomer(10,ShowType::”Sales (LCY)”,ChartType::”Bar chart”);

  • Create an instance of Excel by adding the following code.

CREATE(xlApp, FALSE, TRUE);

  • Add a new workbook to Excel.

xlBook := xlApp.Workbooks.Add(-4167);

xlSheet:= xlApp.ActiveSheet;

xlSheet.Name := ‘Top 10 Customer’;

The following describes the code:

    • In the first line, you use the Add method of the Workbooks collection to return a new workbook. The attribute -4167 is the enumerator value of worksheets as they apply to Workbook objects.
    • In the second line, you use the ActiveSheet property of the Application class to ensure that what is done next affects the active sheet of the new workbook.
    • In the third line, you use the Name property to name the sheet.

Transferring Data

To transfer the data, you must calculate the data and transfer the results of the calculation.

To transfer data

  • In the C/AL Editor, on the codeunit, use following code to transfer data of Top 10 Customers to Excel. To transfer the data to Microsoft Excel, add the following code.

CellNo1 := ‘A1’;

CellNo2 := ‘B1’;

IF CustAmount.FINDSET THEN

REPEAT

CellNo1 := INCSTR(CellNo1);

xlSheet.Range(CellNo1).Value := CustAmount.”Customer No.”;

CellNo2 := INCSTR(CellNo2);

xlSheet.Range(CellNo2).Value := ABS(CustAmount.”Amount (LCY)”);

UNTIL CustAmount.NEXT = 0;

  • The final step is to create the graph. You will use the ChartWizard method to create chart. This is a fast and simple way to do it. You can more tightly control the design of the graph by setting it up using the methods and properties of the various Chart objects, such as ChartArea and Legend.

Creating the Graph

The final step is to create the graph. You will use the ChartWizard method to create chart. This is a fast and simple way to do it. You can more tightly control the design of the graph by setting it up using the methods and properties of the various Chart objects, such as ChartArea and Legend.

To create the graph

  •   In the C/AL Editor, on the current codeunit, define a range for the data in the graph.

xlRange := xlSheet.Range(‘A2:’+FORMAT(CellNo2));

  • Add a new chart sheet and give it a name.xlChart.Name := ‘ Top 10 Customer – Graph’;

xlChart := xlBook.Charts.Add;

xlChart.Name := ‘ Top 10 Customer – Graph’;

  • Create the graph.

xlChart.ChartWizard(xlRange,-4101,7,2,1,0,1,’Top 10 Customer’);

The following table describes the optional arguments that are used in the ChartWizard method.

Argument Description Value in method call
Source The range that contains the source data for the new chart. xlRange – The object returned by xlSheet.Range(‘A2:C3’).
Gallery The chart type. -4101 – The enumerator for the Chart Shown above.
Format The option number for the built-in auto formats. 7
PlotBy An integer specifying whether the data for each series is in rows or columns. 2 – The enumerator for the xlRows XlRowCol enumerator.
CategoryLabels An integer specifying the number of rows or columns within the source range that contains category labels. 1 – There is one row with category labels (the department names).
SeriesLabels An integer specifying the number of rows or columns within the source range that contains series labels. 0 – There are no series labels in your data.
HasLegend TRUE to include a legend. 1
Title VARIANT with the title of the chart. You pass a string such as ‘Top 10 Customer’.
  • Make Excel visible by adding the following code.

xlApp.Visible := TRUE;

Excel produces a General Protection Fault error when you close a new Excel worksheet that is created when Excel is invisible. To resolve this, you can make Excel visible immediately after you create a new worksheet. You can also make Excel visible just before you create a new Excel worksheet and then make it invisible again immediately after creating the new Excel worksheet. In this case, you would add the following code.

xlApp.Visible := TRUE;

xlBook := xlApp.Workbooks.Open(FileName);

xlApp.Visible := FALSE;

  •  Clearing the Temp Table by adding the following code.

CustAmount.DELETEALL;

  • Complete code in OnRun trigger should look like below:

ExcelChart-4
Saving and Running the Codeunit

You can test the codeunit for creating the graph by running the codeunit from Object Designer.

To save and run a codeunit

  1. On the File menu, choose Save.
  2. In the Save As window, enter an ID and name, and then choose the OK
  3. In Object Designer, select the codeunit, and then choose the Run

The Microsoft Excel graph should appear. As show above in beginning of the post.

Note

If you get an error states Old format or invalid type library, then make sure that the language in the regional settings of your computer matches the language version of Microsoft Excel.

Below detailed reference to the values used in above code:

 

XlChartType

Name Value Description
xl3DArea -4098 3D Area.
xl3DAreaStacked 78 3D Stacked Area.
xl3DAreaStacked100 79 100% Stacked Area.
xl3DBarClustered 60 3D Clustered Bar.
xl3DBarStacked 61 3D Stacked Bar.
xl3DBarStacked100 62 3D 100% Stacked Bar.
xl3DColumn -4100 3D Column.
xl3DColumnClustered 54 3D Clustered Column.
xl3DColumnStacked 55 3D Stacked Column.
xl3DColumnStacked100 56 3D 100% Stacked Column.
xl3DLine -4101 3D Line.
xl3DPie -4102 3D Pie.
xl3DPieExploded 70 Exploded 3D Pie.
xlArea 1 Area
xlAreaStacked 76 Stacked Area.
xlAreaStacked100 77 100% Stacked Area.
xlBarClustered 57 Clustered Bar.
xlBarOfPie 71 Bar of Pie.
xlBarStacked 58 Stacked Bar.
xlBarStacked100 59 100% Stacked Bar.
xlBubble 15 Bubble.
xlBubble3DEffect 87 Bubble with 3D effects.
xlColumnClustered 51 Clustered Column.
xlColumnStacked 52 Stacked Column.
xlColumnStacked100 53 100% Stacked Column.
xlConeBarClustered 102 Clustered Cone Bar.
xlConeBarStacked 103 Stacked Cone Bar.
xlConeBarStacked100 104 100% Stacked Cone Bar.
xlConeCol 105 3D Cone Column.
xlConeColClustered 99 Clustered Cone Column.
xlConeColStacked 100 Stacked Cone Column.
xlConeColStacked100 101 100% Stacked Cone Column.
xlCylinderBarClustered 95 Clustered Cylinder Bar.
xlCylinderBarStacked 96 Stacked Cylinder Bar.
xlCylinderBarStacked100 97 100% Stacked Cylinder Bar.
xlCylinderCol 98 3D Cylinder Column.
xlCylinderColClustered 92 Clustered Cone Column.
xlCylinderColStacked 93 Stacked Cone Column.
xlCylinderColStacked100 94 100% Stacked Cylinder Column.
xlDoughnut -4120 Doughnut.
xlDoughnutExploded 80 Exploded Doughnut.
xlLine 4 Line.
xlLineMarkers 65 Line with Markers.
xlLineMarkersStacked 66 Stacked Line with Markers.
xlLineMarkersStacked100 67 100% Stacked Line with Markers.
xlLineStacked 63 Stacked Line.
xlLineStacked100 64 100% Stacked Line.
xlPie 5 Pie.
xlPieExploded 69 Exploded Pie.
xlPieOfPie 68 Pie of Pie.
xlPyramidBarClustered 109 Clustered Pyramid Bar.
xlPyramidBarStacked 110 Stacked Pyramid Bar.
xlPyramidBarStacked100 111 100% Stacked Pyramid Bar.
xlPyramidCol 112 3D Pyramid Column.
xlPyramidColClustered 106 Clustered Pyramid Column.
xlPyramidColStacked 107 Stacked Pyramid Column.
xlPyramidColStacked100 108 100% Stacked Pyramid Column.
xlRadar -4151 Radar.
xlRadarFilled 82 Filled Radar.
xlRadarMarkers 81 Radar with Data Markers.
xlStockHLC 88 High-Low-Close.
xlStockOHLC 89 Open-High-Low-Close.
xlStockVHLC 90 Volume-High-Low-Close.
xlStockVOHLC 91 Volume-Open-High-Low-Close.
xlSurface 83 3D Surface.
xlSurfaceTopView 85 Surface (Top View).
xlSurfaceTopViewWireframe 86 Surface (Top View wireframe).
xlSurfaceWireframe 84 3D Surface (wireframe).
xlXYScatter -4169 Scatter.
xlXYScatterLines 74 Scatter with Lines.
xlXYScatterLinesNoMarkers 75 Scatter with Lines and No Data Markers.
xlXYScatterSmooth 72 Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers 73 Scatter with Smoothed Lines and No Data Markers.

expression .ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)

expression A variable that represents a Chart object.

Parameters

Name Required/Optional Data Type Description
Source Optional Variant The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.
Gallery Optional Variant One of the constants of XlChartType specifying the chart type.
Format Optional Variant The option number for the built-in autoformats. Can be a number from 1 through 10, depending on the gallery type. If this argument is omitted, Microsoft Excel chooses a default value based on the gallery type and data source.
PlotBy Optional Variant Specifies whether the data for each series is in rows or columns. Can be one of the following XlRowCol constants: xlRows or xlColumns. Values can be [1 or 2]
CategoryLabels Optional Variant An integer specifying the number of rows or columns within the source range that contain category labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
SeriesLabels Optional Variant An integer specifying the number of rows or columns within the source range that contain series labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
HasLegend Optional Variant True to include a legend.
Title Optional Variant The chart title text.
CategoryTitle Optional Variant The category axis title text.
ValueTitle Optional Variant The value axis title text.
ExtraTitle Optional Variant The series axis title for 3-D charts or the second value axis title for 2-D charts.

Remarks

If Source is omitted and either the selection isn’t an embedded chart on the active worksheet or the active sheet isn’t an existing chart, this method fails and an error occurs.

You can use other values from above table to create graph of your choice.

Using Automation to Write a Letter in Microsoft Office Word

Automation lets you use the capabilities and features of Microsoft Office products, such as Microsoft Word or Microsoft Excel, in your Microsoft Dynamics NAV application.

Today we will implement Word Automation from a customer card in the Microsoft Dynamics NAV Windows client.

Note: The Microsoft Dynamics NAV Web client does not support automation.

Most information that we need to transfer to Word for this example is in the Customer table. The Customer table contains a FlowField called Sales (LCY) that contains the aggregated sales for the customer.

In this example we are learning about Automation, so we will use the existing value. In a real customer installation, we would need to set up an appropriate date filter to get the sales for the past year only.

We also need to retrieve the information about our own company that we will use in the letterhead and in the greeting of the letter. This information is contained in the Company Information and User tables.

  • The Automation server must be installed on the computer that compiles an object that uses Automation. If you must recompile and modify an object on a computer that does not have the Automation server installed, then you must modify the code to compile it again. We recommend that you isolate code that uses Automation in separate codeunits.
  • Performance can be an issue if extra work is needed to create an Automation server with the CREATE system call. If the Automation server is to be used repeatedly, then you will gain better performance by designing your code so that the server is created only once instead of making multiple CREATE and CLEAR calls).

Performance can be improved by putting the code on the customer card because you do not have to open and close Word for each letter that is created in the session.

You can work around this problem. If Word is already open when it is called from the code, then the running instance is reused. You can manually open Word or do not close Word after creating the first letter.

We will extract and transfer data one customer at a time. We will also initiate this processing and the subsequent processing in Word from the customer card.

We will insert fields into the Word template and give these fields convenient mnemonic names that correspond to the names of the record fields that we are using.

To make this work, C/AL code must make two extra calls to Microsoft Office Word. You must call the ActiveDocument.Fields.Update method before using the fields. After you have transferred all the information, you must call the ActiveDocument.Fields.Unlink method. This ensures that you can successfully use the Word fields as placeholders.

In addition, while you can name the Customer or Address fields, you must reference them by indexing into the Fields collection of the document. This can make the C/AL code harder to understand.

Creating the Word Template for Use by Automation

First, task is to create a Word template that we will use to create letters to customers that qualify for a discount. To create the template, we will add mail merge fields for displaying data that is extracted from Microsoft Dynamics NAV that you want included in the customer letter, such as the customer’s name, contact, and total sales.

You will create and save the template on the computer running the Microsoft Dynamics NAV Windows client, because you will configure the automation object to run on the client.

  • On the computer running Microsoft Dynamics NAV Windows client, open Word and create a new document.

WordAutomation-1

  • Choose where you want to insert the fields. Then, on the Insert tab, in the Text group, choose Quick Parts, and then choose Field.

WordAutomation-2

  • In the Categories list, select Mail Merge.
  • In the Field names list, select MergeField.
  • In the Field Name box under Field Properties, type Contact. This field will display the name of your contact person at the customer site as taken from the Customer table.
  • Choose OK to add the field.

WordAutomation-3

  • Repeat steps as above to add the remaining fields as follows:
Field name Description Underlying table
Name The name of the customer. Customer
Address The address of the customer. Customer
Sales (LCY) The total amount that the customer has purchased from you. Customer
Company Name The name of your company. Company Information
  • Save the Word document as a template with the name Discount.dotx in folder of your choice.

WordAutomation-4

Creating the Codeunit and Declaring the Variables

The next step is to create the codeunit that calls Word and creates the letter.

To create the codeunit

  • In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
  • On the View menu, choose Properties to open the Properties window of the codeunit.
  • In the TableNo field, choose the AssistEdit button to open the Table List window.
  • In the Table List window, select the Customer table, and then choose OK.

WordAutomation-5

  • Close the Properties window.

To declare the variables

  • Choose the OnRun Trigger and on the View menu, choose C/AL Locals, and then choose the Variables tab.
  • On a blank line, type wdApp in the Name field and set the Data Type field to Automation.

Note

When you create an Automation variable, some hidden events are also created for it. If you want to delete the variable, be aware that the events are also not deleted. This can cause issues if you then create a variable with the same name.

  • In the Subtype field, choose the AssistEdit button. The Automation Object List window is displayed.
  • In the Automation Server field, choose the AssistEdit button.
  • In the Automation Server List, select Microsoft Word 15.0 Object Library if you are running Word 2013, or select Microsoft Word 14.0 Object Library if you are running Word 2010, and then choose OK.
  • From the list of classes in the Automation Object List, select the Application class, and then choose OK.

WordAutomation-6

  • Repeat steps above to add the following two Automation variables:
Name Data type Subtype Class
wdDoc Automation Microsoft Word 14.0/15.0 Object Library Document
wdRange Automation Microsoft Word 14.0/15.0 Object Library Range
  • Add the following variables.
Name Data type Subtype Length
CompanyInfo Record Company Information
TemplateName Text 250
  • Close the C/AL Locals window.

Writing the C/AL Code

Before you start writing the C/AL code that uses Automation, you must do some initial processing. You start by calculating the Sales (LCY) FlowField. Then, you check whether the customer qualifies for a discount. Finally, you retrieve the information from the Company Information and User tables that you use to fill in some of the fields in the letter.

To write the C/AL code

  • In the C/AL Editor, add the following lines of code to the OnRun section.
  CALCFIELDS(“Sales (LCY)”);CompanyInfo.GET;
  • To create an instance of Word before using it, enter the following line of code.
 CREATE(wdApp, FALSE, TRUE);
  • This statement creates the Automation object with the wdApp variable.
    1. The first Boolean parameter in the statement (FALSE) tells the CREATE function to try to reuse an already running instance of the Automation server that is referenced by Automation before creating a new instance. If you change this to TRUE, then the CREATE function always creates a new instance of the Automation server.
    2. The second Boolean parameter in the statement creates the Automation object on the client. This is necessary to use this codeunit on a page in the Microsoft Dynamics NAV Windows client.
  • Enter the following lines of code to add a new document to Word that uses the template that you designed earlier. If required, replace C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter with the correct folder path to the template that you defined in the procedure.
 TemplateName := C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter\Discount.dotx’;wdDoc := wdApp.Documents.Add(TemplateName);wdApp.ActiveDocument.Fields.Update;
  • Because the Add method of the Documents collection requires that you pass the path to the template by reference, you must set up the TemplateName variable to hold this information. You will get a compilation error if you put the path into the call as a literal string.
  • The Documents property returns a Documents collection that represents all open documents. You can also see that the Documents collection object has an Add method, and that the Add method has the following syntax.
  • expression.Add(Template, NewTemplate, Document Type, Visible)
  • expression is a required argument, and it must be an expression that returns a Documents object. All the arguments are optional. You will use Template to open a new document that is based on your template.
  • For the syntax in the C/AL Symbol Menu, note that the Documents property returns an object of type DOCUMENTS, which is a user-defined type. The property returns a Documents class or IDispatch interface. This information helps the compiler perform a better type check during compilation. The following statement can also pass both the compile-time and the run-time type checks.
  • wdDoc := wdApp.Documents.Add(TemplateName);
  • Finally, the Add method returns a Document class. While you did not need to declare a C/AL variable for the interim Documents class, you have declared a variable for the wdDoc return value,.
  • The third line contains a call that must be made to ensure that the template works as intended.
  • wdApp.ActiveDocument.Fields.Update;

Transferring Data to Word

Now you can transfer the actual data from the Customer record to the placeholder fields in the Word document.

You have set up the first three fields in the template so that they can contain the contact, name, and address of the customer and you can transfer the data.

To transfer data to Word

  • Transfer the data by adding the following lines of code.
 wdRange := wdAPP.ActiveDocument.Fields.Item(1).Result; wdRange.Text := Contact; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(2).Result; wdRange.Text := Name; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(3).Result; wdRange.Text := Address; wdRange.Bold := 1;
  • You cannot use the fields directly as variables and make an assignment such as Fields.Item(3) := Address. Instead, you use the Result property of the field. This property returns the result of the field as a range. You place this range in the wdRange Automation variable that you declared.
  • You then set the Text property of the range to the desired values, which is the name of your contact person and the name and address of the customer. Finally, you add bold formatting.
  • The data you are transferring must be in text format. If it is not in text format, then you get a compilation error. wdRange.Text expects arguments to be of type BSTR, which maps to either Text or Code. This means that any data that is not Text or Code must be converted before it is passed to Word. To convert a field to Text, you use the FORMAT function. All the fields that are transferred in this step are in text format, so no conversion is needed and the FORMAT function is not used. However, in this example, you also need to transfer the Sales (LCY) field, which is a Decimal field. To see how to convert the Sales (LCY) field, go to the next step.
  • To transfer and format the data from the Sales (LCY) field, add the following code.
 wdRange := wdAPP.ActiveDocument.Fields.Item(4).Result;wdRange.Text := FORMAT(“Sales (LCY)”);wdRange.Bold := 0;
  • To transfer the information from the Company Information table, add the following code.
 wdRange := wdApp.ActiveDocument.Fields.Item(5).Result;wdRange.Text := CompanyInfo.Name;
  • To complete the processing in Word, add the following code.
 wdApp.Visible := TRUE;wdApp.ActiveDocument.Fields.Unlink;
  • The first statement opens Word and shows you the letter that was created. The second statement makes the fields work as placeholders.

WordAutomation-7

  • Save and compile the codeunit

To-Do List

Although this code will work, you must add a few things to make it complete:

  • We recommend that you do not use a hardcoded template name. You should keep the template name in a table, and the user should select it from a page. You can then have different templates for different types of letters that you want to send to your customers.
  • You should add some error-handling code. For example, the CREATE call fails if the user does not have Word installed or if the installation has been corrupted. You should check the return value of CREATE and give an appropriate message if it fails.
  • The user should get a message if the customer does not qualify for the discount. In the example, the codeunit closes without any message.

Calling the Codeunit from the Customer Card

The final task is to ensure that you can call the codeunit from the Customer Card page in the Microsoft Dynamics NAV Windows client.

To call the codeunit from the Customer card page in the Microsoft Dynamics NAV Windows client

  • Open Object Designer, and then choose Page.
  • Select the Customer Card page and then choose Design.
  • On the View menu, choose Page Actions.
  • To add a new action, locate the action container with the subtype set to ActionItems.
  • Right-click the next line after the ActionItems container, and then choose New.
  • In the Caption field of the new line, type Word Letter.
  • Set the Type field to Action.
  • With the new action selected, on the View menu, choose Properties.
  • In the RunObject field, type codeunit Discount Letter.

Note

If you saved the codeunit that you created in the previous procedure under a different name, then substitute Discount Letter with the name that you used.

  • Use the arrow buttons to make sure that the new action is indented only once from the ActionItems container above it

WordAutomation-8

  • Save and compile the Customer Card page.

To run the Customer Card and view the Word letter

  1. In Object Designer, choose the Page
  2. Select the Customer Card page, and then choose Run.
  3. In the ribbon, on the Actions tab, choose the Word Letter

The letter document opens in Word.

WordAutomation-9

Next Steps

The letter that you have just created only contains five fields and sample body text. Before you can use this letter in an actual situation, you will need to add some more fields, such as the name and address of your own company, the date, and the currency code, and the main text of the letter. It will also need some formatting to make it look more attractive. If you alter the order in which the fields appear in the template, you must change the numbering of the fields in the codeunit to ensure that the correct data is inserted into the appropriate fields.

 

OFFICE 365 INTEGRATION IN NAV 2015 – Word/Excel/SharePoint

If you have signed up for Office 365, Microsoft Dynamics NAV can use this service to open and save documents. For example, when you export a report to Excel and save it on Office 365, Microsoft Dynamics NAV can use Excel Online that your Office 365 account uses to open the document.

Before you can use Office 365 and SharePoint Online with Microsoft Dynamics NAV, you must configure the service in Microsoft Dynamics NAV.

If you want to use the same folder to store temporary files for all users, you must specify a shared user account. Otherwise, each user’s temporary files are stored in their personal document repository on the SharePoint site.

Let us first understand the details before we start with Setup for SharePoint Shared Folder.

Your IT can help you configuring and assigning suitable rights to access. Although you can explore and help yourself. Since we are concentrating on Navision so we will not go in details for Share point.

Just to make understand I will be only covering top level information.

In this example I assume you already have up and running site for Document Sharing in SharePoint 2013 – 365 Online.

Here I am logging in to Office 365 – Online registered by me for demo purpose. You can register for free trial to explore the same in more details.

SharePoint-1

My Opening screen looks as one below.

SharePoint-2

I select SharePoint Site to continue. Below is my SharePoint Opening screen. I will select my site which I have created for this demo and file sharing with other users in my organization.

SharePoint-3

Let us understand different parts which will help configuring our Online Document Storage Configuration in Navision 2015.

SharePoint-4

Here I am in my Folder at Lower level where all my files will be stored and shared with my other colleges.

SharePoint-5

Open “Online Document Storage Configuration” from path shown in below screen.

SharePoint-6


Specify the Service Name of your choice or use Default provided by the System.


Fill the Location to your SharePoint site: Specifies the unified resource indicator (URI) for your site on SharePoint Online, such as [https://krishnasoftwaredevelopers.sharepoint.com/MS_Nav_Documents] in my case I will use this.


Fill the Folder field which specifies the folder in the document repository for this document service that you want documents to be stored in.


This field is required. If you have not already created a subfolder in your document repository, you must create one now, and then specify it in the Folder field.


The Document Repository field includes the top-level folder in the document repository that your document service provides. The Folder field specifies a subfolder so that you can keep business documents and drafts separate from pictures and other shared documents.


For example, if the Document Repository field is set to [Documents], the Folder field can be set to [NavisionDoc] as per my example discussed above.


When a user exports data to Excel, for example, Microsoft Dynamics NAV saves the temporary file at the location that is specified in the Document Repository and Folder fields, such as

[https://krishnasoftwaredevelopers.sharepoint.com/MS_Nav_Documents/Documents/NavisionDoc]


Fill Document Repository field: Specifies the name of the document library, such as [Document]


Fill User Name field: Specifies the account that Microsoft Dynamics NAV Server must use to log on to the document service.


I would recommend that you specify a different user name than the administrative account. This helps reduce the risk of users accessing administrative settings, for example. For example, if your user name as an administrator is Administrator@KrishnaSoftwareDevelopers.onmicrosoft.com,

then the user name for Microsoft Dynamics NAV Server can be Nav@KrishnaSoftwareDevelopers.onmicrosoft.com or any of the regular user names that you have created.

SharePoint-7


On the Actions tab, in the General group, choose Set Password.


In the window that appears, specify and confirm the password for the account    that you specified in the User Name field. Choose the OK button.


When you have set up the service, you can test whether that configuration is valid.

SharePoint-8


On the Actions tab, in the General group, choose Test Connection.


A message displays the result of the test.

SharePoint-9


The above message confirms we can now save documents on our SharePoint.


Let us check a sample as below:
Open the Document and Execute the report available.
While Running the Report I specify I want to Print with Microsoft Word.

SharePoint-10


Document will be generated and opened in Word – Office – 365. Temp file name is given to the file. Close the File as it is auto Saved by Office 365 Word Application.

SharePoint-11


Browse to your SharePoint Folder configured above, you will see the Word file is now available in this Folder.

SharePoint-12


Right Click on Name of the File and Rename with some meaning full Name as shown below.

SharePoint-13


Give the Name to the File and press Save Button.

SharePoint-14


Here is the File after Rename.

SharePoint-15


This way when ever you Print your document from Navision in Word or Excel it will get saved in this Folder.

By setting appropriate access rights you can share the files between you colleges in your Organization.

 Please check SharePoint stuffs with you admin or appropriate authority in your organization.

SMTP Setup errors while setting Office 365

Normally we see below Errors while configuring our SMTP in Navision.

Error-1

Check that your User Setup is having valid e-mail id.

Error-2

If you are using Domain make sure you are accessible to your domain. If you are outside you office make sure you are connected using your VPN.

Error-3

Previous Older Entries

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: