By: Rob Fisch | Updated: 2010-04-14 | Comments | Related: >SharePoint

Excel Web Internet Query. Loading an.iqy file (Also known as Web Queries) into an existing Excel file can be archieved by loading the.iqy file into connections using the following steps in Excel: Choose the tab 'Data' Click 'Existing Connections' Choose 'Browse for More.' (This opens up a file picker) Navigate to.iqy. Integrating an Oracle BI Request with Microsoft Excel's Internet Query Feature. Excel Web Query '.iqy' Files. The real key to creating a dynamic excel web query is to create your own '.iqy' file. In it's basic form, the '.iqy' file is simply a TEXT file with three main lines: WEB 1 paramname = value ¶m2=value2 You can create the file using a simple text editor!

Problem

I find it easier to analyze data using Excel. Is there a way to export my SharePointList or Library metadata?

Solution

Yes.

The example below shows a SharePoint list presented in a web part zone addedto a site page. For aesthetics, it is presented with a cleaner look using the 'SummaryToolbar', rather than the 'Full Toolbar'. Drivers modem interface. However, to access the Excel export option,a user must open the full list. This is easy to do by clicking on the web part title,in this case 'Helpdesk Progress'.

The full list shows the full toolbar. Click the 'Actions' menu and then select'Export to Spreadsheet'.

A prompt displays asking to 'Open' or 'Save' the export. For the purposes ofthis article I will save it first, but you can do either.

There is a default file name but I change it to something more meaningful. Thiscan be useful because the export file can be saved and reused by using the exportfile directly in Excel rather than navigating through the SharePoint portal again.

The file is saved with the 'iqy' file extension.

When opening the file in Excel 2007, I get a security prompt which must be enabledto use the file. (Excel 2003 works also..there maybe a different security prompt.)

Results

Excel Iqy Format

Here's what the Excel export looks like:

The iqy file is not a static file. The SharePoint data is not stored in the .iqyfile. This is 'Web Query File' and refreshes the SharePoint data every time yourun it. It's a great feature, but don't forget, if you are not connected to thenetwork (and if you don't have permissions) you won't get the data.

If you need to save a snapshot (a point in time) of the SharePoint data you should'Save As' an Excel Workbook (or some other static format). You would also need to'Save As' a workbook if you were sharing information with an external user (i.e.outside the organization).

• Save the .iqy file if you want to rerun the export with fresh data withoutgoing through the 'Export to Spreadsheet' procedure (described above).
• 'Save As' an Excel Workbook, if you need a static copy of the data. Theworkbook will NOT refresh new data. Only the web query (.iqy) file can.

Document libraries can also be exported to Excel, however, the export will notcontain the document. In it's place a link to the document is available.

Next Steps

• Check out these otherSharePoint tips.

About the author

Microsoft has silently added a new Group Policy (GPO) to allow Office 365 admins to block Excel users from opening untrusted Microsoft Query files with IQY, OQY, DQY, and RQY extensions.

Microsoft Query files are used to retrieve data from external sources such as corporate Microsoft Office Access, Microsoft SQL Server, and Microsoft SQL Server OLAP Services servers, Excel workbooks, and even from text files.

This type of file has been increasingly weaponized as part of malicious attacks, with various campaigns having been observed actively using IQY files, for instance, to deliver remote access Trojans and malware loaders since early 2018, with very recent reports also mentioning active campaigns.

Two other new GPOs allow Office admins to make sure that untrusted database files (e.g., DBF) and text-based files (i.e., CSV, DIFF, and SYLK) are always opened in Protected View as discovered by SwiftOnSecurity.

NEW: Excel adds Group Policy to block untrusted Microsoft Query files (.iqy, .oqy, .dqy, .rqy) from opening. These files bypass many security controls to allow infection. Policy requires Office365 license.
Security threat: https://t.co/DVGJPRJKcR
ADMX: https://t.co/5c4KIoSX5E

Untrusted Microsoft Query files can also be blocked manually

The three GPOs can be downloaded via the Microsoft Download Center in the form of Administrative Template files (ADMX/ADML) for Office 365 ProPlus, Visio Pro for Office 365, and Project Online Desktop Client.

While the download page states that the Administrative Template files will also work for volume licensed versions of Office 2019 and Office 2016, the GPOs themselves say that the 'policy setting only applies to subscription versions of Office, such as Office 365 ProPlus.'

'If you enable this policy setting, Microsoft Query files in an untrusted location are prevented from opening. Users will not be able to change this setting under File > Options > Trust Center > Trust Center Settings > External Content,' says the new Excel Group Policy.

'If you disable or don't configure this policy setting, Microsoft Query files in an untrusted location are not prevented from opening, unless users have changed this setting in the Trust Center.'

Users can follow the following procedure if they want to block connections to Microsoft Query files on their own without using this new GPO:

Outlook on the Web file extension blocklist also updated

Microsoft announced a week ago that they will also be blocking an additional 38 file extensions from being downloaded as attachments including Java, PowerShell, Python files to protect Outlook on the Web users from malicious attacks.

This adds to the current list of 104 blocked file extensions users of Outlook on the Web are blocked from downloading as a security measure.

The 38 new extensions to be be blocked in Outlook on the Web are listed below.

Incoming Office 365 security-focused updates

Microsoft also works on including a new Automated Incident Response playbook to Office 365 Advanced Threat Protection (ATP) to allow SecOps teams to investigate and remediate hacked accounts automatically, as well as a new tenant-wide idle session timeout feature for Microsoft 365 web apps for preventing information exposure stemming from unauthorized access to systems left unattended.

Iqy Excel Online

The company is also on extending the Office 365 Advanced Threat Protection (ATP) Safe Links protection capability to Office Online apps, a feature that provides 'time-of-click verification of web addresses' for URLs discovered in emails and Office documents, set to arrive in October.

Redmond is also currently rolling out better manual threat hunting features for the Office 365 Threat Explorer and is planning to enhance the quarantined malware and phishing email notification system for Microsoft 365 admins in early October.

Iqy Excel