Sometimes when I export a data, the tool offers me only to have it in IQY file, in this case, I have to open it and save it as an excel file but most of the time, I don’t need to do that because from my main excel file, this macro will help me to extract the data then copy/paste it to the sheet I want in my main file. In the other hand, if I have to update the data each month, it is best to save it as an xls file then doing a refresh every month.

 

When I use the macro ?

To export the data from an IQY file to an excel sheet or to save it as an xls file then refreshing every month to get the updated data.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
' change xxx by the file name
' if located to another folder, put IQYFile = "C:\fullpath\xxx.iqy"
IQYFile = ThisWorkbook.Path & "\xxx.iqy"
' change yyy by the sheet name where to paste
' if paste to current sheet, put ActiveSheet instead first Worksheets("yyy")
' if paste to current sheet, remove second Worksheets("yyy"). and change A2 by the cell to paste
With Worksheets("yyy").QueryTables.Add(Connection:="FINDER;" & IQYFile, Destination:=Worksheets("yyy").Range("A2"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

This option is good if I have to use only 1 time the file but if every month, I need to download it to get the updated data, I prefer to save it in an excel xlsm file then use it as my main one or to export the data directly into a sheet of my main xlsm file. After that, every month, I just need to refresh the table.

For only 1 single data connection:

Sub test()
' change xxx by the sheet name
Worksheets("xxx").Range("A1").ListObject.QueryTable.Refresh
End Sub

For all data connection:

Sub test()
' change xxx by the sheet name
Worksheets("xxx").Range("A1").Select
ActiveWorkbook.RefreshAll
End Sub

Instead to refresh via a macro, I can do it with one of those options in the “connection properties”.