Excel changelinks

[Fraser]

Good Afternoon,

I'm having a few issues with changing source links in excel, my problem appears to be with the activeworkbook.changelink function.

I have some code which is reading the links, unprotecting all of the sheets, then modifying the link and putting it back into the document (below)

$aLinks = $oExcelWB.LinkSources($xlExcelLinks)
For $oSheet In $oExcel.ActiveWorkbook.Worksheets
    $oSheet.UnProtect("")
Next
For $link In $aLinks
$oldlink = $link
$link = StringReplace($link, "\TEST\", "\TEST2\", 1, 0)
$oExcelWB.ChangeLink('"' & $oldlink & '"', '"' & $link & '"', '1')
Next

However I'm getting an error with the command and obviously the link isn't being changed, has anyone come across this or have any ideas?

Thanks,

Fraser

[SmOke_N]

What error?

I don't see any debugging, are you sure the link data is correct and the replace happened correctly?

[Fraser]

I'm capturing the error for the changelink function which is returned as: -2147352567. The above code is a basic version of the full code however it is only doing a few more checks of the file path.

The links aren't being changed at all, after reading about the above error code a lot of people suggest it is related to the worksheets being protected however I have checked and they are being unprotected correctly.

The link data is defiantly correct as this command if entered into a VBA project in excel works fine. 

[SmOke_N]

The little I read up on the changelink function, just for giggles, can't really see it changing, but if it's literal, it shows you passing decimal 49 instead of 1... if you change:

$oExcelWB.ChangeLink('"' & $oldlink & '"', '"' & $link & '"', '1')

To:

$oExcelWB.ChangeLink('"' & $oldlink & '"', '"' & $link & '"', 1)

Is there any change?

[water]

Could you please add the following code at the top of your script so we get more detailed error information?

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

[Fraser]

The little I read up on the changelink function, just for giggles, can't really see it changing, but if it's literal, it shows you passing decimal 49 instead of 1... if you change:

$oExcelWB.ChangeLink('"' & $oldlink & '"', '"' & $link & '"', '1')

To:

$oExcelWB.ChangeLink('"' & $oldlink & '"', '"' & $link & '"', 1)

Is there any change?

 

Thanks for the suggestion SmOke_N however this hasn't worked.

 

Could you please add the following code at the top of your script so we get more detailed error information?

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

 

Thanks for the suggestion water, the debug function doesn't return any results.

Here is my full code for information, I have tried a few different ways of calling the changelink option, like $oExcel.ActiveWorkbook.ChangeLink however this didn't appear to work.

expandcollapse popup

#Region
#AutoIt3Wrapper_Run_Tidy=y
#AutoIt3Wrapper_Tidy_Stop_OnError=n
#EndRegion

Opt("TrayIconHide", 1) ;0=show, 1=hide tray icon

#include <array.au3>
#include <file.au3>
#include <excel.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ProgressConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Debug.au3>
_DebugSetup("Check Excel", True)

Global $Button1, $Button2, $Label1, $Progress1, $oExcel, $strFileName, $with = 0, $without = 0
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc"), $Label4, $Label2, $Label3, $reportpath = @ScriptDir & "\Results\SS Excel Report.html"

Dim $Filename
Dim $xlsApp
Dim $objWorkbook
Dim $i
Dim $newSheet
Dim $docs

$strFileNamelinkinfo = @ScriptDir & "\all_links.csv"
$Filename = @ScriptDir & "\final list.txt"

createreport()

$Form1 = GUICreate("Excel Check", @DesktopWidth - 50, 140, -1, -1)
$Label1 = GUICtrlCreateLabel("Checking file: ", 10, 8, @DesktopWidth - 50 - 20, 40, $SS_CENTER)
$Label2 = GUICtrlCreateLabel("", 120, 80, @DesktopWidth - 50 - 240, 40, $SS_CENTER)
$Label3 = GUICtrlCreateLabel("", 120, 100, @DesktopWidth - 50 - 240, 20, $SS_CENTER)
$Label4 = GUICtrlCreateLabel("", 120, 120, @DesktopWidth - 50 - 240, 20, $SS_CENTER)
$Progress1 = GUICtrlCreateProgress(10, 56, @DesktopWidth - 50 - 20, 17)
$Button1 = GUICtrlCreateButton("Start", 10, 80, 97, 25)
$Button2 = GUICtrlCreateButton("Exit", @DesktopWidth - 50 - 10 - 99, 80, 99, 25)
GUISetState(@SW_SHOW)

$oExcel = _Excel_Open(True, False, True, -1, -1)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            ReadFile($Filename)
        Case $Button2
            Exit
    EndSwitch
WEnd

Func ReadFile($AFileName)
    _FileReadToArray($Filename, $docs)
    If IsArray($docs) Then
        $num_files = $docs[0]
        $i = 0
        $current = 1
        $left = $num_files
        _ArrayDelete($docs, 0)
        For $file In $docs
            $temp_num = 100 / $num_files
            $i = $i + $temp_num
            GUICtrlSetData($Progress1, $i)
            GUICtrlSetData($Label1, "Checking file " & $file & " for links")
            ListLinks($file)
            $left = $left - 1
            GUICtrlSetData($Label2, $left & " files left of " & $num_files)
            GUICtrlSetData($Label3, $current & " files checked")
            $current = $current + 1
        Next
        FileWrite($reportpath, "</tbody></table></body></html>")
    EndIf
EndFunc   ;==>ReadFile

Func ListLinks($test_file)
    Dim $aLinks
    Const $xlExcelLinks = 1

    $strFileName = $test_file
    $winwait1 = Run(@ScriptDir & "\new 3 win wait.exe")
    $oExcelWB = _Excel_BookOpen($oExcel, $strFileName, False, True, "modify2012", "modify2012")
    ProcessWait($winwait1, 10)
    If ProcessExists($winwait1) Then
;~      MsgBox(0, "test", "Load Win Exists")
        ProcessClose($winwait1)
    EndIf
    $aLinks = $oExcelWB.LinkSources($xlExcelLinks)
    For $oSheet In $oExcel.ActiveWorkbook.Worksheets
        ;   $oSheet.Protect("Test")     ; Protect
        $oSheet.UnProtect("")
    Next
;~  _ArrayDisplay($aLinks)
    MsgBox(0, "", "")

    If IsArray($aLinks) Then
        $dontadd = 0
        For $link In $aLinks
            If StringInStr($link, "C:\") Or StringInStr($link, "M:\Users\") Or StringInStr($link, "M:\Documents and Settings\") Or StringInStr($link, "http://") Or StringInStr($link, "https://") Or StringInStr($link, "\Appdata\") Or StringInStr($link, "TIFF") Or StringInStr($link, "\\MIS2328") Or StringInStr($link, "\\LTG-SR-001") Or StringInStr($link, "\\Dc01srv") Or StringInStr($link, "\\scceastfl5\") Or StringInStr($link, "\\sqh-sr-001") Then
                $dontadd = 1
                ExitLoop
            EndIf
        Next
        If $dontadd = 1 Then
            FileWriteLine(@ScriptDir & "\results\Docs with out links.txt", $strFileName)
            $without = $without + 1
        Else
            $htmllinks = ""
            $htmlstatus = ""
            $htmlstatuscolor = ""
            $htmltitle = ""
            For $link In $aLinks
                $oldlink = $link
                If StringInStr($link, "\\test.co.uk\sharedfiling\") Then
                    If FileExists(StringLeft($link, StringInStr($link, "\", 0, -1))) Then
                        If FileExists($link) Then
                            $htmlstatus = ""
                            $htmltitle = "The document link already exists and works."
                        Else
                            $htmlstatus = "There are errors with some links."
                            $htmltitle = "The folder the document is said to be in exists however the file doesn't exist."
                        EndIf
                    Else
                        $link = StringReplace($link, "\HR Shared Service Centre\DATA\", "\Shared Services\Allocation\DATA\", 1, 0)
                        $link = StringReplace($link, "\HR Shared Service Centre\Work Box - Tally Sheets\", "\Shared Services\Active Working Files\Tally Sheets\", 1, 0)
                        $link = StringReplace($link, "\HR Shared Service Centre\Allocation Documents 2011\", "\Shared Services\Allocation\", 1, 0)
                        If FileExists($link) Then
                        EndIf
                        $htmlstatus = "There are errors with some links."
                        $htmltitle = "The folder doesn't exist in the new location"
                        $oExcelWB.ChangeLink('"' & StringStripWS($oldlink, 3) & '"', '"' & StringStripWS($link, 3) & '"', 1)
                        If @error Then
                            MsgBox(16, "Error", "Error: " & @error & @CRLF & "Extended: " & @extended)
                        EndIf
                    EndIf
                Else
                    $htmlstatus = "There are errors with some links."
                    $htmltitle = "The link needs to be changed"
                EndIf
                If StringInStr($htmlstatus, "error", 0, 1) Then
                    $htmllinks = '<span style="color:red;" title="' & $htmltitle & '">' & $link & "</span><br>" & $htmllinks
                    $htmlstatuscolor = "red"
                Else
                    $htmlstatuscolor = "lightgreen"
                    $htmllinks &= '<span title="' & $htmltitle & '">' & $link & "</span><br>"
                EndIf
                FileWriteLine(@ScriptDir & "\results\all links.txt", $link)
            Next
            FileWrite($reportpath, '<tr><td id="changeme' & $with & '" onmouseover="changecolor(&#39;hover&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onmouseout="changecolor(&#39;off&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" style="width:20px;cursor:pointer;" onclick="showorhide(&#39;' & $with & '&#39;, &#39;changeme' & $with & '&#39;)">+</td>')
            FileWrite($reportpath, '<td id="link' & $with & '" onmouseover="changecolor(&#39;hover&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onmouseout="changecolor(&#39;off&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onclick="showorhide(&#39;links' & $with & '&#39;, &#39;changeme' & $with & '&#39;)" style="cursor:pointer;">' & $strFileName & '</td>')
            FileWrite($reportpath, '<td onmouseover="changecolor(&#39;hover&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onmouseout="changecolor(&#39;off&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onclick="showorhide(&#39;links' & $with & '&#39;, &#39;changeme' & $with & '&#39;)" style="background:' & $htmlstatuscolor & ';width:30px;cursor:pointer;" title="' & $htmlstatus & '"></td></tr>')
            FileWrite($reportpath, '<tr><td></td><td id="links' & $with & '" onmouseover="changecolor(&#39;hover&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" onmouseout="changecolor(&#39;off&#39;, &#39;changeme' & $with & '&#39;, &#39;link' & $with & '&#39;);" colspan="2" style="display:none;background:#CDCDCD;">' & $htmllinks & '</td></tr>')
            FileWriteLine(@ScriptDir & "\results\final list.txt", $strFileName)
            $with = $with + 1
        EndIf
    Else
        FileWriteLine(@ScriptDir & "\results\Docs with out links.txt", $strFileName)
        $without = $without + 1
    EndIf
    $changedlinks = $oExcelWB.LinkSources($xlExcelLinks)
    If IsArray($changedlinks) Then
        _ArrayDisplay($changedlinks)
    Else
        MsgBox(16, "Array", "Not an array")
    EndIf
    $winwait = Run(@ScriptDir & "\scc_winwait_close.exe")
    MsgBox(16, "", "Close")
    $oExcelWB.Close(False)
    ProcessWait($winwait, 20)
    If ProcessExists($winwait) Then
;~      MsgBox(0, "test", "Close Win Exists")
        ProcessClose($winwait)
    EndIf
    GUICtrlSetData($Label4, "Files witout links: " & $without & "    Files with links: " & $with)
EndFunc   ;==>ListLinks

Func createreport()
    If FileExists($reportpath) Then
        FileDelete($reportpath)
        FileWrite($reportpath, "<html><head><style>.expand {color:#FFF;padding:0px 5px;background:#426B8D;border 3px solid #cdcdcd;font-size: 18px;cursor: pointer;text-align:left;}")
        FileWrite($reportpath, @CRLF & ".expand:hover {color:#426B8D;padding:0px 5px;background:#CDCDCD;}.links {margin-left:40px;background:#CDCDCD;}")
        FileWrite($reportpath, "</style>" & @CRLF & '<script>function showorhide(elm, but) {e = document.getElementById(elm);b = document.getElementById(but);if (e.style.display == "none") {e.style.display = "block";b.innerHTML = "-";} else {e.style.display = "none";b.innerHTML = "+";}}')
        FileWrite($reportpath, @CRLF & 'function changecolor(type, elm1, elm2) {e1 = document.getElementById(elm1);e2 = document.getElementById(elm2);if (type == "hover") {e1.style.background = "#CDCDCD"; e2.style.background = "#CDCDCD";e1.style.color = "#000000"; e2.style.color = "#000000";} else {e1.style.background = "#426B8D"; e2.style.background = "#426B8D";e1.style.color = "#FFF"; e2.style.color = "#FFF";}}</script></head>' & @CRLF & "<body><center><h1>Shared Services - Excel Files with Document links</h1></center>")
        FileWrite($reportpath, '<table style="table-layout:fixed;width:100%;border-collapse: collapse;"><tbody>')
    Else
        FileWrite($reportpath, "<html><head><style>.expand {color:#FFF;padding:0px 5px;background:#426B8D;border 3px solid #cdcdcd;font-size: 18px;cursor: pointer;text-align:left;}")
        FileWrite($reportpath, @CRLF & ".expand:hover {color:#426B8D;padding:0px 5px;background:#CDCDCD;}.links {margin-left:40px;background:#CDCDCD;}")
        FileWrite($reportpath, "</style>" & @CRLF & '<script>function showorhide(elm, but) {e = document.getElementById(elm);b = document.getElementById(but);if (e.style.display == "none") {e.style.display = "block";b.innerHTML = "-";} else {e.style.display = "none";b.innerHTML = "+";}}')
        FileWrite($reportpath, @CRLF & 'function changecolor(type, elm1, elm2) {e1 = document.getElementById(elm1);e2 = document.getElementById(elm2);if (type == "hover") {e1.style.background = "#CDCDCD"; e2.style.background = "#CDCDCD";e1.style.color = "#000000"; e2.style.color = "#000000";} else {e1.style.background = "#426B8D"; e2.style.background = "#426B8D";e1.style.color = "#FFF"; e2.style.color = "#FFF";}}</script></head>' & @CRLF & "<body><center><h1>Shared Services - Excel Files with Document links</h1></center>")
        FileWrite($reportpath, '<table style="table-layout:fixed;width:100%;border-collapse: collapse;"><tbody>')
    EndIf
EndFunc   ;==>createreport

Edited November 20, 2014 by Fraser

[Fraser]

Afternoon,

Sorry to chase, has anyone got any ideas of how I could approach this?

Thanks,

Fraser

[water]

Error code -2147352567 (Hex: 0x80020009) is a COM error and means: General error.

So if you still get this error the debug lines I proposed should give more detailed error information.

[water]

I just noticed that line

_DebugCOMError()

is missing.

Could you please add this line and retry?