I am doing a report which prints the summarised data and also transfers it to a Excel spreadsheet. I create a new Excel workbook in OnPreDataItem as follow: RowNum := 0; IF CREATE(xlApp,FALSE) THEN BEGIN xlApp.Visible(TRUE); xlApp.ScreenUpdating(TRUE); xlWorkbooks:=xlApp.Workbooks; END ELSE ERROR(‘Could not start Excel’); xlWorkbook := xlWorkbooks.Add; xlWorksheet := xlApp.ActiveSheet; and transfer data in OnAfterGetRecord like this: xlCell := STRSUBSTNO(’%1%2’,‘A’,RowNum); xlWorksheet.Range(xlCell).Value := “Salesperson/Purchaser”.Code; xlCell := STRSUBSTNO(’%1%2’,‘B’,RowNum); xlWorksheet.Range(xlCell).Value := FORMAT(NetSales + ForecastSales); RowNum := RowNum + 1; The report creates a workbook successfully but pops up a message ‘Internal error in module 45’. Anyone knows what does this mean or what is better way to use Excel automation in Report object rather than Codeunit object?
I was playing with Excel Automation the other day, and found that when I ran it from a report it did not seem to “release” the Excel instance to me, and caused the system to crash retaining a instance of excel not visible. So when I try to run it again it crashes as the original Instance of Excel is still going. Try it in a Codeunit. If it works in the Codeunit then call the functions on the codeunit from your report passing it parameters if you have too. If you need any more help dont hesitate to reply. Cheers Tony Edited by - tonyh on 2002 Mar 28 10:40:41
Thanks, Tony. It works fine in codeunit object.
If you look at Report 29, there is a reference to a table called “Excel Buffer”. This table has the Excel automations defined inside it and also functions through which you can insert records into this table which will do the Excel interface. I just reused some code from report 29: the ‘EnterCell’ function is pretty much all you need and you just gotta figure out the input data going into this function. You will then call the EnterCell function on each AfterGetRecord for example with the Record.Field to be passed to this function. I know it would be more brainy to work with Excel from scratch, but doing it this way makes life MUCH easier. For people who are not hardcore MS people, it is a big relief. Secondly in the ExcelBuffer table there is a function called GiverUserControl or something which I believe displays the resulting Excel sheet. I insert the values on OnAfterGetRecord through EnterCell function. On PostDataItem, you can call the GiveUserControl. This will ensure that the report runs through all the records and THEN automatically launch Excel with the filled up columns. Try it!