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]

Does the code I provided you yesterday not work?

http://www.mrexcel.com/forum/showthread.php?t=528195

At the very end it loops through every connection and removes them, but you took that out?

 

[tlindeman]

sorry didn't want to bother you again, no I get an invalid procedure error.

 

[tlindeman]

the actual error is " run time error 5" invalid procedure call or argument

 

[schielrn]

So if you run this code by itself does it not work and give an error?

sub deleteConnections()
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn
end sub

<!-- / message -->

 

[tlindeman]

It works as a standalone, I set it up so I would call the code so call deleteconnections and then it errors out. There is an easy workaround though ,all I need to do is setup a second control that the user will hit. I also would like the deleteconenctions macro to run on exit, could you help me with that code?

 

[schielrn]

This needs to go in the thisworkbook part of the code section:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cn In ThisWorkbook.Connections
MsgBox cn
Next cn
End Sub

I'm not positive it is that code causing the error, but I did come accross this when searching for the error:

http://support.microsoft.com/kb/263498

Not sure if this actually has any relation to your project or not as I am not as familiar with ActiveX Data Objects (ADO) and so forth.

 

[tlindeman]

Here is my guess, I think your code works great, I think the only issue is that it tries to delete the connection when there is no connection and maybe that is the way it connects to google.com/financials. It is hard to explain but I noticed I get a message in cell B1 that states refreshing data, then it trys to remove the connections and then it actually connects and pastes the data in. In any case , no big deal, I will just use your code to delete the connections when the workbook closes. Thank You for your help. I really struggled trying to get the financials into excel and you helped tremendously.

 

[schielrn]

How did you have the code before?

Maybe it just needs to have some time before the actual delete connections is run? See if this errors out?

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
do
doevents
loop
For Each cn In ThisWorkbook.Connections
MsgBox cn
Next cn
End Sub

Hope that helps.

 

[tlindeman]

one last question, how do I stop the loop if it finds a cell that has nothing in it? I am thinking something like:

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

however that doesn't work. I have to click o.k, everytime it hits a cell that is blank.