Fraser Posted November 18, 2014 Posted November 18, 2014 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
Moderators SmOke_N Posted November 18, 2014 Moderators Posted November 18, 2014 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.
Fraser Posted November 18, 2014 Author Posted November 18, 2014 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.
Moderators SmOke_N Posted November 18, 2014 Moderators Posted November 18, 2014 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.
water Posted November 18, 2014 Posted November 18, 2014 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Fraser Posted November 19, 2014 Author Posted November 19, 2014 (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. 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('hover', 'changeme' & $with & '', 'link' & $with & '');" onmouseout="changecolor('off', 'changeme' & $with & '', 'link' & $with & '');" style="width:20px;cursor:pointer;" onclick="showorhide('' & $with & '', 'changeme' & $with & '')">+</td>') FileWrite($reportpath, '<td id="link' & $with & '" onmouseover="changecolor('hover', 'changeme' & $with & '', 'link' & $with & '');" onmouseout="changecolor('off', 'changeme' & $with & '', 'link' & $with & '');" onclick="showorhide('links' & $with & '', 'changeme' & $with & '')" style="cursor:pointer;">' & $strFileName & '</td>') FileWrite($reportpath, '<td onmouseover="changecolor('hover', 'changeme' & $with & '', 'link' & $with & '');" onmouseout="changecolor('off', 'changeme' & $with & '', 'link' & $with & '');" onclick="showorhide('links' & $with & '', 'changeme' & $with & '')" style="background:' & $htmlstatuscolor & ';width:30px;cursor:pointer;" title="' & $htmlstatus & '"></td></tr>') FileWrite($reportpath, '<tr><td></td><td id="links' & $with & '" onmouseover="changecolor('hover', 'changeme' & $with & '', 'link' & $with & '');" onmouseout="changecolor('off', 'changeme' & $with & '', 'link' & $with & '');" 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 Posted November 21, 2014 Author Posted November 21, 2014 Afternoon, Sorry to chase, has anyone got any ideas of how I could approach this? Thanks, Fraser
water Posted November 21, 2014 Posted November 21, 2014 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
water Posted November 21, 2014 Posted November 21, 2014 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now