Output to Microsoft Excel using Validation

I’m currently working on an export to Excel which needs to be imported into NAV again. My main problem is that I want to prevent the user from entering more than the maximum allowed number of characters into each cell in Excel. Otherwise I risk either buffer overflow or that not all the data the user entered is imported into Excel is actually imported into Navision.

I found this example on how it can be done with VB:

[code]
With Range("e5").Validation
    .Add Type:=xlValidateTextLength, _
        AlertStyle:=xlValidAlertInformation, _
        Minimum:="1", Maximum:="30"
    .InputTitle = "Max. 30 characters"
    .ErrorTitle = "Max. 30 characters"
    .InputMessage = "Enter a text of max. 30 characters"
    .ErrorMessage = "You max. enter 30 characters"
End With
[/code]

Does anyone know if it Is possible to do a similar code in NAV? 

Hi Erik,

You can write code in the OnValidate of the table, form or page and assign back to the xrec value if invalid. Another suggestion is to declare a variable of the length you want and then add code to assign it to the field on the record

Hi Dave,

Thanks.

But I’m sorry, I don’t understand what it is that you’re suggesting here. It seams like what you’re suggesting is the solution to solve it when importing it into NAV. And that code I already have, so that I’m not getting runtime errors in NAV.

I really need this code to be in the Excel spreadsheet, so that the data are validated in Excel even before importing into NAV. And to do so then I need the “Validation.Add Type:=xlValidateTextLength,” into the cell when I’m exporting the spreadsheet.

Otherwise I risk ex. that a user is entering data which is then cut off when importing into NAV or that I get the error so that I cannot import. And neither options are ok.

Hi Erik,

I misunderstood the requirement. I thought you were talking about Nav code. Have a look a Data Validation in Excel - I think this is what you want

2308.Excel Data Validation.PNG

That is exactly what I want! :slight_smile:

I just want to be able to do it programmatically from Navision using the Excel automation. And the feature is already there, my question is just how I do the same thing in Navision as in the VB script in my first question. [:)]

Hi Erik,

Now I get it [:$]

Not something I have done but the automation ‘Microsoft Excel 14.0 Object Library’.Validation has all the properties you need. This is referenced on the Excel Buffer table in function CreateValidationRule

Hope this helps.

Hi Dave,

Yes! Now we are getting closer! [:D]

Now I just need the right translation of the different Validation.Add parameters. I think that they represent some constant values, I just need to find where I can find a “translation” of these values into integer values, such as they are used in the the Excel Buffer table.

Btw. do you know where in NAV that the CreateValidationRule function is being used? (I don’t have an updated cross-ref tool by hand).

Hi Erik,

It’s used in report 82 - Export Budget to Excel

Hi Erik,

A late tip how to “write” VB code for activities which can be done from Excel UI. (and in your particular case you could use this approach)

Start Recording a Macro

Perform needed actions, in your case:

  1. select range,
  2. format it as text
  3. open Data Validation dialog, set params, close it

Stop Recording a Macro and go edit it, here’s the code:

Range("A1:A10").Select
Selection.NumberFormat = "@" 'format as text

' this is important, as otherwise Excel's format guessing kicks in
' when you export some data which can be treated as number or worse, as Date
' for example, Code field from Navision containig something like "9/11"
' Excel will autoformat it as Date, and you can NOT ever override it
' even by trying to reformat it back to Text.
' This will cause you problems, when you'll try to import your sheet back to
' Navision.

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="50"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Imput validation"
.InputMessage = ""
.ErrorMessage = "Text must not be longer than 50 chars!"
.ShowInput = False
.ShowError = True
End With

This way you do not need to goooogle or dig through VBScript manual to quickly “write” some simple code [:)]

Well, I however do not have a quick idea, how the same script can be initiated from within Navision thru Automation, sorry, I’m not a Navision programmer.

a followup to my previous post:

as you do not now how many rows there will be, instead of

[code]
Range("A1:A10").Select
[/code]

you might use

Columns("A:A").Select

Thanks, but as far as I understand, then your suggestion sort of requires that you already have the Excel file when starting?

Eric… If I read you problem correctly:

“I want to prevent the user from entering more than the maximum allowed number of characters into each cell in Excel”

You could do this thru the Excel Data menu under “Data Validation / Validation Criteria / Allow: Custom” and then use a formula like:

=LEN()<= (Along with appropriate warning messages for the users. Note the = sign in front.)

You’d then do a “copy/paste special:validation” to other cells that need this check. This will dynamically update the cell reference to cells being pasted to.

I’m also assuming you’d like to do this automatically so you could :

  1. Record this as macro which applies the data validation check to the necessary cells and then have this macro run when the export Excel sheet is created/opened (e.g. via auto_open). However, this is pretty much the same method as the VBA code you mentioned above.

  2. Since using macros can get problematic for users who don’t or can’t enable macros, another thought is to have an empty spreadsheet with the Data Validation I mentioned above applied to necessary cells (e.g to a particular row/column) and when you create the data in Excel take it and paste values to this empty spreadsheet and save as the spreadsheet the users will edit (this will preserve the data validation). This way they don’t need to envoke macros.

Anyway just some thoughts. Stef…

This is something I have done before several months ago and yes other posts are on the right track. My case was slightly different, as we exported Purchase Quote Line Values, sent them to the Vendor(s) and then imported them back in. So the validations were added on a cell by cell basis… The vendors weren’t adding new lines, just updating existing exported values. Anyway, this should get you pretty close,it sounds like you want the validation on a column, so you should be able to change the Range as needed.

To do this, I started by recording a macro to determine the Excel Code required. You also have to use the MSDN library to figure out the enumerated types to translate into C/AL code.

This changes the NAV code on the Excel Buffer Table to create the Validation at the time the Worksheet is created.

What I did was add two new fields to the Excel Buffer Table called Text Length Validation (Boolean) and Max Text Length (Integer). When you fill the Buffer table, you will just enable that flag and set the text length to the limit you want to enforce.

Then in the CreateSheet function on the table, you can add the following code below the “If Underline” section of code…

IF “Text Length Validation” THEN BEGIN
XlWrkSht.Range(xlColID + xlRowID).Validation.Delete;
//xlValidateTextLength, xlValidAlertStop, xlLessEqual
XlWrkSht.Range(xlColID + xlRowID).Validation.Add(6,1,8, “Max Text Length”);

XlWrkSht.Range(xlColID + xlRowID).Validation.IgnoreBlank := FALSE;
XlWrkSht.Range(xlColID + xlRowID).Validation.ShowInput := TRUE;
XlWrkSht.Range(xlColID + xlRowID).Validation.ShowError := TRUE;
XlWrkSht.Range(xlColID + xlRowID).Validation.ErrorTitle := ‘Error Text Length Exceeded’;
XlWrkSht.Range(xlColID + xlRowID).Validation.ErrorMessage := STRSUBSTNO(Text50001,“Max Text Length”);

END;

I have the Text Constant Text50001 = “Text must be less than or equal to %1 characters!” that is the message you will receive in Excel.

Hi Brandon,

This was great. Thanks! I have been searching for the place on MSDN to figure out how to translate the enumerated types to numbers usable in C/AL, but unsuccessful. Do you remember where you found it?

Hi Erik

Have you made any progress on this? I’m managing a project to get a fixed asset tracking scanner software’s data imported into NAV 2009 SP1 via Excel and do the same to get NAV data into the scanner. It would be good to know how your coding went.

Related link: http://dynamicsuser.net/forums/p/22958/203980.aspx#203980

Thanks for an excellent forum.

Kuhen

Hi Erik,

you can find it in the ADG (p. 382 - at least for NAV 5.0x)

Regards,

Jan