Jump to content
Sign in to follow this  
b0ony

ExcelCOM - _ExcelPictureInsert

Recommended Posts

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

#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

Share this post


Link to post
Share on other sites
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 by Locodarwin

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×