Jump to content

_Excel_PictureAdd issue


Champak
 Share

Recommended Posts

I'm using two sheets in an excel file and on the 2nd sheet I'm adding images using the following in a loop:

        _Excel_PictureAdd($oWorkbook, "Barcode Print", $BarcodesDir & $Code & ".png", $Col1 & $l + 1)

The problem is when sheet 1 is visible or excel is minimized and the script is running, the images are not placed exactly where the should be on the cell as the loop progresses with the image additions. As the script continues, the images are inched up slightly all the way down the sheet, basically offsetting the image a couple MM vertically as the loop continues down. But when sheet 2 is active (the sheet that the images are being added to) and visible the images are added exactly where they are supposed to be on every cell throughout the entire loop. Is this issue known and accepted, basically I have to be on whatever sheet the script is working on when it involves adding images?

Hopefully someone has seen this before or knows about it and can answer, if not, I will try to provide a stripped down version of what is happening.

 

 

Edited by Champak
Link to comment
Share on other sites

  • Champak changed the title to _Excel_PictureAdd issue

A stripped down reproducer script would be fine.
It's hard to tell with just a single line of code 😉 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

You will need the chrome UDF to actually run this, but this is the entire function with the variables changed around a little

#Include <WindowsConstants.au3>
#Include <WinAPIEx.au3>
#Include "RDC.au3"
#include <Excel.au3>
#include "wd_helper.au3"
#include "wd_capabilities.au3"
#include <ColorConstants.au3>


Global $sDesiredCapabilities = Call(SetupChrome)
Global $sWorkbook = "\ProductImport.xlsx"
Global $BarcodesDir = "\Barcodes\"

Global $Col1
Global $aColorTable[17][2] = [["White", "0xffffff"], ["Black", "0x000000"], ["Silver", "0xcccccc"], ["Grey", "0xcccccc"], ["Red", "0x0000ff"], ["Blue", "0xff0000"], ["Green", "0x33ff33"], ["Yellow", "0x00fbff"], ["Orange", "0x0088ff"], ["Purple", "0xac39ac"], ["Pink", "0xff80ff"], ["Brown", "0x336699"], ["Beige", "0xc6d8eb"], ["Cream", "0xccebff"], ["Burgundy", "0x200080"], ["Multi", ""]]
Global $URL_Start = "http://generator.onbarcode.com/linear.aspx?TYPE=4&DATA="
;ATTENTION: "&Y=55&" is the height of the overal image
Global $URL_End = "&UOM=0&X=1&Y=52&LEFT-MARGIN=0&RIGHT-MARGIN=0&TOP-MARGIN=0&BOTTOM-MARGIN=0&RESOLUTION=72&ROTATE=0&BARCODE-WIDTH=0&BARCODE-HEIGHT=0&SHOW-TEXT=true&TEXT-FONT=Arial%7c9%7cRegular&TextMargin=6&FORMAT=gif&ADD-CHECK-SUM=false&I=1.0&N=2.0&SHOW-START-STOP-IN-TEXT=true&PROCESS-TILDE=false"
Global $Image1 = "U", $Image2 = "V", $Image3 = "W", $Image4 = "X", $Image5 = "Y"



_WD_Startup()
    If @error <> $_WD_ERROR_Success Then MsgBox(0,9,@error)
    $sSession = _WD_CreateSession($sDesiredCapabilities)


    Local $oHPageBreaks, $oHPageBreak, $aPageBreaks[0]


    $oExcel = _Excel_Open()
    ;$oExcel = _Excel_Open(True, False, False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf


    ; Attach to the first Workbook where the file path matches
    $oWorkbook = _Excel_BookAttach($sWorkbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 1", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;---MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 1", "Search by 'filepath':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of cell B3: " & $oWorkbook.Activesheet.Range("B3").Value)



    Local $aArray1D[0]
    ;$RecentBarcode = RegRead("HKEY_CURRENT_USER\Software\ChristopherCiara", "RecentBarcode")
    $RecentBarcode = 1
    If $RecentBarcode = "" Then $RecentBarcode = 0
    ;ConsoleWrite("! " & $RecentBarcode & @CRLF)
    $BarCodeCounter = 1
    ;$Counter = GUICtrlRead($Barcodes);1
    $Counter = 1
    $PageCount = 1
    ;$BarcodeMax = $RecentBarcode + GUICtrlRead($BarcodesCount) + 4;The "+4" is to compensate for the rows that are skipped for the option entries on sheet 1 in the excel file
    $BarcodeMax = $RecentBarcode + 40 + 4;The "+4" is to compensate for the rows that are skipped for the option entries on sheet 1 in the excel file

    $OptionRow = False
    $l = 1
    $o = 0
    $Col1 = "A"
    $Col2 = "C"
    For $i = $RecentBarcode To $BarcodeMax
        ;ConsoleWrite("+ " & $i & @CRLF)

        If $OptionRow = True Then
            If $o = 4 Then
            ;   ConsoleWrite("> " & "Setting False" & @CRLF)
                $OptionRow = False
                $o = 0
                $i -= 4
            Else
                $o += 1
                $Counter += 1
            ;   ConsoleWrite("! " & "Skipping" & @CRLF)
                ContinueLoop
            EndIf
        EndIf

        $OptionRow = True
        ;ConsoleWrite("+ " & " Not Skipping" & @CRLF)


        If $BarCodeCounter = 11 And $Col1 = "A" Then
            $BarCodeCounter = 1
            $Col1 = "E"
            $Col2 = "G"
            $l = $PageCount
        EndIf

        If $BarCodeCounter = 11 And $Col1 = "E" Then
            $BarCodeCounter = 1
            $Col1 = "I"
            $Col2 = "K"
            $l = $PageCount
        EndIf

        If $BarCodeCounter = 11 And $Col1 = "I" Then
            $BarCodeCounter = 1
            $Col1 = "A"
            $Col2 = "C"
            ;$Counter = $Counter + $l
            $PageCount = $l
        EndIf

        ;ConsoleWrite("> " & $Col1 & $l & @CRLF & "! " & $Counter & @CRLF)
        ;$Code = "A" & StringFormat("%07i", $i) & "-" & GUICtrlRead($BarcodesExtI)
        $Code = "A" & StringFormat("%07i", $i) & "-A"

        _WD_Navigate($sSession, $URL_Start & $Code & $URL_End)


        ; Locate a single element
        $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByTagName, "img")

        $sResponse = _WD_ElementAction($sSession, $sElement, 'screenshot')
        $bDecode = __WD_Base64Decode($sResponse)

        $hFileOpen = FileOpen($BarcodesDir & $Code & ".png", $FO_BINARY + $FO_OVERWRITE)
        FileWrite($hFileOpen, $bDecode)
        FileClose($hFileOpen)
    ;   ConsoleWrite($BarcodesDir & $Code & ".png" & @CR)

        ;ConsoleWrite(StringFormat("%012i", $i) & @CRLF)
        _ArrayAdd($aArray1D, "A" & StringFormat("%07i", $i) & "-A")
        ;Sleep(1500)
        $k = $l + 6

        $oRange = $oWorkbook.Worksheets(1).Range("B" & $Counter & ":AR" & $Counter)
    ;   $oRange = $oWorkbook.Worksheets(1).Range("B" & $Counter + 1 & ":AR" & $Counter + 1)
        ;Designer / Gender / Size / Price
        $Info = $oRange(2).text & "/" & StringLeft($oRange(13).text, 1) & "/" & $oRange(12).text & "/$" & $oRange(4).text
    ;   ConsoleWrite("--- " & $Counter + 1)
    ;   ConsoleWrite("- " & $Info & @CRLF)
        _Excel_RangeWrite($oWorkbook, "BarCode Print", $Info, $Col1 & $l)
        $sColor = _ArraySearch($aColorTable, $oRange(11).text)
    ;   ConsoleWrite("+ 7 " & $oRange(11).text & "    " & $sColor & @CRLF)

        If $sColor = "-1" Then $sColor = 0
        ;ConsoleWrite("+ " & $aColorTable[$sColor][1] & @CRLF)

        If $oRange(11).text = "Multi" Then
            $oWorkbook.Worksheets("Barcode Print").Range($Col2 & $l + 1 & ":" & $Col2 & $l + 1).Interior.Color = Number($aColorTable[6][1])
            $oWorkbook.Worksheets("Barcode Print").Range($Col2 & $l + 2 & ":" & $Col2 & $l + 2).Interior.Color = Number($aColorTable[4][1])
            $oWorkbook.Worksheets("Barcode Print").Range($Col2 & $l + 3 & ":" & $Col2 & $l + 3).Interior.Color = Number($aColorTable[5][1])
        Else
            $oWorkbook.Worksheets("Barcode Print").Range($Col2 & $l + 1 & ":" & $Col2 & $l + 3).Interior.Color = Number($aColorTable[$sColor][1])
        EndIf

        _Excel_PictureAdd($oWorkbook, "Barcode Print", $BarcodesDir & $Code & ".png", $Col1 & $l + 1)

        ConsoleWrite("> " & $Col1 & $l + 1 & @CRLF)
        $l += 4
        $Counter += 1
        $BarCodeCounter += 1

        $ProgressSet = Round(100 / ($BarcodeMax / $i)+ 4)
        ;GUICtrlSetData($BarcodeProgress, $ProgressSet)

    Next

    RegWrite("HKEY_CURRENT_USER\Software\ChristopherCiara", "RecentBarcode", "REG_SZ", $i + 4)

    $ProductIndex = 1;GUICtrlRead($Barcodes)

    For $Z = 0 to UBound($aArray1D) - 1

        _Excel_RangeWrite($oWorkbook, $oWorkbook.Worksheets("Products"), $aArray1D[$Z], "A" & $ProductIndex)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

        $ProductIndex += 5

    Next

    MsgBox($MB_SYSTEMMODAL, "Complete", "Barcodes successfully generated.")


Func SetupChrome()
    _WD_Option('Driver', 'chromedriver.exe')
    _WD_Option('Port', 9515)
    _WD_Option('DriverParams', '--verbose --log-path="' & @ScriptDir & '\chrome.log"')
    _WD_CapabilitiesStartup()
    _WD_CapabilitiesAdd('alwaysMatch', 'chrome')
    _WD_CapabilitiesAdd('w3c', True)
    _WD_CapabilitiesAdd('args', '--headless')
    _WD_CapabilitiesAdd('excludeSwitches', 'enable-automation')
    _WD_CapabilitiesDump(@ScriptLineNumber) ; dump current Capabilities setting to console - only for testing in this demo
    Local $sDesiredCapabilities = _WD_CapabilitiesGet()
    Return $sDesiredCapabilities
EndFunc   ;==>SetupChrome

 

Also this the following isn't working for me:

$oExcel = _Excel_Open(True, False, False)

When parameter 3 is set to False the last part of this function when it switches to "Products" sheet fails to run. Even when I set screenupdating back to True that loop fails.

Thanks

P.S. If you see a way to speed this up, please feel free 😉

Link to comment
Share on other sites

  • Drop _Excel_BookAttach. You either use _Excel_BookOpen or _Excel_BookAttach. Not both!
  • Calling _ArrayAdd in a loop is a time consuming idea. Define the array larger than needed and shrink it after the loop to the size needed.

This example works just fine for me. Can you please test this script and tell me what you get (Screenshot)?

#include <Excel.au3>

Global $oExcel, $oWorkbook, $sPicture = "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel.jpg"
$oExcel = _Excel_Open(Default, Default, False) ; Stop Screenupdating
$oWorkbook = _Excel_BookNew($oExcel, 1)
_Excel_SheetAdd($oWorkbook, 1, False, 1, "Barcode Print")

$oWorkbook.Sheets(1).Activate ; Activate Sheet 1
_Excel_PictureAdd($oWorkbook, "Barcode Print", $sPicture, "A1")
_Excel_PictureAdd($oWorkbook, "Barcode Print", $sPicture, "B9")

$oWorkbook.Sheets(2).Activate ; Activate Sheet 2
_Excel_PictureAdd($oWorkbook, "Barcode Print", $sPicture, "C17")
_Excel_PictureAdd($oWorkbook, "Barcode Print", $sPicture, "D25")

$oExcel.ScreenUpdating = True ; Restart Screenupdating

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

OK, this is done with the same example script. A1 and A2 are done with the barcode sheet active while running the script. B1 and B2 are done with a different sheet active while running the script. If you notice the red line drawn on the top and bottom left hand side, you see where the top of the barcode meets up with the black box. With the "A" examples there is no offset, with the "B" examples it alignment goes off. And as more pages are done, it gets worse. I'll test out what you provided and let you know.

Screenshot A1.jpg

Screenshot A2.jpg

Screenshot B1.jpg

Screenshot B2.jpg

Edited by Champak
Link to comment
Share on other sites

Can you please send me this Excel file by PM?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...