Sign in to follow this  
Followers 0
Fraser

Excel changelinks

9 posts in this topic

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

Share this post


Link to post
Share on other sites



What error?

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


Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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?


Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

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()

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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.

#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 by Fraser

Share this post


Link to post
Share on other sites

Afternoon,

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

Thanks,

Fraser

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I just noticed that line

_DebugCOMError()

is missing.

Could you please add this line and retry?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  
Followers 0