When the code, that populates data in Excel Buffer for single sheet, is finished we have to call this function to write sheet and flush all the data so it is free for another sheet.Īlso, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer. I really want the first worksheet to be shown but you might want it the other way.įinally, we WriteSheet and delete all data currently in Excel Buffer. In line 19 I have commented out line of code that sets the last sheet that we call to be initially shown when we open Excel workbook. Then the main task of this function is executed in line 17 when we append new worksheet to currently open workbook. If this is true then we append ‘-01’ so we can use INCSTR(SheetName) until we find unused SheetName – so we don’t overwrite data on existing sheet. In line 9 we check if there is sheet in workbook that has the same name as the one we have passed as parameter – which can happen in subsequent calls. If it is not initialized we call standard CreateBook function and set the ActiveSheetName to that “first” excel sheet.
First in line 5 we check if the WorkSheetWriter is initialized. Let me explain few key points in this code.
ActiveSheetName := SheetName //last sheet activatedĪdditional variable that I have used is FirstIncrement (Boolean) – all other are global variables in Excel Buffer table. XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName) WHILE XlWrkBkWriter.HasWorksheet(SheetName) DO BEGIN In Excel Buffer table (370) I have created AddNewSheet function that receives only one parameter (SheetName).ĪctiveSheetName := SheetName //first sheet activated So I decided to write function that enables just that, open single Excel workbook and just adds new worksheet one after another. I haven’t found any function in ExcelBuffer that does that – correct me if I am wrong. Let say you want to export Item and Item Ledger Entry table to single Excel workbook. The one function that is needed, in real life, when exporting data to Excel workbook is to export data to the same Excel workbook but to different worksheets. More information about that can be found on NAV Team blog in article Excel Buffer Using Open XML Instead of Excel Automation. In Microsoft Dynamics NAV 2013 Excell Buffer table (370) changed from using Excel Automation variable to Open XML 2.0 DotNet interoperability classes.
0 kommentar(er)
