and again: Navision and Excel...PLEASE HELP!

Hello! I am working on a project in which I have to transfer fields from MS Excel to Navision. Everything works except for one thing: Some of the fields in Excel are far over 250 chars. (Its about pricelists, these fields contain descriptions). How can I get these fields to Navision? For the information: one of the goals of this project is to make sure the end-users won’t have to do anything with Excel themselves: the only interface they have is Navisions’. Thanks. Anolis Netherlands

Hi, I think you can create Macro that will cut all fields up to 250 characters and run this Macro before start import. Valentin Gvozdev BMI Inc.

You just have to find a way to truncate those text fields. The simplest way is to make a new column which applies the Excel =LEFT() comnand to the original column. You shouldn’t lose much data anyway, because (unless I am mistaken or unless they changed this since I was a professional Excel 4.0 macro programmer) Excel can only have 256 characters of text in a field. The best way is to see if you can find the original text file that the spreadsheet was made from and use a nonprinting report to extract data directly from it (rather than going through Excel first.) ------- Tim Horrigan

Hi. You could try to look at the text import Mergetool uses to import objects. You should find this on you Navision CD, d:/merge/merge.fob etc. Regards Gudmundur Petursson

Thanks, all of you. It’s all been of great help! I mannaged to get teh fields into excel now. Fo the information: I’m using Excel 2000. In this newest version a field can contain more than 256 characters. There is only one problem left: The transfer of the data takes a lot of time! This is propably because Navision has to analyse the exceldocument everytime it imports a field. This has to happen because the exceldocuments that are to be imported, differ in Lay-out. I created an import-wizzard in which the End-User has to answer some questions. Navision uses the gathered information to ‘filter’ the excel-document on categories, sub-categories and besides that Navision has to determine where the Id’s, descriptions and prices are located. When I import a document of lets say 2000 rows, I’ve got enough time to get me a cup of coffee and drink it! Has anyone experience with letting Navision Analyse externel data like this?

If you are importing a large file then don’t use dataports - instead change to a codeunit. Why? Dataports have a lot of overhead.

It sounds like your original Excel spreadsheet actually had named ranges. Lucky you! I have seen many Excel spreadsheets in my day. I have never yet run into a corporate finance type who knew how to create named ranges in an Excel spreadsheet… let alone how to use names for other purposes in Excel (e.g., to stor constants.) ------- Tim Horrigan

No, there are no named ranges. That’s the tricky thing: Navision not only has to analyse, but has to categorize as well. This is what makes it so slow. Martin Pennings Sittard Netherlands

About this 250 Character issue: I have read in another forum that a message-body which exceeds 250 Characters can be imported into an Array. So id you have a Text array of st250 you should be able to import 1000 Bytes into the array. If it works with Message-bodies, why not with Excel Cells? Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

This sounds nice, and I tried it already, but this time the problem is different. Sure, it’sa possible to import more than 250 characters, but how do I get them from Excel? I can get the first 250 characters, and put them in some text variable, but how to get te rest of the characters from the Excel field? By the way, if anybody is interested, I made a “virtual textbox” for Navision, which can contain an unlimmited amount of characters. It stil gots some bugs, but it might be helpfull. Anolis Sittard Netherlands

Martin, please share some of the information concerning the virtual text box for Navision Financials. I started a new topic: Virtual Text Box in NF - Developer Forum. Thanks, Best regards, Soren Nielsen, moderator Navision Online User Group