Jump to content
Sign in to follow this  
robertocm

Excel 2007 Open XML File, change linked image paths

Recommended Posts

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:

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

Edited by robertocm
amended code

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • 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,
    • By goku200
      I'm having some issues with writing to column C when an element is found. It works on C2 but it does not continue to C3, C4, C5, etc..... I'm wanting to write "test" if the element //input[@id='username'] is found  $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']"). I have attached my HTML and Excel file along with my AutoIt code below:
      #Include "wd_core.au3" #Include "wd_helper.au3" #Include "wd_core.au3" #Include "File.au3" #Include "Array.au3" #Include "Excel.au3" Local $sDesiredCapabilities, $sSession _WD_Startup() $Ssession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://127.0.0.1/test.html") _WD_LoadWait($sSession) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\<Username>\Downloads\test.xlsx") Local $aArrayTest1 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $aArrayTest2 = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) For $i = 0 To UBound($aArrayTest1) - 1 _WD_Navigate($Ssession, $aArrayTest1[$i]) _WD_LoadWait($sSession) $someUser = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='username']") _WD_SetElementValue($sSession, $someUser, $aArrayTest2[$i]) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@type='submit'][@value='Submit']") _WD_ElementAction($sSession, $sElement, 'click') _WD_LoadWait($sSession) Sleep(5000) If $someUser Then Local $aArray2D[2] = ["test"] _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray2D, "C2") EndIf Next 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  
      test.html test.xlsx
×
×
  • Create New...