Navision Attain Export & Import Excel

hi all, in navision attain, you import and export excel as excel worksheets for your budgets and account schedule. but i find this functionality in navision a bit buggy, as sometimes when i attempt to perform this function, a read-only error will occur. the only way to correct this is to recompile the affected objects again. but even after recompiling, this problem will some again from time to time. anyone has any similar experiences? Edited by - jordi79 on 2001 Dec 13 06:07:31

Hi, we are using this function quite often (SG3.01) within our company database as well as during presentations with W1.3.01 without any problems. When do you get the error? Import? Export? Do you have the hole error message? Read you soon. Walter

Could the performance of this interface be affected by the version of Excel that is running or the verion of Windows? Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner

Well when the budget is exported to Excel, the progress bar stops at 3% and an exception error message poped up. the exact message is as follows: <This message is for C/AL programmers An exception error occurred from an external component. The component did not provide the exception description.> I am using Excel 2000 running on Windows 98. Once the exporting fails for the first time, it will fail for good. this is corrected after recompiling the objects. But, the importing of the budgets from excel, has never worked for me. Both importing and exporting works for you in budget, Walter? And may i ask, does the updating of excel workbook in account schedule works for you too?

Hi, I’m also has experience that error with navision version SG3.00 and SG.01, as I know with SG3.01 the error wiil come if you try to export ProfitLoss and Balance Sheet the others should working properly. I’ve reported this error to Navision and the response was suggest me to try with SG3.01B version but I just got message from Navision Singapore that we have to throw the SG3.01B version CD because this version contains a serious error that can make your database corrupt. I think we have to wait the next new version. Aloi

Hi everybody, as I mentioned before, nearly everything works fine with the Excel export AND import. Jordan, the error message appears on my workstation when I click “Include Totaling Formulars” on the Option Tab. We discovered long time ago, that this is related to the different Excel versions. I work with Excel 2002 on W2K workstation, no other language packs installed. David, do you discovered any Problems with Report 82 in the US? Since we discovered the Problem, we a not using this “totaling” anymore. Anyway, recompile the report does not help in my enviroment. Importing is working fine for us as well, but, compared with the logical work of the Navision using these Matrix forms, the import is different. When you add Budgeted Amount 100 you get one G/L Budget entry with 100, then you fill in Budgeted Amount 0 you get one G/L Budget entry with -100 when you use the Matrix form. During import, the amounts will be “recalculated” and added to the G/L Budget entry. Another import will “reset” the last import and the amounts will be “recalculated”. I have not had any problems during import. Even if I copy the sheet1 into sheet2, modify there etc. I can import direct from my sheet2. Any other suggestions? Aloysius, we received SG 3.01B Hotfix2 a few days ago but I haven’t heard anything about serious error. Can you explain, please. Kind regards, Walter

Walter, I must confess to not having done much testing with the latest version. The problems reported were quite similar to some I had with Navision’s interface to Microsoft Office products some time back - so I asked what seemed to me to be a reasonably intelligent question, given the situation. All these years I have survived ought to be good for something. But in this case, you are the expert. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner

Since I haven’t tested with Attain 3.01.B - I can’t say whether the problem exists there. However, I do known that this is a known issue with Attain 3.01.A

Wow, what a good discussion I found here. I would like to add my problem here as well. I’ve tested Navision Attain 3.01B with Excel 2000 in Windows 98. Yeap. The good news is Export Budget to Excel works perfectly. And the bad news is Import Budget to Excel displays an error which sound like this : “Couldn’t invoke member Open. OLE control/Automation server returns ‘The requested member doesn’t exist and the call tried to set read-only error.’” However, this doesn’t appear in Attain 3.01B, Windows Server 2000, Excel 2000. Hmmm…anyone with any idea ?

quote:


Originally posted by skchong: I’ve tested Navision Attain 3.01B with Excel 2000 in Windows 98. Yeap. The good news is Export Budget to Excel works perfectly. And the bad news is Import Budget to Excel displays an error which sound like this : “Couldn’t invoke member Open. OLE control/Automation server returns ‘The requested member doesn’t exist and the call tried to set read-only error.’” However, this doesn’t appear in Attain 3.01B, Windows Server 2000, Excel 2000. Hmmm…anyone with any idea ?


It sounds like the Import Budget from Excel object (presumably this is a nonprinting report, though it might be a codeunit or dataport) uses an automation variable which works fine when you have W2K Server installed but not W98. The problem might be an Excel problem rather than a Navision problem. ------- Tim Horrigan horrigan@aol.com

Hi Walter, I think the message that I got from Navision Singapore it’s not clear, because today we have a new CD from them with the same version that they told us not to use it and throught out right away. I’ve tested the new CD from them and there is no error again about export account schedule to Excel. I think Navision Singapore did customizetation for our region and there is a serious bug that they realized after they sent it the CD to us. So you don’t have worry about my warning and sorry about that. Aloi

I encounter the same problem stated earlier in Windows NT 4 and Office 97 environment. Wait a minute. We can’t expect all our customers to have Win2K and Office 2000 in order to run Import Budget from Excel. I think Navision should really look into this matter. Or perhaps someone can share with me what I’m missing that this problem arises.

Thanks to Navision Singapore that finally I can overcome this problem. Try to compile Table 370 which is the Automation Server for Excel. That will end the problem.

In the last 2 years I spent about 30% of my working time on Navision-Excel issues. I came accross different Excel versions (from Excel97 German to Excel 2002 English), different Operating Systems (Win98, NT, Win2000, Win XP) and all Navision versions from 2.01 to 3.01B. So this is what I can tell: EXCEL Luckily enough the different Excel versions since 97 are fully compatible as long as you use ‘normal’ functions. (Some Internet related functions where implemented in Excel 2000 which are not available in Excel 97 but if you don’t use these functions, an automation created for Excel 2000 will work on a client with Excel 97 installed). The only issue are language dependent formulas such as the SUM() formula which is called SUMME() in German speaking countries. You can easily solve this problem by using a global variable for the SUM-Formula which you initialize depending on the country:


CountryCode := xlApplication.International(1);  // 1=xlCountryCode
CASE CountryCode OF
  // English
  0,      // Default
  1,      // USA
  2,      // Canada
  44,     // United Kingdom
  61,     // Australia
  64,     // New Zealand
  353     // Ireland
       :  BEGIN
            xlFormulaSUM := '=SUM';
          END;
  // Deutsch
  41,     // Schweiz
  43,     // Österreich
  49      // Deutschland
       :  BEGIN   // Deutsch
            xlFormulaSUM := '=SUMME';
          END;
  ELSE
    ERROR ('Unknown Excel Country-Code %1 ',xlApplication.International(1));
    {
    ============= NOTE FOR THE PROGRAMMER ==============
    Include the language in question according to this list:
    CTRY_DEFAULT = 0;
    CTRY_AUSTRALIA = 61; // Australia
    CTRY_AUSTRIA = 43; // Austria
    CTRY_BELGIUM = 32; // Belgium
    CTRY_BRAZIL = 55; // Brazil
    CTRY_CANADA = 2; // Canada
    CTRY_DENMARK = 45; // Denmark
    CTRY_FINLAND = 358; // Finland
    CTRY_FRANCE = 33; // France
    CTRY_GERMANY = 49; // Germany
    CTRY_ICELAND = 354; // Iceland
    CTRY_IRELAND = 353; // Ireland
    CTRY_ITALY = 39; // Italy
    CTRY_JAPAN = 81; // Japan
    CTRY_MEXICO = 52; // Mexico
    CTRY_NETHERLANDS = 31; // Netherlands
    CTRY_NEW_ZEALAND = 64; // New Zealand
    CTRY_NORWAY = 47; // Norway
    CTRY_PORTUGAL = 351; // Portugal
    CTRY_PRCHINA = 86; // PR China
    CTRY_SOUTH_KOREA = 82; // South Korea
    CTRY_SPAIN = 34; // Spain
    CTRY_SWEDEN = 46; // Sweden
    CTRY_SWITZERLAND = 41; // Switzerland
    CTRY_TAIWAN = 886; // Taiwan
    CTRY_UNITED_KINGDOM = 44; // United Kingdom
    CTRY_UNITED_STATES = 1; // United States
    }
END;

This is the only issue concerning different Excel-versions. I was happily developing with US-Excel 2000 and while the customer had german-Excel 97 installed. Navision I was using the same automation fob’s on Fin 2.01, 2.60 and 3.01 without any problems and without any changes required. I therefore do not think that the Navision Version could be the source of any problem! Operating system That’s where the shit hit’s the fan! The main problem was (and is) to not only start Excel but to deallocate the task after the job is done. Let me give you an easy example: After having initiated Excel and opened or created the worksheet you usually invoke two statements:


xlApplication.ScreenUpdatingFALSE);  
xlApplication.Visible(FALSE);

The first one avoids automatic recalculation of the worksheet why you fill in your data. The second one makes Excel invisible which doubles the speed as no screen-output will have to be shown. Of course, after all data have been transferred to Excel you will …


xlApplication.ScreenUpdating(TRUE);
xlApplication.Visible(TRUE); 

… to pass control to the user. Now let’s assume that during Export to Excel an error occurs. Navision will halt and display the error. As the xlApplication.Visible(TRUE); statement never has been executed you will not see Excel in your taskbar and will therefore think is’t not loaded. However it is still there thus invisible and a second attempt to open the same worksheet will fail. In NT or W2k you can kill Excel in the Task manager. In Win98 you can try to do the same but stand a 50% chance that the system will hang! From my experience I suggest that you keep ScreenUpdating and Visible = TRUE while you are developing (even if it slows down the speed). You will be able to see what navision does and will find the exact location in the excel worksheet where the error occured. With best regards from Switzerland Marcus Fabian

I found that when I export Acc. Schedule with description ‘----’ or ‘====’, I hit error as <This message is for C/AL programmers An exception error occurred from an external component. The component did not provide the exception description.> And when I edit the table 370, voila !! I managed to export to Excel. The reason is ‘----’ and ‘====’ and ‘++++’ must have a single quotation. This is needed in Microsoft Excel. So, here’s my contribution and hope that it’s of good use to some of you. Enjoy! PS: I fail to upload a 12KB file. Hence, please email me I’ll love to share with you.