DigDeep 1 Posted December 5, 2017 Is there a way to know the Connections Name and how to remove the Connections Name from Excel? Share this post Link to post Share on other sites
orbs 180 Posted December 5, 2017 MS Office scripting via COM (as done in AutoIt) pretty much correlates to VBA. so google "excel vba remove connections" yields enough simple VBA scripts that can be easily converted to AutoIt. this VBA snippet, for example: Sub removeconnections() Dim xConnect As Object For Each xConnect In ActiveWorkbook.Connections If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete Next xConnect End Sub Share this post Link to post Share on other sites
Earthshine 313 Posted December 5, 2017 You can put that VBA directly in excel why bother with autoit My resources are limited. You must ask the right questions Share this post Link to post Share on other sites
DigDeep 1 Posted December 5, 2017 @Earthshine, I am using AutoIT to export SharePoint site into Excel to work on the required task. In order to further make changes to the excel I want to remove the connections from the Excel pointing to the SharePoint site so the changes in Excel doesn't affect into SharePoint site. @orbs, thanks and I'll give it a try. will come back if getting stuck. Share this post Link to post Share on other sites
Earthshine 313 Posted December 5, 2017 (edited) nm Edited December 5, 2017 by Earthshine My resources are limited. You must ask the right questions Share this post Link to post Share on other sites
DigDeep 1 Posted December 6, 2017 This is giving me Unexpected error. In the meantime I was trying out to check if there is anyway to read the Data Connections name but I am not able to get that part inside the Excel UDF function. Checking if @water can help here too as I have seen some good excel examples from you too. Local $oExcel = _Excel_Open(False) If @error Then MsgBox(0, '', 'Error') EndIf Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePath & "\TestBook.xls") If $oWorkbook.ActiveSheet.Connect = "ConnetcionName" Then ; MsgBox(0, '', $oWorkbook.ActiveSheet.Connect) ; Display the Data Connection name for testing purpose ; Delete the Data Connection EndIf Sleep(3000) _Excel_Close($oExcel) Share this post Link to post Share on other sites
water 1,968 Posted December 6, 2017 You have to do it in a loop as the VBA example does: #include <Excel.au3> Global $sFilePath = "..." Global $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error returned by _Excel_Open. @error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath & "\TestBook.xls") If @error Then Exit MsgBox(0, "Error", "Error returned by _Excel_BookOpen. @error = " & @error & ", @extended = " & @extended) For $oConnection In $oWorkbook.Connections If $oConnection.Name = "ConnectionName" Then MsgBox(0, "Info", "Connection Name: " & $oConnection.Name) ; Display the Data Connection name for testing purpose $oConnection.Delete If @error Then Exit MsgBox(0, "Error", "Error returned when deleting connection " & $oConnection.Name & ". @error = " & @error & ", @extended = " & @extended) Else MsgBox(0, "Info", "Connection " & $oConnection.Name & " successfully deleted.") EndIf EndIf Next _Excel_Close($oExcel) 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - WikiiCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & SupportExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - Wiki Tutorials:ADO - Wiki Share this post Link to post Share on other sites
DigDeep 1 Posted December 6, 2017 @water, first of all thank you for getting this. This is running fine but $oConnection.Delete, gives the confirmation for successfully deleted but when I open the Excel, I can still see the Connection available. So I had made a little change to get the Connections deleted and then saving the excel as a new file. This works out. $oConnection.Delete If @error Then Exit MsgBox(0, "Error", "Error returned when deleting connection " & $oConnection.Name & ". @error = " & @error & ", @extended = " & @extended) Else MsgBox(0, "Info", "Connection successfully deleted.") EndIf ;~ EndIf Next _Excel_BookSaveAs($oWorkbook, $sFilePath & "\TestBookNew.xls") _Excel_Close($oExcel) Big help from all. Thanks again. Share this post Link to post Share on other sites
water 1,968 Posted December 6, 2017 Glad you got it working 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - WikiiCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & SupportExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - Wiki Tutorials:ADO - Wiki Share this post Link to post Share on other sites
Earthshine 313 Posted December 6, 2017 Water, your UDF is cool. I will probably be using it and production because our builds are based on Excel spreadsheet My resources are limited. You must ask the right questions Share this post Link to post Share on other sites
water 1,968 Posted December 6, 2017 Glad you like the UDF If there are any questions, just post and I will do my very best to assist. 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - WikiiCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & SupportExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - Wiki Tutorials:ADO - Wiki Share this post Link to post Share on other sites