I have made a report that use excel automation. The following code is used to end the report in onPostReport: IF createExcelOutput THEN BEGIN xlsRange.Select; xlsRange.EntireColumn.AutoFit; xlsRange := xlsWorksheet.Cells.Item(1, 1); xlsRange.Select; xlsWorkbook.SaveAs(saveFilename); //xlsApp.Visible(TRUE); xlsApp.Quit; END; But every time i open the xls file for the first time it crash. Second time i open it it works perfect… i have tried with open attain, open execl, closed attain totaly before opening the xls file and even restartet my computer… but it allways crash (and only) the first time. Anybody know about this and maybe got a proposal for a solution?
Hi, Please clear the reference of all the variable before quitting from Excel. i.e. CLEAR(xlsWorkbook); CLEAR(xlsRange); CLEAR(xlsApp); I hope that would sort out your problem. Best Regards.
Nope… tried some versions of those but still hang when i open the xls file… And do anybody know how to make it overwrite the file automatic? No i get this “Do u wanna overwrite xxxxx.xls” every time i run the report [:I] So… suggestions are taken with big pleasure [:D]
Please try this: =============== CLEAR(xlsApp); CLEAR(xlsWorkbook); CLEAR(xlsWorksheet); IF createExcelOutput THEN BEGIN // IF NOT CREATE(xlsApp,TRUE) THEN ERROR(‘Error message’); xlsApp.Visible(FALSE); xlsWorkbook := xlsApp.Workbooks.Add; xlsWorksheet := xlsWorkbook.Worksheets.Add; xlsWorksheet.Range(‘A’+‘1’).Value := ‘This is data’; xlsRange := xlsWorksheet.Cells.Item(1, 1); xlsRange.Select; xlsRange.EntireColumn.AutoFit; xlsWorkbook.SaveAs(‘c:\sbm\excelfile.xls’); xlsWorkbook.Close; xlsApp.Quit; END;
Hi Frodio
quote:
Originally posted by Frodio
And do anybody know how to make it overwrite the file automatic? No i get this “Do u wanna overwrite xxxxx.xls” every time i run the report [:I]
Try the following lines:
....
xlApp.DisplayAlerts(False);
xlBook.SaveAs('YourFileName.xls');
xlApp.DisplayAlerts(True);
....
Don’t know, if this works with automation. In Excel- VBA it works [8D]! bye Andre
Are you using Attain? If so, you can populate the Excel buffer table and use the functions already on it to create your excel sheet. I had a similar problem, where by I created my Excel sheet ran my macro saved the file and closed. Then excel crashed. but all the stuff that had been done survived. To fix it I just restructured my code slightly. ??
This is getting interesting… I added the clear function for each variable… But on the CLEAR(xlsApp) i got an error that says “This Automation Variable is not momentary option. You can chose it by creating it or connect it” … badly translated from norwegian though [:I]… But when i have done this the excel file open perfectly… but when u close excel u got an error from excel saying there has been an error and the program will close. Then i removed the CLEAR function then excel crash before i got to see any of the data inside the excel file (like mentioned earlier on this thread). I use Attain version NO 3.01B The variable look like this… : xlsApp Automation ‘Microsoft Excel 9.0 Object Library’.Application I guess the error got something to do with me clearing the whole xlsapp before quitin? Right now i am realy stuck and dont know what to do for a next move… tried like 100 differente things here. So please speak up if u got some thoughts about this crossing your mind when u read this [:p] For those who dont have any ideas… wish me luck [:D]
Hi, I have only the following lines in my codeunit Excel In/Out:
**FileClose**
...
xlBook.Close;
xlApp.Quit;
...
It works! Without any clearing! Frodio: In your first posting you wrote
quote:
“The following code is used to end the report in onPostReport:”
You should put the code into a codeunit instead into a report. Maybe this could solve your problem. For further information please check the tips & tricks forum. There is a cool posting from Marcus Fabian. http://www.navision.net/forum/topic.asp?TOPIC_ID=1318 bye Andre
Thats a good point, I remember having put some code in a report that crashed and then placed it in a codeunit and it worked fine!
quote:
Originally posted by Frodio
I guess the error got something to do with me clearing the whole xlsapp before quitin? Right now i am realy stuck and dont know what to do for a next move… tried like 100 differente things here. So please speak up if u got some thoughts about this crossing your mind when u read this [:p] For those who dont have any ideas… wish me luck [:D]
Hi Frode! CLEARing an automation variable when you still need to use it (e.g. by calling the App.Quit method) is generally a BAD idea [8D] The DisplayAlerts trick mentioned by Andre works in Automation too, at least it did so on my system [:D] About the Excel crash: Could you be more specific about how this crash looks like? Is there an error message, does the system freeze, does it simply disappear? In any case, if rebooting doesn’t help, then it’s not a problem of Navision holding any stale references to the Automation object or whether the code is in a report or codeunit. Instead, this is strong evidence that the Excel file is somehow corrupt or may contain VB code that causes it to misbehave. Of course, I’m wishing you luck anyway [:D]
Looks like I stuck there too. 
This is problem of Item property: xlsRange := xlsWorksheet.Cells.Item(1, 1); Navision has replaced Item property to Item (Index/RowIndex, [ColIndex],[Value/Item]) for modify of cells. But if it used for selecting cell of range, it makes Excel crash. Office 2000. NA 3.60. So, how write in C/AL such sentencies as “For Each” in VB. Or select one cell from range, referencing only index, like in VB “= Range.Item(n)” ? Example: We have named range, it can be vertical, horizontal, multi areas. Range is all these cells, so how select first cell of range or ? Any sugestions ?