Excel Worksheets

I have completed several reports that export to Excel from Navision and they work great. All only have 1 worsheet. Now, I need to be able to create a spreadsheet with multiple worksheets and switch between them. Does anyone know how to do this?

Let’s say you need 2 sheets. You could use the following approach: xlsSheets := xlsWorkbook.Worksheets; xlsSheet1 := xlsSheets.Add; xlsSheet1.Name := sheetName1; xlsRange1 := xlsSheet1.Cells; xlsSheet2 := xlsSheets.Add; xlsSheet2.Name := sheetName2; xlsRange2 := xlsSheet2.Cells; (The generalization for N sheets should be obvious [;)]) Then you can use the xlsRange[12] variables to fill in the sheets, using either one as needed. They all can coexist peacefully, and there is no need to switch “active sheets” or stuff like this. Hope this helps,

Thanks alot for the assist. What I will be doing is using the ChangeCompany function to produce this spreadsheet, with each company going to a separate sheet. The method you propose would work but, I was hoping for a method that would let me dynamically send Information to each sheet, regardless of how many companies are used. See the code below. If you can adapt it as I have said above, I’d really appriciate it. OnPreReport: CREATE(xlApp); xlBook := xlApp.Workbooks.Add(-4167); xlSheet := xlApp.ActiveSheet; OnPreDataItem: xlSheet.Name := ‘Open AR’; xlSheet.Range(‘A1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Customer No.”); xlSheet.Range(‘B1’).Value := ‘Customer Name’; xlSheet.Range(‘C1’).Value := ‘Customer Address’; xlSheet.Range(‘D1’).Value := ‘Customer City’; xlSheet.Range(‘E1’).Value := ‘Customer State’; xlSheet.Range(‘F1’).Value := ‘Customer Zip Code’; xlSheet.Range(‘G1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Document Type”); xlSheet.Range(‘H1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Document No.”); xlSheet.Range(‘I1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Posting Date”); xlSheet.Range(‘J1’).Value := ‘Original Amount’; xlSheet.Range(‘K1’).Value := ‘Amount Due’; xlSheet.Range(‘L1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Your Reference”); xlSheet.Range(‘M1’).Value := ‘Retainage’; i := 1; OnAfterGetRecord i += 1; Row := FORMAT(i); xlSheet.Range(‘A’+Row).Value := “Customer No.”; xlSheet.Range(‘B’+Row).Value := Cust.Name; xlSheet.Range(‘C’+Row).Value := Cust.Address; xlSheet.Range(‘D’+Row).Value := Cust.City; xlSheet.Range(‘E’+Row).Value := Cust.State; xlSheet.Range(‘F’+Row).Value := Cust.“ZIP Code”; xlSheet.Range(‘G’+Row).Value := FORMAT(“Document Type”); xlSheet.Range(‘H’+Row).Value := “Document No.”; xlSheet.Range(‘I’+Row).Value := “Posting Date”; xlSheet.Range(‘J’+Row).Value := Amount; xlSheet.Range(‘K’+Row).Value := "Remaining Amount ($); xlSheet.Range(‘L’+Row).Value := “Your Reference”; xlSheet.Range(‘M’+Row).Value := Retainage; Thanks for all of your help!

OK, so your report’s data items look somewhat like Company >>“Cust. Ledger Entry” (>> stands for 2 spaces, which seem to get lost during formatting) If all you want to do is create a new sheet for each Company instead of switching between sheets in “random” order, then you do not need multiple variables for sheets and ranges - instead, your code only needs to be slightly modified. OnPreReport CREATE(xlApp); xlBook := xlApp.Workbooks.Add(-4167); Company:OnAfterGetRecord xlSheet := xlBook.Worksheets.Add; xlSheet.Name := Company.Name; // all the stuff that created the column headers in OnPreReport is moved here Everything else remains the same! This adds a new sheet for each Company, fills in the headers, and the rest of your code remains unchanged and simply uses this new sheet in the subordinate data item.

Bill, it seems that you edited your last post while I was replying to it [8D] My code still seems to be doing the job, though [;)]

Yes, I was. I read my reply and it sounded a little “pushy” so, I changed it. You are right though, your code will still work. Thanks so much!