gein


Hi, this is my first post here. I'm having some difficulties with Excel 2007. I'm creating Connections (URL based) on the fly in VBA. The code for that looks like this:

fId = Trim(Str(Target.Value))

Set QT = ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://here-the-address-to-the-location/ id=" & fId _
, Destination:=Target.Offset(-1, 2))
With QT
.Name = "ID_" & fId & "_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

I'm quite new to this so if I seem a bit lost, you know why. This code works great. It creates a Querytable called ID_XXXX_1. I save this name for later reference. When I want to delete this QT together with the Connection I just call ActiveSheet.QueryTables(NameOfTheQTWhichIHaveSaved).Delete

This removes the QT, NamedRange and the Connection, which is good. That's exactly what I want. The problem is, if something is wrong with the URL (most often the fID is incorrect) the .Refresh row will fail. If this happens I want to clean up everything, that is, delete the Connection that was created. But I'm unable to reference that particular Connection.

I cannot run ActiveSheet.QueryTables(NameOfTheQT).Delete because there is no such QT. It seems like the QT is created when the Refresh method is invoked. So I end up with a stalled Connection (which I cannot reference, in order to delete it).

Does anyone even understand my problem The best thing would be if I could name my Connection when I create it, then it would be really easy to remove it. But I haven't found a way to name the Connection on creation so its name is not known.

Thanks,

Patrik




Re: How to delete a connection?

Mezhick


Hi!

Why don't you use "QueryTables.Count" property Name is not principal for this. You have the ID of the last added connection.

So you need to add such code:

Code Block

on error GoTo ErrHandle

...

:ErrorHandle:

ActiveSheet.QueryTables.Item(ActiveSheet.QueryTables.Count).Delete

Set QT = Nothing







Re: How to delete a connection?

gein

Well, there's no problem with that. The thing is that the Connection is not removed. The Error handler runs and the QT seems to be removed but the Connection is still there. I can see that by opening up the Connections dialog where every connection is listed.

This will result in stalled Connections everytime there's an error in my URL string.

Is it possible to, in the same way you describe, get the latest created Connection






Re: How to delete a connection?

gein

I tried your solution with the Connections instead, and it almost works.

Code Block

ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete

This code actually doesn't delete the latest created Connection but the "last Connection".

If I create Connection1 and the Connection2 and after that I remove Connection1 and "accidently" adds a stalled Connection which needs to be removed. This new Connection will get the name Connection1 and my code will instead remove Connection2 instead of Connection1.

A new approach:

Is there a way to first create the Connection by using ActiveWorkbook.Connections.Add. And then use the resulting Connection when creating the QueryTable

Something like this pseudo vba code:

Code Block

Conn = ActiveWorkbook.Connections.Add( ... ... )

Set QT = ActiveSheet.QueryTables.Add( Conn, Destination:=Taget.Offset(-1,2))

With QT

...

...

But I can't change

Code Block

Set QT = ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.privataaffarer.se/fonder/fond.asp intFundID=" & fId _
, Destination:=Target.Offset(-1, 2))

into

Conn = ActiveWorkbook.Connections.Add( ... ...)

Set QT = ActiveSheet.QueryTables.Add(Conn , Target.Offset(-1, 2))





Re: How to delete a connection?

gein

I was able to solve the problem with:

Code Block

ActiveSheet.QueryTables(Target.Offset(0, 1).Value).WorkbookConnection.Delete





Re: How to delete a connection?

JohanBark

Dea MSDN,

This was very helfull. Is there and way of deleting these connections by name

Is there a reason why the name is given to the connection from the querytable yet not usable in a workbook.connections property

Thanks
Johan