ExcelCOM - _ExcelPictureInsert

[b0ony]

Hi @ all,

I have a question about ExcelCOM_UDF.au3

the function _ExcelPictureInsert(....) works fine on a existing excelsheet without any problems,

but not on a created spreadsheet

expandcollapse popup

#include <GUIConstants.au3>
;#include <ExcelCOM_UDF.au3>

;edit
$sPic_FilePath = @ScriptDir & "\test.gif"

;===============================================================================
Dim $oMyError
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
;===============================================================================
; spreadsheet create
If RegRead("HKCR\OWC9.spreadsheet", "") Then $oExcel = ObjCreate("OWC9.spreadsheet")
If RegRead("HKCR\OWC10.spreadsheet", "") Then $oExcel = ObjCreate("OWC10.spreadsheet")
If RegRead("HKCR\OWC11.spreadsheet", "") Then $oExcel = ObjCreate("OWC11.spreadsheet")
; spreadsheet options
If IsObj($oExcel) Then
    With $oExcel
        .AllowPropertyToolbox = True
        .DisplayOfficeLogo = False
        .DisplayPropertyToolbox = False
        .DisplayTitleBar = False
        .DisplayToolbar = False
        ;.ViewOnlyMode = True
        ;.autofit = True
        ;.Maxwidth = 1000
        ;.Maxheight = 1000
    EndWith

    With $oExcel.ActiveWindow
        .DisplayGridlines = True
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayColumnHeadings = True
        .DisplayRowHeadings = True
        .DisplayWorkbookTabs = False
        .EnableResize = False
    EndWith
EndIf
 ;===============================================================================  
; gui
GuiCreate("spreadsheet test", 790, 590, -1, -1, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)
$Load_btn = GUICtrlCreateButton("&Load Pic", 720, 20, 60, 30)
$GUI_ActiveX = GUICtrlCreateObj ($oExcel, 10, 10 , 700 , 500)


GuiSetState()
While 1
    $msg = GuiGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $Load_btn
            _insert_test_pic ()
    EndSelect
WEnd

Exit
;===============================================================================
Func _insert_test_pic ()
    
    _ExcelPictureInsert($oExcel, $sPic_FilePath, 50, 50, 18, 18, False, False)
    
EndFunc
;===============================================================================
;
; Description:      Insert a picture from a separate file into the active sheet.
; Syntax:           _ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sFilePath - The full path of the picture to be inserted
;                   $fLinkToFile - "True" to link the picture to the file from which it was created. 
;                                  "False" to make the picture an independent copy of the file. The default value is False.
;                   $fSaveWithDoc - "True" to save the linked picture with the document. The default value is False.
;                   $iLeft - The position (in points) of the upper-left corner of the picture relative to the upper-left corner of the worksheet
;                   $iTop - The position (in points) of the upper-left corner of the picture relative to the top of the worksheet
;                   $iWidth - The width of the picture, in points
;                   $iHeight - The height of the picture, in points
; Requirement(s):   None
; Return Value(s):  On Success - Returns an object representing the inserted picture
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $iLeft < 1 Then $iLeft = 1
    If $iTop < 1 Then $iTop = 1
    If $iWidth < 1 Then $iWidth = 1
    If $iHeight < 1 Then $iHeight = 1
    $oExcel.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight).Select
    Return $oExcel.Selection.ShapeRange
EndFunc ;==>_ExcelPictureInsert
;===============================================================================
;custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  ConsoleWrite("=======================================================" & @CRLF & _
             "AutoIt COM Error !"                                        & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber               & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext    & @CRLF _
            )
  SetError(1) ; to check for after this function returns
Endfunc



#cs
err.number is:  80020006
err.lastdllerror is:    0
err.scriptline is:  93
#ce

...Unfortunately it doesn't add this picture ...

Does anyone have an idea of how I can do what I'm trying-to?

Any advice would be fantastic!...

Thanks and regards,

b0ony

[Locodarwin]

The functions from my UDF are not meant to work with the Office Web Components (OWC). They are designed to work with the full application objects (i.e. Excel.Application). The OWC objects support only a subset of the methods and properties of the full Office suite objects.

To open a new workbook with my UDF, use this function:

_ExcelBookNew($fVisible = 1)

...like so:

#include <ExcelCOM_UDF.au3>
$oExcel = _ExcelBookNew(1)

; ...now use _ExcelPictureInsert()

Good luck with your Excel endeavors!

-S

Edited January 22, 2008 by Locodarwin