Macro to delete all exernal data connections

[hyperdreamz]

Hi All

I am trying to write a macro to delete all connections from a very large excel file

I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)

The issue is the 1000+ connections on this file make it next to impossible to operate

I cannot recreate the file

Need some help with a macro to delete all connections in all sheets in a given work book.

Sample :http://www.4shared.com/file/95922031/b7611f8f/Book1.html


Macro used :


Sub Dump()

Dim myURL

Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select

myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text

Sheets("Sheet2").Select
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myURL, _
Destination:=Range(ActiveCell.Address))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.PreserveFormatting = False
.SaveData = True

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
'.WebTables = "9"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
'.Refresh BackgroundQuery:=False

End With

Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
nName.Delete
End If
Next nName

End Sub


URL's on sheet1:

http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html
http://archive.corewebprogramming.com/Chapter2/Simple-Table.html

 

[smiths87]

Maybe this?

Sub deletedconns()

For i = 1 To ActiveWorkbook.Connections.Count
If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
ActiveWorkbook.Connections.Item(i).Delete
i = i - 1
Next i

End Sub

 

[hyperdreamz]

Hey smiths87

Thanks for the input. Your solution worked. But a friend reccomended this (PFB) approach that seemed to run faster.


Dim Sh As Worksheet, xNazwa As Object
Dim xConect As Object


For Each xConect In ActiveWorkbook.Connections
If UCase(xConect.Name) Like "*" Then xConect.Delete
Next xConect

For Each Sh In ActiveWorkbook.Worksheets
For Each xNazwa In Sh.Names
xNazwa.Delete
Next xNazwa
Next Sh

 

[gad79]

I use this one.

sub delete_Connections()
do while activeworkbook.connections.count > 0
activeworkbook.connections.item(activeworkbook.connections.count).delete
loop
end sub

 

[Mr Boris]

I use this one.

sub delete_Connections()
do while activeworkbook.connections.count > 0
activeworkbook.connections.item(activeworkbook.connections.count).delete
loop
end sub

A long time ago I know, but just what I was looking for!
Cheers
B

 

[Excelish08]

A long time ago I know, but just what I was looking for!
Cheers
B

I get the following error when I use this code : Run-time Error 5, Invalid procedure call or argument