unable to get external data from web in excel for mac

Unable To Get External Data From Web In Excel Password To Execute
Unable To Get External Data From Web In Excel Trial And Error

I would suggest granting the team read access and simply let them use their own user idpwd to connect and run the query.

However, if you find yourself running the same query on a regular basis and then manually importing the results into Microsoft Excel and customizing the look and feel of the results within Excel (alignment, formatting, titles, etc.), you may find it quicker to connect Excel directly to the external data source (database) and letting Excel run your SQL to retrieve the data directly.

Select the Data tab on the ribbon and click on the From Other Sources command in the Get External Data ribbon group.

This will bring up the Choose Data Source panel.

Note: I had trouble when the Use the Query Wizard to createedit queries item was checked so I keep it unchecked.).

If you dont see the connection needed, you will have to manually addconfigure the data source first.

Clicking OK will start the Microsoft Query application and display a connect to database dialog.

It may take a while to connect to the data source the first time.

Once connected, Microsoft Query assumes you want to create your SQL query using the graphicalvisual method (similar to MS Access) and prompts you to select the tables for your query.

Since we didnt use the graphical SQL editor, Microsoft gives us the following Microsoft Query warning.

Assuming you have no SQL errors, you should see the results of your SQL in Microsoft Query.

From the File menu in Microsoft Query, select Return Data to Microsoft Office Excel.

When you want to rerun your SQL, select the Refresh command from the Connections ribbon group, or right-click in the Table and select Refresh from the pop-up menu.

Unable To Get External Data From Web In Excel Password To Execute

You may be prompted for your user id and password to execute the SQL.

To view the properties, click on the Properties command button located in the Connections ribbon group on the Data tab.

Once you have the SQL and all column formatting the way you want (you can use any of the column formatting options for dates, currency, highlighting, alignment, etc.), you will want to turn on Preserve column sortfilterlayout and Preserve cell formatting.

If you have manually sized your columns, and want to keep them that way when you refresh the data, turn off Adjust column width.

Unable To Get External Data From Web In Excel Trial And Error

The best way to learn how these options affect the view is by trial and error.

On the Connection Properties panel, select the Definition tab.

If your alias contains a space, you can use the (double quotes) around the alias.

Im not sure what the SQL limitations are; I have been able run some pretty complex queries (multiple table joins, sub-selects, where conditions, etc.) without trouble.

You may be prompted to enter your user id and password for the database connection.

That is, pull up the spreadsheet, save it as new workbook and then edit the SQL for the new report.

My intention, is for my team, whom do not have SQL access to be able to refresh the dataset at their convenience.

Unable To Get External Data From Web In Excel For Mac