Champak Posted February 12, 2022 Share Posted February 12, 2022 (edited) 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 February 12, 2022 by Champak Link to comment Share on other sites More sharing options...
water Posted February 13, 2022 Share Posted February 13, 2022 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Champak Posted February 15, 2022 Author Share Posted February 15, 2022 You will need the chrome UDF to actually run this, but this is the entire function with the variables changed around a little expandcollapse popup#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 More sharing options...
water Posted February 15, 2022 Share Posted February 15, 2022 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Champak Posted February 15, 2022 Author Share Posted February 15, 2022 (edited) 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. Edited February 15, 2022 by Champak Link to comment Share on other sites More sharing options...
water Posted February 15, 2022 Share Posted February 15, 2022 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now