Excel 2007 Open XML File, change linked image paths

[robertocm]

change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:

expandcollapse popup

#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

Edited April 2, 2021 by robertocm
amended code