Automation Export to Excel

Business Central/NAV Developers Forum
1

I have an existing spreadsheet with many sheets. Sheet1 has been renamed Header. I need to open the spreadsheet from Navision and update Sheet1(Header) and save the spreadsheet without changing or losing the content of the other sheets. The code below should instatiate Excel CREATE(xlApp); xlApp.Visible(FALSE); xlApp.Workbooks.Open( FileName); Sheet1.Activate; xlSheet := xlApp.ActiveSheet; instead it returns the run time error message : This Automation Variable has not been instatiated You can instantiate it by either creating it or assigning it. The message is clear enough but I do not know how to resolve the problem. Please Help

2

There is an Excel interface in Navision in Table 370 Excel Buffer. Maybe you can have a look at that.

3

I guess the Sheet1.Activate fails as it has not been referred before. You need to change your third line to: xlWorkbook := xlApp.Workbooks.Open( FileName); xlSheet := xlWorkbook.Sheets.Item(1); xlSheet.Activate();

4

quote:

There is an Excel interface in Navision in Table 370 Excel Buffer. Maybe you can have a look at that.
Originally posted by marq - 2005 Nov 11 : 07:41:51

Table 370 does not cater for multiple sheets.

5

quote:

Thank You for the reply. Unfortunately I still get the same error message. The code looks like this now CREATE(xlApp); xlApp.Visible(FALSE); xlApp.Workbooks.Open( FileName); xlSheet := xlWorkBook.Sheets.Item(1); xlSheet := xlApp.ActiveSheet;

There is one error in your code: You are using xlWorkBook without initialization. The Workbook is returned by the Workbooks.OPEN() function: So change your third line and use the return value of the function. xlWorkBook := xlApp.Workbooks.Open( FileName);

6

And to have the complete code together: CREATE(xlApp); xlApp.Visible(FALSE); xlWorkbook := xlApp.Workbooks.Open( FileName); xlSheet := xlWorkbook.Sheets.Item(1); xlSheet.Select(); xlSheet.Activate(); this is all code you need. Now you can do with the worksheet whatever you want.

7

quote:

And to have the complete code together: CREATE(xlApp); xlApp.Visible(FALSE); xlWorkbook := xlApp.Workbooks.Open( FileName); xlSheet := xlWorkbook.Sheets.Item(1); xlSheet.Select(); xlSheet.Activate(); this is all code you need. Now you can do with the worksheet whatever you want.
Originally posted by tb@softsys.at - 2005 Nov 11 : 11:15:13

Thank you it works well