I’m working on a Sales Commission report by Salesperson and I’ve got an Excel Automation problem. I want to create a new worksheet for each Salesperson I read from my Salesperson Master table, but I don’t know how to setup the variables for the worksheet objects on the fly[:I]. It would really be [8D]“cool”[8D] if I could copy my layout from a template worksheet to the new salesperson too!!! If you’re going to tell me to use the Excel Buffer table…can you explain how to use it??? I’ve looked at reports 81 & 82 and all I can do is scratch my head![:(] Any help is MUCH appreciated!
Just the simple overview - [;)] Basically you should use the excel buffer, it is a great way to make spread sheets. It takes about 15 minutes to create a report that will do this. (I know because the first time I ever tried was during a 15 minute break in training when one student asked me if it was complex.) Basically you have four things to worry about.
- Initialization.
Copy this code straight from one of the existing reports.- Maintaining Rows and Columns
Create a variable for column and row positions, and make sure you ALWAYS increments columsn then rows. And always reset the Column and increment the Row at the same time.- Entering data and formats
Entering the data is the easiest part. I have modified the Excel buffer as needed to add things such as Strikethough font size etc. If modifying an existing report, then populate the Excel buffer on the OnPostSection of the appropriate section. If designing a new report, do it the OnAfterGetRecord of the DataItem- Creating Spreadsheet.Again copy this straight from one of the existing reports.
Having said this, the best thing is to create a very simple report (say an Item list) then just use this as the base of all reports you do after this. (If you really get stuck, I can help you out with a sample.) Let me know if this helps. [;)]
Hi Faithie Unfortunately the Sheet.Copy - function doesn’t work properly with Excel- automation. AFAIK you can’t set the Before / After variable. But perhaps this can help you: I copy the content from a template sheet to a new added sheet: xlSheet := xlBook.Worksheets.Item('YourTemplate') xlSheet.Activate; xlRange:= xlSheet.Cells; //mark the entire sheet xlRange.Select; xlRange.Copy; xlSheet := xlBook.Worksheets.Add(); xlRange:= xlSheet.Range('A1'); xlRange.Activate; xlSheet.Paste; xlRange:= xlSheet.Range('A1'); xlRange.Select; xlSheet.Name:= 'YourNewName'; ... put your data in the sheet bye André
David, simple question It goes much faster if you don’t use the excell buffer and just create your own automation variable. You do not have to write to a table. Am I wrong? At least for a simple report.
“SalesPerson dataitem” | → “Other dataitem” If I understood your problem right then try this one: *Variables: Name DataType Subtype eExcelApp Automation ‘Microsoft Excel 9.0 Object Library’.Application eWorkBooks Automation ‘Microsoft Excel 9.0 Object Library’.Workbooks eWorkBook Automation ‘Microsoft Excel 9.0 Object Library’.Workbook eWorkSheets Automation ‘Microsoft Excel 9.0 Object Library’.Worksheets eWorkSheet Automation ‘Microsoft Excel 9.0 Object Library’.Worksheet *Code: <<“SalesPerson dataitem” - OnPreDataItem()>> CREATE(eExcelApp,TRUE); eWorkBooks := eExcelApp.Workbooks; eWorkBook := eWorkBooks.Add;//New workbook eWorkBook.Activate(); eSheets := eWorkBook.Sheets; //This is the variable you need!!! <<“SalesPerson dataitem” - OnAfterGetRecord()>> eWorkSheet := eSheets.Add; //New WorkSheet eWorkSheet.Name := Salesperson.Name; //Salesperson name as a worksheet name eWorkSheet.Activate; //…your code <<“Other dataitem” - OnAfterGetRecord()>> eWorkSheet.Range(‘B1’).Value := ‘Some data’; //e.g. //…your code <<“Salespreson dataitem” - OnPostDataItem()>> //Finishing… eExcelApp.Visible := TRUE; eWorkBook.Activate;
WOW! Three different scenarios! Surely I can make one of them work [:D] David - I do need a sample…I guess I just don’t get it. Can you show me how you’d populate the table for a field of data, for a format, and so forth. If this will take care of the automation setup and whatnot, then I’m all for it! But I’ll need to be able to format columns, color my headings, bold print, and so forth. Gotta sample code sniplet you can share? Andre - Thanks! That will prevent me from having to run through all the setup code each and every time. Mucho Gracias! John - nope, haven’t seen your frog, but I’m sure it’s quite lovely.[:p] Raimond - what does the variable setup for eSheets look like? Is it just a Sheets type? I’m using Excel 8.0 objects. Will it still work? I think this is exactly what I was looking for. Thanks All! Looks like I might finish this by Friday after all…with a little help from my friends![:)][:)][:)]
Faithie - I just forgot to write it down 
Name DataType Subtype eSheets Automation ‘Microsoft Excel 9.0 Object Library’.Sheets Good luck! P.S. I guess that it will work on Excel 8.0 too…
Hi Raimond I think you don’t need the Sheets - variable in your code (if you only want to add a new sheet). You can add a new sheet direct from the WorkBook - variable. eWorkSheet := eWorkBook.Worksheets.Add(); It saves a variable and a few lines of code [:D]. bye André
Hello André Basicly you are right, but it would be a good solution in Visual Basic, but not in Navision Attain (atleast not in 3.10). I use 3.10 (I don’t know how it works in 3.60 or 3.70) and in this version you can’t see methods and properties deeper than 1 level! For example, if you have a variable eWorkBook, you cant see “ADD” method of “Worksheets” in “C/AL Symbol Menu” :(( How about that? Bye P.S. Tell me if I’am wrong
Hello Raimond YEP. You are right. You can’t see the methods and properties deeper than one level. If you wish to see this it is better to use a Sheets- variable. But as I wrote [;)]: //…if you only want to add a new sheet…// There you don’t need to know something else than the Add method. Then IMHO it is easier and faster to use WorkBook.WorkSheets.Add(). btw. I’m working with 3.01 here. bye André