Jump to content
DigDeep

Read and Delete Excel Connections

Recommended Posts

DigDeep

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

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

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

@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

nm

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
DigDeep

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

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)

 

  • Like 1

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
DigDeep

@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

Glad you got it working :)

  • Like 1

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
Earthshine

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

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

  • Like 1

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.