Jump to content
  1. UEZ

    UEZ

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By robertocm
      change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:
      #include <File.au3> #include <WinAPIFiles.au3> ;Required 7-zip Global $Path7z = @ProgramFilesDir & "\7-Zip" If Not FileExists($Path7z & "\7z.exe") Then MsgBox(16, "", "7z.exe not found in path " & $Path7z) Exit EndIf Global $bFileOpen Global $sFileRead ;Global $sOldImg = "C:\Users\MyUserName\Documents\MyImageFolder" ;Global $sNewImg = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder" Global $sOldImg = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Global $sNewImg = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Global $sFileSelectFolder = FileSelectFolder("Directory to change excel image paths", "") Global $sTempDir = @ScriptDir & "\TempDir" Global $sFileCoreXml = $sTempDir & "\docProps\core.xml" If FileExists($sTempDir) Then DirRemove($sTempDir, $DIR_REMOVE) ;Look for excel files in selected directory and subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $aFileList[0] ;Discard some kind of temp files (locked files from antivirus?) If StringLeft($aFileList[$i], 1) = "~" Then ContinueLoop $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $Path7z & '\7z.exe" x -aoa -r -y "' & $aFileList[$i] & '" -o"' & $sTempDir & '"', $Path7z, @SW_HIDE) If FileExists($sFileCoreXml) Then $sFileRead = FileRead($sFileCoreXml) If __ReplaceImagePaths($sTempDir, $sOldImg, $sNewImg) = 1 Then Consolewrite("--> Image path replaced in file: " & $aFileList[$i] & @CRLF) EndIf ;Help File 7-zip.chm 'Switch can be used in any place in command line' RunWait('"' & $Path7z & '\7z.exe" a -r -tzip -y "' & $aFileList[$i] & '" "' & $sTempDir & '\*"', $Path7z, @SW_HIDE) Else If FileExists($sTempDir & "\EncryptedPackage") Then Consolewrite("Error password protected file: " & $aFileList[$i] & @CRLF) EndIf DirRemove($sTempDir, $DIR_REMOVE) Else Consolewrite("Error Locked file: " & $aFileList[$i] & @CRLF) EndIf Next Else MsgBox(16, "Error", "No excel files were found in the folder") EndIf Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileListDrw = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileListDrw[0] $iRetval = _ReplaceStringInFile($aFileListDrw[$i], $sFind, $sReplace) Next If Not $iRetval = -1 Then Return 1 EndFunc  
      Some references:
      https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp EDITED:
      Note: it seems that if User Account Control (UAC) is enabled then 7zip is unable to overwrite the destination file (using the same name).
      In this case, a possible solution would be to rename the original excel file before (see _PathSplit in help file).
      In my case i prefer just to disable UAC
    • By Hermes
      My _Excel_RangeCopyPaste is not working as intended. What I am trying to accomplish is copy the range B:E using _Excel_RangeCopyPaste in the first row and repeat the same for row 2 and so on.
      ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next Here's the full code:
      #Include <Chrome.au3> #Include <wd_core.au3> #Include <wd_helper.au3> #Include <WinHttp.au3> #include <MsgBoxConstants.au3> #include <IE.au3> #include <Array.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "test.html") Local $oAppl = _Excel_Open() Local $sWorkbook = "test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $oRange = $oWorkbook.ActiveSheet.Range("B:E") ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') Return '{"capabilities":{"alwaysMatch":{"goog:chromeOptions":{"w3c":true,' & _ '"excludeSwitches":["enable-automation"],"useAutomationExtension":false}}}}' EndFunc ;==>SetupChrome For the first row I am trying to copy just B:E with the following info
      Apple Banana Orange Mango and then repeat for row 2, row 3, etc. I've attached the spreadhseet.
      test.xlsxI have also attached the excel file for reference.
    • By Rskm
      Hi, My autoit program generates excel output file.  How do i set the author name for this excel file. thanks
    • By Hermes
      I have an html table that displays data along with an excel spreadsheet that has the same data as the html table. I am wanting to only match the Title column in my html table with the Title column in my Excel spreadsheet. If the titles match, click on the Edit hyperlink and continue to loop to next row. The issue I'm experience is its not matching correctly. So far  i've written the codes below:
      <table border="1" class="test"> <tr> <th> UniqueID</th> <th> Title</th> <th> UserID</th> <th> Address</th> <th> Gender </th> </tr> <tr> <td> 1 </td> <td> Title1 </td> <td> 12345 </td> <td> Manila </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5"> <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 2 </td> <td> Title2 </td> <td> 67891 </td> <td> Valenzuela </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 3 </td> <td> Title3 </td> <td> 88888 </td> <td> Ohio </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 4 </td> <td> Title4 </td> <td> 77777 </td> <td> California </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 5 </td> <td> Title5 </td> <td> 33333 </td> <td> Arizona </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> </table> #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "Excel.au3" #Include "_HtmlTable2Array.au3" #Include "Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "index.html") Sleep(6000) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx") ; Get the table element $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']") ; Retrieve HTML $sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML") ;Local $aTable = _HtmlTableGetWriteToArray($sHTML) Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) Local $aArray2 = _HtmlTableGetWriteToArray($sHTML) ;_ArrayDisplay($aArray1) ;_ArrayDisplay($aArray2) For $i = UBound($aArray1) - 1 To 0 step - 1 For $j = UBound($aArray2) - 1 to 0 step - 1 If $aArray1[$i][1] == $aArray2[$j][1] Then _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") _WD_ElementAction($sSession, $test1, 'click') ;_ArrayDisplay($aArray1) ;_ArrayDelete($aArray1 , $i) ;exitloop EndIf Next Next _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome Would appreciate if anyone can provide tips, or point me in the right direction in doing it.
       
      test.xlsx
    • By AnonymousX
      Hello,
      I'm struggling and not sure what I'm missing with how to use the _Excel_RangeRead function.
      Based on: https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm
      The second parameter should allow me to select different sheet names, however I can't seem to get it to work.
      I was thinking something like this (see below) would be all I need to get an array for the sheet named as "test sheet". 
      $array = _Excel_RangeRead($oWorkbook, "test sheet")  
      Can someone please help tell me what I'm missing here?
       
      Thank you,
×
×
  • Create New...