Read and Delete Excel Connections

[DigDeep 1]

Is there a way to know the Connections Name and how to remove the Connections Name from Excel?

[orbs 180]

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

 

 

[Earthshine 311]

You can put that VBA directly in excel why bother with autoit 

[DigDeep 1]

@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.

[Earthshine 311]

nm

Edited December 5, 2017 by Earthshine

[DigDeep 1]

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)

 

[water 1,968]

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)

 

[DigDeep 1]

@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.

[water 1,968]

Glad you got it working

[Earthshine 311]

Water, your UDF is cool.  I will probably be using it and production because our builds are based on  Excel spreadsheet

[water 1,968]

Glad you like the UDF  
If there are any questions, just post and I will do my very best to assist.