robertocm

Excel 2007 Open XML File, change linked image paths

1 post in this topic

#1 ·  Posted

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

#include <File.au3>

;Change this
Local $sFind = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png"
Local $sReplace = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png"

Local Const $sMessage = "Directory to change excel image paths"
Local $sFileSelectFolder = FileSelectFolder($sMessage, "")
Local $sTempDir = @ScriptDir & "\testdir"

;Required 7-zip
Local $PathZipProgram = @ProgramFilesDir & "\7-Zip\"
If Not(FileExists($PathZipProgram & "\7z.exe")) Then
  MsgBox(16, "", "7z.exe not found in path " & $PathZipProgram)
  Exit
EndIf

;look for excel files in selected directory and all subdirectories
Local $SFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls.;*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
If Not @error Then
  For $i = 1 To $SFileList[0]
    DirRemove($sTempDir, 1)
    ;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('"' & $PathZipProgram & '7z.exe" x -aoa -r "' & $SFileList[$i] & '" -o"' & $sTempDir & '" -y', $PathZipProgram, @SW_HIDE)
    __ReplaceImagePaths($sTempDir, $sFind, $sReplace)
    RunWait('"' & $PathZipProgram & '7z.exe" a -r "' & $SFileList[$i] & '" "' & $sTempDir & '\*" -tzip -y', $PathZipProgram, @SW_HIDE)
  Next
Else
  MsgBox(16, "Error", "No files were found in the folder specified.")
EndIf

DirRemove($sTempDir, 1)

Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace)
   ;List all files with .xml.rels extension in the directory \xl\drawings\_rels
   Local $aFileList = _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 $aFileList[0]
      $iRetval = _ReplaceStringInFile($aFileList[$i], $sFind, $sReplace)
   Next
EndFunc

 

Some references:

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

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Written by GreenCan and water.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have a problem with the deletion of an empty row in Excel.
      My code:
       
      If $vRow_2 = "" Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf I want to delete the second row. $vRow_2 is an empty cell, "A2".
      After running the code, the second row is not deleted.
      I have tried also:
       
      If $vRow_2 = Null Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf But it doesn't work.
      Any suggestion?
      Thanks in advance.
    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?