Skip to content

The Beloved Report

"Oh Excel, how I love thee, let me count the ways! A1, A2, A3, A4…" 

Of all the development tasks I’m asked to do the most common has got to be to add Excel output to a NAV report.  Most often, the request is specifically for the Detail Trial Balance or Inventory Valuation report. 

Computer screen covered in flowersYears ago when I first started working with NAV I decided the Excel Buffer Table method was just not my cup of tea.  Now you may use it and find that it works just fine to accommodate all your needs.  I, however, like to flavor my spreadsheets with color and multiple fonts and even turning some values red when they should draw attention. 

I’ve also found that the temporary table size restriction of 2GB can prevent some reports from being created using the Excel Buffer Table.  Likewise, the SAVEASEXCEL function has a 64K limit, so you can’t use it for larger reports either.  And, if you’re running on the RTC client, know that the automation is invoked on each row, which tends to slow down performance on larger reports as well.

But it was solely from the desire to make the Excel reports “fancier” that I developed a codeunit full of Excel commands.  You will recall from a blog article a few weeks ago that we talked about reusing code, and placing all our reusable code into a single codeunit, which makes all the functions you need easy to find and readily accessible.  This is a prime example of that, and the most used of those codeunits that I have.  It’s probably been placed in somewhere around 50 different databases so far.  Today, it’s yours for free!  Enjoy!  And I’m going to show you how to use it.

But there’s no need to fill up the tank if you don’t have the keys!  In order to use the codeunit you will have to have Microsoft Excel loaded – and not just the reader, but the full version.  You can use Excel from the 2010 version back to the 1995 version, and possibly older version.  The codeunit uses Excel as an Automation, which is a data type in NAV that is used to incorporate external software into NAV functions using COM technology.  In the classic client environment, Excel would reside on the client machine.  But when using the Role Tailored Client, whether Excel resides on your client or your NAV server is dependent on several factors.  Click here to read about those.


You will find the functions within the codeunit do a number of different things using Excel classes, but you may decide you want to add a few more.  (If you do – please share them back using the comment box below). The easiest way to find the commands to use is by recording a macro in Excel, and reviewing the VBA command, which is not exactly like NAV uses it, but very close. 

In Excel 2010, the commands to create a macro are: View-Macro-Record Macro-(name it)-and click OK. To stop the recording, just click Macro-Stop Recording.  To see the commands used by Excel, use Macro-View Macro-Edit.  In my simple test for bolding the contents of a single cell, I turned on the macro record function, entered “ABC” into a cell, and then bolded the cell.  I then stopped the macro recording, and edited the macro.  The results look something like this:

Sub Macro1()
Macro1 Macro
    ActiveCell.FormulaR1C1 = "ABC"
    Selection.Font.Bold = True
End Sub

But in NAV, using the Excel automation, the command to bold a single cell looks like this:

xlSheet[Sheet1].Range(A1).Font.Bold := 1;

Okay, stay with me now!  It’s really not all that complicated.  We first have to identify the sheet in which we are bolding the cell.  (In the macro you don’t see this because the macro is already on the sheet.  It doesn’t record what you’ve already done prior to setting it to record.) Then we have to identify the Range, which is ‘A1’.  Then we just execute the “Font.Bold” class of Excel by assigning it a value of 1.

If you don’t “get it” right from the beginning, don’t sweat it.  Nearly any example you need is found in the codeunit already.  Just dig through it and you’ll either find what you’re looking for - or find something close enough to lead you to what you need. 

If you’re ambitious and you want to look at the various classes and parameters for Excel, while editing the macro click F2 (Object Browser), and set your library to EXCEL.  You’ll see all the classes of Excel, and be able to drill down to the different settings for each one.  When you see what you’re looking for, right click on the class, and select HELP, which almost always gives you the full command.

Below you’ll see what this looks like when I search for the syntax for setting the font color for a range:

Image of NAV screen



The beauty of the codeunit is that each function can simply be passed the values it needs (such as sheet number, range, font size, color name, etc…) and it will just handle the commands for you.  So if you learn how to call the commands using these variables, you really don’t have to learn the excel commands at all!  About the only way you can mess it up is by doing things out of order.  For example, you can’t bold a field before you create the sheet.

Let’s go through a quick example, and I’m going to show you just how easy it is to add Excel output to the Inventory Valuation report. (It’s really just cut, paste, modify a bit, and go!)  I’ll also give you some tips on designing Excel output from NAV.  And I will give you an iron clad, money-back guarantee that you can do this faster with the codeunit than with the Excel Buffer table! (It’s free, remember?)

I will be using a standard, NAV 2009 R2 version, of the Inventory Valuation report 10139.  If you’re not yet on NAV 2009 R2, don’t worry! The report layout has changed very little through past versions.  The code inserted should be the same for versions even 10 years old, as long as you haven’t customized your report.

First, download the codeunit below in text format. 

Excel Function Codeunit

(Right click and “SAVE” to download)

Since it’s in text format, you can open it and change the object number to something good for your database and license prior to importing it.  Then simply import it to your development database (remember – we never do development in our production database), and compile it.  As long as you have Excel loaded, the compile shouldn’t give you problems at all.

Now, let’s get started adding Excel output to our Inventory Valuation report!  Watch the video below and I’ll take you through it step by step and show you how it’s done.  Then try it!  You’ll be surprised just how easy it is to add an Excel report to any NAV report. 

But don’t stop there! Grow the codeunit by adding new functions! You might want to add a sort function where you pass it a few columns for sort priority, or maybe a pivot table function.  If you add the functions to it to perform the things your users tend to do most, and then do it for them in NAV, imagine how much your users will love you!  Why, you’ll feel as if you’re wearing Super Man’s cape!


Blog Tags: 



Thanks for the CodeUnit!
I just tried it today and it seams to work very well.
I added one function thou. I use Excel in danish wich means the default sheet that's made when the workbook is created is not called "Sheet1" but "Ark1".
I could just switch the text when calling ExcelDeleteSheetName(SheetName : Text[30]), but its imposible to know if the user uses the danish, english or any other version of Excel.
So I decited to delete it based on its Index:
xlSheet1 := xlSheet[2];


Great catch Kasper! You may run into that in several places in the codeunit. I'd suggest you do a quick scan of the "Sheet1" sheet name just to be sure you've got them all. 

Enjoy the codeunit! I'm so happy you've used it!



Great work.  Always good fun to put color to world, etc.
A thing Navision always have been holding back on.

Just a comment to Kasper Tran about Danish Sheet names.
I am Danish too, and have been working all over so I have run into the problems with "Sheet names" as well.
I will suggest you look in table 370  (Excel Buffer)  at function "SelectSheetName".

You can see that it reads the sheets into a text var  (Sheetlist) so the user can select which sheet to work with.
Copy the first part into your new Code Unit here - but add the sheets to a TEMP table instead.
Now you can roll through this temp table and delete the sheets you want  -  and no longer having a problem with the language the user have installed.

Have nothing short of a great day.



Soren, if you like the use of colors, you're going to love the codeunit.  You can add so many "flavors" by changing colors, fonts, font sizes, adding italics, etc... - you'll think you've found the Baskin Robbins of spreadsheets!

Sheet1 has long been my enemy.  Regardless of what you do, it's there.  You can't get rid of it till you've added a second sheet.  I've tried referencing it and writing to it, but that becomes a problem when you want to allow the user to add more than one sheet (first one is named, the rest aren't...that's ugly).  I'm not sure how the TEMP table solution works, but I'll be looking into it. If you happen to add the code to your codeunit, please drop us a line and share!


Hi Faithie;

Sure thing.  Will have a look tonight  -  and send it as a seperate CU, so you can do the merge as you please.


Dearest Soren! Please forgive me for being so late in answering!  I have been on vacation and worked all week to try to catch up.

I did receive your code and reviewed it.  Saving the sheets to a temp table, and then accessing by name to determine the sheet number in the workbook is a great idea.  And this could easily be added to the codeunit for sheet selection. KUDOS!

I was given another request just a few days ago to determine the code for the "Freeze Panes" function in Excel.  To share that with all the readers, here is the function to add to the Excel Codeunit (In TEXT format of course):

PROCEDURE ExcelFreezePanes (SheetNumber: Integer;CellRange: Text[30]);
      xlSheet[SheetNumber] := xlApp.ActiveSheet;
      xlApp.ActiveWindow.FreezePanes := TRUE;


I would like a updated codeunit, if you have one.
I have saved the link you have in your article, but is hasn't the correction mentioned above.
It is a fob file and not a text file, by the way :-)
Best Regards,


Hello Kenneth,

Thanks for bringing it to my attention that the link did not download a .txt version of the codeunit.  That has been corrected, and this version does include the freeze pane function.  I have not, however, incorporated the temp file for holding sheet names.  Feel free to work that into your version and make it your own :)

Thanks for commenting! It's good to know there really are readers out there!

- Faithie


Hi again
Could you send me the codeunit with the temp file, so that I don't have to program it myself.
- Kenneth



Unfortunately, I don't have it merged into the codeunit at this time.  Eventually, when I do, I'll post the new one in a seperate blog issue.  Just be sure you are subscribed so you don't miss it. 

However, since Soren Smith was so kind as to send the code to me, I'm going to insert the code for the function here.  You can add it to the codeunit as a callable function.


PROCEDURE RetrieveExcelSheetNames@2(FileName@1000 : Text[250];VAR TmpSheetNames@1102631011 : TEMPORARY Record 180);
      XlApp@1102631007 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00024500-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Application";
      XlWrkBk@1102631006 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00020819-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Workbook";
      XlWrkSht@1102631005 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00020820-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Worksheet";
      XlWrkshts@1102631004 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{000208D8-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'._Worksheet";
      XlRange@1102631003 : Automation "{00020813-0000-0000-C000-000000000046} 1.7:{00020846-0000-0000-C000-000000000046}:'Microsoft Excel 14.0 Object Library'.Range";
      i@1002 : Integer;
      EndOfLoop@1003 : Integer;
      Found@1004 : Boolean;
      ErrMissingFileName@1102631008 : TextConst 'ENU=You most specify name of Excel file to retrieve Sheetnames from!';
      ErrFileDontExist@1102631009 : TextConst 'ENU=Excel file %1 does not exist!';
      ErrExcelMissing@1102631010 : TextConst 'ENU=Can not create Excel Application!';
      IF FileName = '' THEN

         IF NOT EXISTS(FileName) THEN


      XlWrkBk := XlApp.ActiveWorkbook;

      i := 1;
      EndOfLoop := XlWrkBk.Worksheets.Count;

      WHILE (i <= EndOfLoop) DO BEGIN
         XlWrkshts := XlWrkBk.Worksheets.Item(i);

         TmpSheetNames."Entry No." := i;
         TmpSheetNames.Line        := XlWrkshts.Name;

         i += 1;



Thanks again to Soren for his work on this!

- Faithie


Hi Faithie
Could you in the link (textfile) also include your report "Inventory Valuation", that would be very nice.
Keep up the good work :-)
- Kenneth


Hi Faithie,
I work as a Nav specialist for an end user. I don't know when I'll be able to use your codeunit but anyway the occasion was too juicy and I couldn't resist to download it!
Thanks a lot for the cu and the nice explanation about getting vb excel code.



You are ever so welcome!  I always enjoy the challenge of finding the code in VB form and converting it to it's NAV counterpart.  You'll find this challenge much easier by referencing the code in the codeunit as well.  At this point, the codeunit has grown to house nearly all classes and excel actions.  I can usually find something very closely related to what I would try to add.


- Faithie


This list is great to avoid the object browser and the subsequent translation into C/AL.  One thing I have struggled with but is not on the list - 3 column sorting.  For some reason, the syntax for sorting in Excel automation as per the object brower does not translate easily into C/AL code.  I have managed a 1 and 2 column sort but my code does not follow the expected logic pattern and bombs on 3 columns.  Do you have any suggestions?


Bsearle, thanks for commenting.  I tend to leave all the sorting to the user, and not do it from NAV.  That way the data is written out in the same sequence as the NAV report, making it much easier to compare the output to it's NAV equivalent.  But if you wanted to try it, I'd suggest starting with recording a macro in Excel that performs the sort, and then review the Macro code created.

I've done a simple sort macro below of 3 columns/3 rows (Name, Address, City).  The Macro looks like this: 

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A4:A6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C4:C6"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A3:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
End Sub

As you can see from the Excel macro, this is no easy thing! At least, not as easy as sorting it in NAV before exporting it to Excel.  As an alternative, you might want to write the data to a temp table, and then on an Integer variable, after sorting the temp table, export it to Excel.

But if you want to try it, the macro will get you started.  The first statement clears the sort prior settings.  The next two statements define the columns used to sort the data.  The last defines the range to be sorted, sort orientation and options, and performs the sort.

Some VB variables are used in NAV as integers.  You can tell this when using an Excel Macro by highlighting the variable (such as "xlTopToBottom" or "xlPinYin") and clicking CTRL+I.  These two variables are set to integer '1'.  I didn't check the rest, but if you try this, do check them from the macro, and then make the assignments to the variables in NAV.  There are others in the codeunit that do this as examples.

If you get this to work, please come back to us and share your success! smiley


Thanks for the reply.  I have extensive experience with Excel automation but have only recently started using NAV where C/AL appears to be derived from Delphi and automation is a bit trickier than in other Microsoft counterparts.  I understand the concepts with Excel macros and the object browser and understand the variable assignments as well.  Since the NAV data is coming from multiple tables, I cannot set the sort at the table level and wish to avoid a workaround such as a temporary table.  If I figure this out, I will post the results.


I apologize for the long response but here goes...
The following is the methodology I used to determine the sorting of 3 columns in NAV 5 (proprietary tables) and Excel 2003 using Excel Automation.

To begin, I ran a macro to determine what is needed in C/AL code and edited the macro to see the Excel code.
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
    , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _

Using the Object Browser I determined some of the Excel variable choices:
Order (xlAscending is 1, xlDescending is 2)
Header (xlGuess is 0, xlYes is 1, xlNo is 2)
MatchCase is boolean (0 is false, 1 is true)
Orientation (xlTopToBottom is 1, xlLeftToRight is 2)
DataOption (xlSortNormal is 0, xlSortTextAsNumbers  is 1)

For easier reading the macro appears as follows…
Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2"), Order2:=xlAscending,
Key3:=Range("C2"), Order3:=xlAscending,

Based on the macro, this is the C/AL code I expected was needed.
The variable ‘Row’ is the last row used in the population of the worksheet data
xlSelectedRange := xlWorksheet.Range(xlCell(1,2)+':'+xlCell(8,Row));


But the above code does not work.
What is missing is a “Type” field immediately following the second key.  The macro does not show the necessity of the Type field but below you can see where it is included.  I have no explanation as to what purpose this serves, why it is needed and why it must follow the second key.

Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2"), Type, Order2:=xlAscending,
Key3:=Range("C2"), Order3:=xlAscending,

The modified version now includes the ‘Type’.  C/AL code does not accept a 1, 0, space, ‘’, or many variations for this Type field.  It does however accept another Range (not sure why) as the type so I repeated the original range.

xlSelectedRange := xlWorksheet.Range(xlCell(1,2)+':'+xlCell(8,Row));


This works.  It sorts my data in cells A1 to Hx by columns A,B and C with a header row.
Hopefully this may save others some grief when sorting more than 2 columns.


Bsearle, that's awesome! Good job my friend! I'm sending you a big box of virtual kudos! smiley

As to what the "TYPE" attribute is...I was scratching my head.  I did do some research and according to what I read, it appears to be something only used when sorting PivotTables (see this link).  However, we know you were sorting a range and not a PivotTable, so maybe NAV requires something to be there just to process the full command.  (That's good to know in case we want to use it for a PivotTable one day!)

Now, if we can just figure out how to put this code into a callable function that would accept the limited number of variables (Range to Sort, Key 1, Key 2, Key 3, etc...) in order to produce the sort, we can add it to the Excel Codeunit. 

Great job my friend! cool And thanks SO MUCH for sharing the code with us.  NAV is so vast that there's no way one developer can ever learn all there is about NAV.  It's only through the community working together that we can achieve excellence in what we do.

- Faithie


What I do is slightly different.
We sell to retailers and also online and I am taking care of all of the ecoms.
The problem with our inventory is that it is pretty messy with several SKUs for same items due to different customers and so on. And ecoms require daily inventory updates. With 5000 skus that are either alive or dead and 15 different ecoms to do that would take at least a week (for one daily update). So I googles some macros and what I did was this. I can generate a "stock status report" from NAV and save it as HTML. I generate it daily and save it on the desktop to make it easy. Then in excel I make a macro to import the HTML file into excel. I also included an auto update on every time you open the excel file. This way I generate the report daily, open the excel file and the inventory automatically updates in the excel file. Now I have all of the info in excel and its easy to manipulate. First I convert the data to text on a new sheet. I think it was "=TEXT(A1,0)" (in case you have "0"s at the beginning of some of your SKUs). Then I VLOOKUP all of the SKUs on a different sheet that are active (the stock status report in NAV only shows + inventory to save time). For this big list of SKUs I assign all of the same items my own SKUs that are the same. This is where where I combine sets together into the master SKU and combine same items into one SKU as well. This lets me check if other stores have the same item but under a different SKU. After that with lots of VLOOKUP tables and different variables for ratios of what each store gets I separate all of the inventory to different stores and on their own sheet with the required format. Then with another macro I save each of the store sheets as a CSV file with the date in the name and simply upload it.
In the end I save at least a day of work, all I have to do is generate a report in NAV, open the excel file, click a button, and then upload the CSV files.
Here is the code I use for importing the HTML file into excel (I am not a programmer so there is probably an easier way to do it, I simply edited a code I found on google)





With ActiveSheet.QueryTables.Add(Connection:= _

"URL;C:\Users\AH3A\Desktop\Stock Status Report.htm", Destination:=Range("$A$1"))

.Name = "inventory"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = True

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebFormatting = xlWebFormattingNone

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With




End Sub



Wow, Ah3A, I commend your creativity! To develop a macro this complex must have taken quite a bit of time.

But if I may, I'd like to suggest you talk to your VAR and see if they can craft a more direct approach to sending your inventory availabilty to your ECOM sites.  ArcherPoint deals with several companies doing vast e-commerce business, and there are better ways to do this through Web Services, XML Exports, and etc... depending on the variations of your ECOM sites capabilities. 

But if that's not an option, I'd encourage you to have them use the Excel Codeunit, which is free to download from the link above, to create an export for you form your Stock Status report.  The video will give them quick instructions on how to use it.  The data would then be exported directly to Excel as the report prints.  That  would free up your time for less tedious tasks! 



Great code Unit.  I was working on adding this Codeunit to the report 10135 Item Sales Statistics and got it to work perfect with the item but then when I go to break it Break down By variant im having some issues.  I'm not certain of how to alter it so it will show only the breakdown of the variant when there are variants as long of the stats of the items without the variant.  Currently its only showing both the stats of the item as a whole and the stats breakdown of each variant doubling the sales of the items with variants and not showing the variant code.  Any help would be awesome. Thanks!


MMcCarthy7, I'm glad you found the codeunit useful!  I often run into situations with NAV reports that have optional data, as you've seen with your report 10135 Item Sales Statistics.  My suggestion is to condition the field headings on the excel report based on whether the variants were selected, but keep the report linear by placing each piece of data in a single cell of a single row.  Since Excel reports can be resorted by the user, you want to always have the same key data fields (Item, Customer, etc...) so that even if they sort the data, they can again tie it back to the initial records printed. 

When printing your column headings in SheetSetup function, condition them based on the option field used to determine if variants are printed.  This basically allows you to create two different excel report layouts based on that report option.  Do the same thing when you output the data in the SheetDetails function - or you can also create a second "SheetDetailsWithVariants" function where you specify the second layout only and then use report option for printing variants to determine which function you call for printing the report details.  (Hint: If you create two functions, you can "wrap" them in the selection code for each report section of the NAV report used to print details so that you can assure you always produce the same report data in Excel as in NAV.)

Have fun! smiley


I think what might work is if you copy that imported text then paste it into notepad then again back to excel. But in that case you cannot get it to update from the html on reopen.


AH3A, you're correct - exporting a NAV report to HTML and then importing it into Excel is a pain! Using the Excel Codeunit, you bypass all that pain by just having NAV write the report directly to Excel.  No fuss - no muss! smiley


Ok time to try out nav
Just have to make sure to follow your instructions so I dont messanything up


AH3B, copy your report object you are going to modify to another numbered report in your license.  Most folks have one in the 50,000 range that's free.  You'll have to modify the name a bit, such as "Inventory Valuation with Excel" so that it will not cause a key error on the Object table ("Another report exists with the name....").  Then, with the original report intact, you can modify your copy for excel output.  But a good developer never does their work in production.  Create a development database where you can play with it and perfect it.  You'll find this method of exporting reports to excel to be very easy. 


Hi , I was trying to use the CU to export Trial Balance to Excel, but didn't succeed with the option of Page Break, i.e., not able to export seperate sheet with G/L account as name and seperate sheet for each account.


Hi,we have upgraded to NAV 2013 from 2009, and we are facing some problems 1) While exporting reports to Excel, columns/cells are merging so it creates lots of problem and we are not able to use excel properly; 2) The report dimension has been corrupted as now printers are leaving blank pages for ex- we have width 10inch , height 6inch stationary, but if width > height, then the layout is changing to landscape automatically (we want portrait), In NAV 2009, it works fine - the setting there was: Visual studio/report properties/page set up Width 8.3in Height 11.7in. But in nav 2013 it leaves blank pages as actual height is 6 in not 11.7 in, and if we try to set height 6 in, then the layout changes to landscape and print out is corrupted. :(


Nishant, NAV 2013 does not handle Excel output the same way as past versions. It uses .NET instead of the OCX when ran from reports, but also style sheets, I believe, when just taking it to Excel. Your VAR should be able to determine the cause by seeing the issue.

Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube