vba to remove connections

[tlindeman]

Could you please supply me with the code to remove connections to a web site? Here is my code, however it creates multiple connections and I would like to delete them after they get created. Thank You

Sub Income_statement()
Sheets("Income Statement").Select
For i = 1 To 11551 Step 231
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance?q=" & Range("A" & i).Value & "&fstype=ii", Destination:=Range("B" & i))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery = False
End With
Next i
End Sub

 

[schielrn]

Right after the for statement at the top, try this line:

If Range("A"& i).Value = "" Then Exit For

Hope that helps.

 

[mole999]

I call this as required

Sub connections()

    'TimeOff local timer control
Application.EnableEvents = False
Application.Calculation = xlManual
    On Error Resume Next
Application.ScreenUpdating = True
    Dim i
    For i = 1 To 10000
        ActiveWorkbook.connections("Connection" & i).Delete
        'Sheets("combination").Range("m11") = i so i can see whats happening
   Application.ScreenUpdating = True
   DoEvents
    Next i

    MsgBox "done"

Application.EnableEvents = True
Application.Calculation = xlAutomatic
    ActiveWorkbook.Save

    TimeOn local timer control

End Sub

for those really large collections of connections, but easily minimsed and adapted

 

[tyger0951]

Sub CopyTExtFIle()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Tyger\Desktop\test2.txt", Destination:=Range("$A$1"))  'Change Connection to your file location and Desitnation to where you want to paste
        .Name = "test2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn

End Sub

Try that , that should delete the connection afterward

 

[ServerDude]

Is there a way to exclude recently refreshed connections from the delete all connections script. I have a sheet which has built up about 65000 connections and i need to keep about 100 of them.

Thanks.
Jason