VBA Releasing IQY file connections

[meganbbk1979]

I have a report I am working on that requires data from multiple IQY files generated from the same web platform. My problem is that when I run it, excel seems to be holding the first iqy file URL and repeating the other data calls with that same IQY file. The URLs are the same, but there is an embedded token for each one that can be seen in notepad. I've tried deleting connections in between each call, but it still repeats only pulling the original IQY file. A friend suggested I need to write something to release the connection, but I haven't found anything that works.

Sub GetData()
' Edit path to .iqy file, if necessary.


Call delCon
Set deddata = ThisWorkbook.Worksheets("StandDed")
deddata.Rows(1 & ":" & deddata.Rows.Count).ClearContents 'clear sheet
deddata.Activate
IQYFile2 = "S:\PrismHR\LIVEEXCELS\StandardDed.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile2, Destination:=Range("A1"))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Call delCon

Set tax = ThisWorkbook.Worksheets("EETAX")
tax.Rows(1 & ":" & tax.Rows.Count).ClearContents 'clear sheet
tax.Activate

Call delCon
IQYFile = "S:\PrismHR\LIVEEXCELS\EETAX.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile, Destination:=Range("A1"))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Call delCon

Set arrear = ThisWorkbook.Worksheets("Arrears")
arrear.Rows(1 & ":" & arrear.Rows.Count).ClearContents 'clear sheet
arrear.Activate
IQYFile = "S:\PrismHR\LIVEEXCELS\ARREARS.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile, Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With



Set er = ThisWorkbook.Worksheets("ERTAX")
er.Rows(1 & ":" & er.Rows.Count).ClearContents 'clear sheet
er.Activate
IQYFile = "S:\PrismHR\LIVEEXCELS\ERBILLING.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile, Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


Set det = ThisWorkbook.Worksheets("PayDetail")
det.Rows(1 & ":" & det.Rows.Count).ClearContents 'clear sheet
det.Activate
IQYFile = "S:\PrismHR\LIVEEXCELS\PayDetail.iqy"
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & IQYFile, Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With





End Sub

Sub delCon()


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


End Sub

 

[meganbbk1979]

I did find a way around this issue by writing out the url & token. It works for the iqy files that don't have filter options.
I tried this now for a couple that do have filter options and it gives me an error. "Compile Error: Expected: list separator or )"

Here is the code. Any ideas on how to fix this? I have to keep the filter options available as these will be run based on a different client ID and different date each time it is run.

Sub ConnectRep()
Set s1 = ThisWorkbook.Worksheets("Invoice")
Set s2 = ThisWorkbook.Worksheets("Journal")
Set s3 = ThisWorkbook.Worksheets("Report")

s1.Activate
s1.Rows(1 & ":" & s1.Rows.Count).ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://xyzinf.prismhr.com/xyz/informer/query?" & _
"token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx=en_US&parameter_0=["Company ID (Indexed) exactly matches"]&parameter_1=["Pay Date on"]&encoding=ANSI", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With



ThisWorkbook.Connections.Item(1).Delete ' ===============important===================