VBA Releasing IQY file connections

meganbbk1979

New Member
Joined
Aug 3, 2017
Messages
2
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

meganbbk1979

New Member
Joined
Aug 3, 2017
Messages
2
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===================
 

Forum statistics

Threads
1,144,663
Messages
5,725,658
Members
422,635
Latest member
crisis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top