I’ve got a question relating to extracting data to Excel. I have an automation object setup with a workbook and 5 sheets. For each sheet I’ll have the same THIRTY column headings, the same Borders, the same formatting, etc… The sheets are just used to hold unique groups of records. I’d rather not replicate the code for the column headings, borders, and formatting for all 5 sheets…that could be UGLY(30 * 5)![:(] I’d like[:D] to create a function and pass it my sheet name and then refer to that sheet when inserting columns (VariableSheetName.RANGE(‘A1’).Value := ‘Item’;). Is there a way to do this, and how (explicidly…I’m a newby)? If not, is there another alternative to prevent repeating the same code for the column headings, etc…? Any assistance is greatly appreciated.[:)]
What version are you using? There is an object (Excel Buffer) in 3.60 that does most of your work for you. If you aren’t running 3.60, then I have also brought this Table down to 2.60 without much problem.
Hmmm…what’s this Excel Buffer table about anyway? I see it but how’s a table going to help me? Can you explain to me how to use it? The fields in the table look like some sort of template for writing a cell at a time??? My version is 3.10. I appreciate the help![:)]
Excel Buffer table works as Temporary table. You can write data in this table by filling out fields [:)] After that you need to run functions that will make Excel workbook, worksheet and write data to excel. Take a look at the functions inside of table, very usefull [;)]
Hi This should do the job (without testing & without using Excel buffer) xlSheets:= xlApp.Worksheets; i:= 1; Repeat xlSheets(i).Select; ... Do your format thing ... i:= i + 1; Until i > 5; bye André bye André
Andre - I added the code as shown and made xlSheets a variable of type Automation subtype ‘Microsoft Excel 8.0 Object Library’.Worksheets. But when I hit my F11 I get a code error saying "xlSheets is not a function and only functions can be called with ‘(…’. What did I do wrong? [:(]
Hi Sorry. I forgot the Item property [:(]. xlSheets:= xlApp.Worksheets; i:= 1; Repeat xlSheets.**Item(i)**.Select; ... Do your format thing ... i:= i + 1; Until i > 5; bye André
I’ve got the following code: CREATE(xlApp); xlBook := xlApp.Workbooks.Add(-4167); xlSheetIC := xlBook.Worksheets.Add; xlSheetCP := xlBook.Worksheets.Add; xlSheetCE := xlBook.Worksheets.Add; xlSheet99 := xlBook.Worksheets.Add; xlSheets := xlApp.Worksheets; i:= 1; x := xlBook.Worksheets.Count; Repeat xlSheets.Item(i).Select; xlSheets.Range(‘A3:AE3’).Font.Bold := 1; xlSheets.Range(‘A3:AE3’).Interior.ColorIndex := 6; xlSheets.Range(‘A3:AE3’).Borders.LineStyle := 1; xlSheets.Range(‘A3’).Value := ‘Item’; xlSheets.Range(‘B3’).Value := ‘Description’; … i:= i + 1; Until i > x; But now it’s giving me an error “The return value should be used for this function”. I know it’s not the function that’s holding the code - it’s simply a function with no variables - a place to store my code - that’s all. What in the world could be wrong?[:(]
Hi Faithie
quote:
Originally posted by faithiejlewis
xlSheets.Range(‘A3:AE3’).Font.Bold := 1;
I’m not sure but this line could be wrong. I format bold with True or False: e.g. xlRange.Font.Bold:= FALSE; Here are a few threads regarding Excel & format: http://www.navision.net/forum/topic.asp?TOPIC_ID=1318 http://www.navision.net/forum/topic.asp?TOPIC_ID=1603&SearchTerms=excel,,ColorIndex http://www.navision.net/forum/topic.asp?TOPIC_ID=6208&SearchTerms=excel,,LineStyle bye André
I changed the bold statement and it doesn’t matter - 1 or True, it works the same way. My error comes up until I comment out the line that says: xlSheets.Item(i).Select; I’m wondering if I have my different classes of Excel right - xlApp, xlBook, xlSheets, xlSheetIC, xlSheetCP… If I’m setting up xlSheets, do I still need xlBook, or does the WorkBook function disagree with the Worksheets function? I don’t think so[;)]…but I’m grasping at straws here. Maybe I’ve got xlSheets wrong - I’m setting it up as an Automation type with subtype ‘Microsoft Excel 8.0 Object Library’.Worksheets. Does anyone have a working example? Still grasping…
Hi Now it’s up to me to test it. I post my way asap. bye André
Hi Faithie My version works [8D]. I don’t know where the error is. Perhaps the Sheets.Select. And IMHO you open a new Sheets instance with xlSheets:=xlApp.Worksheets; Please try it with xlSheets:=xlBook.Worksheets;. IF CREATE(xlApp,TRUE)THEN; xlApp.Visible(TRUE); xlApp.Workbooks.Add; xlBook:=xlApp.ActiveWorkbook; xlBook.Worksheets.Add; xlBook.Worksheets.Add; xlBook.Worksheets.Add; xlBook.Worksheets.Add; xlBook.Worksheets.Add; i:= 1; REPEAT xlSheet := xlBook.Worksheets.Item(i); xlSheet.Range('A1').Value:= 'Format'; i:= i +1; UNTIL i > 5; bye André
Andre - You know I’m searching when I’m checking this forum on the weekend from home to see if there’s a solution. Thanks for your reply - and I will try it first thing Monday a.m. On first view - it may be the difference in having the Item(i) property on the worksheet in the book rather than on a sheets group. I’ll get back with you as to how I fair Monday. THANKS AGAIN for your help!
Andre - you win the prize [^] my friend! It worked like a charm[:D]!!! For those who will read this one day searching like I did, here’s what I ended up with (with a few other things I had to search for or figure out - like deleting a sheet, and formating an entire column). Globals: Name DataType Subtype xlApp Automation ‘Microsoft Excel 8.0 Object Library’.Application xlBook Automation ‘Microsoft Excel 8.0 Object Library’.Workbook xlSheetCP Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlSheetCE Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlSheetIC Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlSheet Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlSheetMC Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet i Integer x Integer CODE: // OPEN THE EXCEL APPLICATION CREATE(xlApp); // ALLOW IT TO BE VISIBLE xlApp.Visible(TRUE); // CREATE A WORKBOOK WITH ONLY ONE SHEET (WORKBOOK CANNOT BE CREATED EMPTY) xlApp.Workbooks.Add(1); // MAKE THE WORKBOOK ACTIVE xlBook := xlApp.ActiveWorkbook; // CREATE THE WORKSHEETS (4 OF THEM) WITHIN THE WORKBOOK, AND NAME EACH ONE ON IT’S WORKSHEET TAB xlSheetCP := xlBook.Worksheets.Add; xlSheetCP.Name := ‘Customer Sales - Parts’; xlSheetIC := xlBook.Worksheets.Add; xlSheetIC.Name := ‘Inter Company’; xlSheetCE := xlBook.Worksheets.Add; xlSheetCE.Name := ‘Customer Sales - Equipment’; xlSheetMC := xlBook.Worksheets.Add; xlSheetMC.Name := ‘Major Code 99’; // Remove the ‘original’ sheet named ‘Sheet1’ - keeping only MY sheets xlApp.DisplayAlerts(FALSE); xlSheet := xlBook.Worksheets.Item(‘Sheet1’); xlSheet.Delete; // CALL FUNCTION TO SETUP SHEETS SheetSetup; SheetSetup function: // SET THE COUNTER TO BEGIN AT THE FIRST SHEET, AND END AT LAST i := 1; x := (xlBook.Worksheets.Count); // LOOP THROUGH EACH SHEET - OUTPUTTTING COLUMN HEADINGS & FORMATTING REPEAT xlSheet := xlBook.Worksheets.Item(i); xlSheet.Range(‘A3’).Value := ‘Item’; xlSheet.Range(‘B3’).Value := ‘Description’; xlSheet.Range(‘C3’).Value := ‘Amount’; xlSheet.Range(‘C:C’).Columns.NumberFormat := ‘#,##0.00’; //…AND SO ON AND SO ON i := i + 1; UNTIL i > x;
Hi Fathie
quote:
Originally posted by faithiejlewis
Andre - you win the prize [^] my friend! It worked like a charm[:D]!!! …
Nice to hear (read) that [:)]. Welcome in the Navision-Excel-Automation world. As you may noticed in other threads I love the connection between Navision and Excel [8D]. So you are able to make things which you can’t do with Navision alone. bye André