SharePoint List to Excel resulting Web query file

[Robotman (Vivekananda Reddy)]

Hi there,

I have a SharePoint list and trying to export it using Export to Excel button as below:

While saving it will allow to save as Microsoft Excel Web Query File (.iqy) and saved it in a folder.

Now I would like to Read this excel into a data table for further use in the automation. I am clueless on how to proceed from here.

Any thoughts.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi @Robotman ,

Sharing my suggestion on the issue you are facing related to iqy file.

i think we can save as iqy file into xlsx format with the help of VB code and using file format as xlOpenXMLWorkbook. Please use invoke code activity to invoke the below vb code. Try and let us know.

Once you are able to save as iqy file to xlsx use Read range activity to read the xlsx data into data table. thanks.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(“Your iqy file path”)
wb.SaveAs(“Your iqy filepath with the extension .xlsx instead of iqy”,Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook)
wb.Save
wb.Close
excel.Quit
wb = Nothing
excel = Nothing
GC.Collect

[Jobin_Joy]

Hi @Robotman

Can you try the below?

Just save the iqy file locally and then point to the iqy file in the Excel Application Scope. Then you can use Read Range to pull the table. Usually it should automatically refreshes the query every time we run it.

Note: Based on the data load/no.of records, there might be a delay in response from server.

If this is not working, try whatever @kirankumar.mahanthi1 mentioned.

Hope this will be helpful. Thank you.

[Robotman (Vivekananda Reddy)]

@Jobin_Joy The above approach is working as desired. Thank you for the Solution.

I hope I should not required to download this query file every time from SharePoint right? Can I use the same for the future runs?

[Jobin_Joy]

Yes. It will refresh the data from the server based on the predefined query.

Thank you.