Jump to content
DigDeep

Read and Delete Excel Connections

Recommended Posts

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, 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

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

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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

@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

Glad you got it working :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

×
×
  • Create New...