Hi all! After long years of avoiding this, now I have to create Excel-Files via Automation [xx(] Thanx to Marcus Fabian (and some others, too) it’s basically not a problem (http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=544)
But …- … how can I insert pictures which are stored in the DB (Item.Picture)
Hi Joerg! Fret not - creating Excel files from Navision is the greatest stuff since the invention of the thumbnail [:p] As a general guideline, you should first perform all tasks in Excel while recording, then examine the VB code that has been generated. This works especially well when formatting cells, since the required format expressions can be quite complicated and non-intuitive. All that remains to do is translating the VB code into C/AL Automation calls. Several things need to be considered - optional parameters aren’t optional any longer (you can omit unnecessary trailing parameters, though), and named parameters can not be used. Constants used as parameters may cause errors, so I recommend using dummy variables of the appropriate type. Using cascaded “.” operations (e.g. foo.bar.baz.method1) has also caused me troubles on some occasions, so I recommend using assignments to intermediate objects. Inserting pictures works by exporting the pictures from Navision to some temporary directory, then importing them into Excel using the appropriate Automation calls. I haven’t done this myself yet, but there was a thread on this topic (along with some problems that showed up [;)]) a few weeks ago. Formatting cells is easy - just record the formatting process in Excel, then use the format string that was generated. Watch out, however - Automation calls use format strings in your local language! Where VB generates “Red” for red numbers, you will have to use “Rot” in C/AL! [:0] By “fixing” the window, you mean fixing lines and columns inside Excel, I suppose, and not the entire application window, right? Once again, record the entire operation in Excel, then translate to C/AL. Feel free to ask any more specific questions if the need arises [8D] Good luck. [:D]
Hello Heinz! Thanx a lot! Good hint! Well, now I “tasted blood” … Excel-Automation is really cool [8D] Ok, her we go: - Formating cells … done - Freezing Panes … done But: - still no idea of importing pictures (well, not from a DB-BLOB but from a JPG) - still fiddling with “Auto Fit” and “Auto Filter” Any solutions? [:p] Thanx and Best Reagrds, Jörg
you can insert picture in excel sheet , i have already replied of this query , you can check some other topics.
[:D] Thanx Ajay! Found your reply and inserted that code:
xlWorksheet.Shapes.AddPicture(<picture.jpg>,1,1,1,1,200,50);
Works fine! But … How can I set the position of that image? I found out how to set the size (in my example: width 200, height 50) but if I change the other parameters, NAVISION crashes with “Error 45 in Module 10” [xx(] What’s wrong? Regards, Jörg P.S.: “AutoFilter” is done, don’t need “AutoFit” (set “ColumWidth”) …
Jörg, try assigning the parameter values to dummy variables of the appropriate type and call the method with the variables instead of with the constants. There seems to be a problem with the conversion of C/AL constants to COM data types. I had this problem on some (not all!) occasions, too, and have made it a habit to avoid passing constant values to COM methods. Haven’t had any problems ever since.
the best way to find out all these automation stuff is using macros in MS Word/Excel, record a macro and start what u want stop and edit that macro see the code and use same in navision like for resize the picture Selection.ShapeRange.ScaleWidth Selection.ShapeRange.ScaleHeight and for moving the picture Selection.ShapeRange.IncrementLeft Selection.ShapeRange.IncrementTop
Hi again! Thanx again, I found the errors … [;)] I did it that way:
EVALUATE(PosX, FORMAT(xlRange.Left));
EVALUATE(PosY, FORMAT(xlRange.Top));
xlWorksheet.Shapes.AddPicture(<picture>,1,1,PosX,PosY,200,50);
So finally, I’m close to finish my first “Excel-Automation-Project” [^] Thanx a lot! Best Regards, Jörg
[:p] Final(?) question … I’m adding pictures this way:
IF EXISTS(STRSUBSTNO('%1%2.jpg', LagerEinr.Bilderverzeichnis, "Nr.")) THEN BEGIN
xlRange := xlWorksheet.Range(xlsCell(10,Zeile));
xlRange.RowHeight := 60;
EVALUATE(PosX, FORMAT(xlRange.Left));
EVALUATE(PosY, FORMAT(xlRange.Top));
xlWorksheet.Shapes.AddPicture(STRSUBSTNO('%1%2.jpg', LagerEinr.Bilderverzeichnis, "Nr."),
1,1,PosX + 5,PosY + 5,200,50);
END ELSE BEGIN
xlsCellValue(10, Zeile, 'Kein Bild vorhanden');
xlsCellFormat(10, Zeile, '@', 'Arial', 8, 0, FALSE, TRUE, FALSE);
END;
Now, I would like to make this Picture depending on the cell size, so if one is changing the Rowheight or ColumnWidth the picture resizes with it. I recorded this macro as an example (“Grafik formatieren - Eigenschaften - Objektpositionierung: von Zellposition und -größe abhängig”):
ActiveSheet.Shapes("Picture 10").Select
With Selection
.Placement = xlMoveAndSize
.PrintObject = True
End With
But … I’m not able to transform this code to C/Al, I tried a lot, but it just won’t work [:(!] Can anyone help? Thanx & best regards, Jörg
Hi Joerg, without testing it:
Name DataType Subtype Length
xlShape Automation 'Microsoft Excel 9.0 ObjectLibrary'.Shape
xlShapes Automation 'Microsoft Excel 9.0 Object Library'.Shapes
xlShapes := xlWorksheet.Shapes;
**NEW**
i := xlShapes.Count;
**/NEW**
xlShape := xlShapes(**i**).Select; // find allways the last
xlShape.Placement(1) // xlMoveAndSize
Andre
[:I] Hi all! Thank you Andre! You know that? You’re fiddling on some code for days, then - frustrated - you ask for help … and only seconds later you find the solution … !? Strange, … [8] I did it like Andre suggested:
...
xlShape := xlWorksheet.Shapes.AddPicture(STRSUBSTNO('%1%2.jpg', LagerEinr.Bilderverzeichnis, "Nr."),1,1,PosX + 5,PosY + 5,200,50);
xlShape.Placement(1);
...
The element xlShape was missing, so I had no chance to set the correct Placement … [:I] Jörg
Hi, try one step behind the other. First add the picture and then try to select this pic with my above code. If the selection works the placement should works too. Perhaps I can test it tomorrow. Andre
Joerg, here is your solution [8D]:
xlShape:= xlSheet.Shapes.AddPicture(YourPic,x,x,x,x,x,x);
xlShape.Select;
xlShape.Placement(1);
Please forget the xlShapes! bye Andre