Excel For Mac Web Query Example With Login And Password

Posted : admin | On 16-09-2021

Excel For Mac Web Query Example With Login And Password Change
Excel For Mac Web Query Example With Login And Password Download
Excel Web Query With Username And Password
Excel For Mac Web Query Example With Login And Password Free

In Excel, open the Data tab and choose From Other Sources - From Microsoft Query. Choose the LDAP DSN. Select the option to use Query Wizard to create/edit queries. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. Select the columns you want to import and click the arrow to add them to your query. I am trying to use Power Query to download an Odata Feed that I created using.net Web Api 2 and the OData v4 nuget package. I'm trying to access an Odata feed that requires authentication. When I edit the authentication type in power query, I'm never seeing the authentication key come through in the request.

Making and Using Excel web queries

Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.

By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies

Data in Web pages

Data in web pages can be displayed a variety of ways:

In row and column tables that are HTML based
As the result of a query
As a picture
Within a PDF file

With Microsoft Excel 2011 for Mac you can grab data from row and column HTML tables. If a query result is presented as an HTML table, you can capture that too. You can't import data directly from pictures or PDF files. Only data arranged in row and column HTML tables can be imported using a web query. Other techniques (e.g. Optical Character Recognition, Adobe PDF tools, etc) not discussed on this page can be used for capturing data from pictures and PDF files.

Getting the data

You can get HTML row and column data by running a web query. A web query is a text file saved with a .iqy file extension. A web query file contains the URL of the web page that holds the data.

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work.

MSN MoneyCentral Currencies
MSN MoneyCentral Major Indices
MSN MoneyCentral Stock Quotes

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet.

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

From the Data menu choose Get External Data > Run Saved query.

The Choose a Query dialog displays defaulting to the Queries folder.
Select a query file to run in the Choose a Query dialog.
Click the Get Data button.
The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.

Click the Run Saved Query button. Because Microsoft forgot to put this button into the Ribbon, to use this option you must first add the Run Saved Query command to a toolbar Once you've added the Run Saved Query command to a toolbar:

Click the Run Saved Query button.
The Choose a Query dialog displays defaulting to the Queries folder.
Select the query file to run in the Choose a Query dialog.
Click the Get Data Button.
The Returning Data to Excel dialog opens. You can choose a cell that will be used for the upper left cell of the imported table. The default location shown is the cell that was selected when you started these steps. We'll discuss the Properties option later.
Click the OK button. Wait as Excel imports the data from the web page. Import speed depends on network speed and how much data you import.

Run a query using a VBA (Visual Basic for Applications) macro

Click here for instructions on how to display the Visual Basic Editor (VBE)
Click here for an introduction to how to code using the VBE
For an example, see heading below on this page: Web queries and Visual Basic for Applications (VBA)

To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. 'Relative' hyperlinks and specialty hyperlink protocols will not work when clicked.

Make a simple web query

The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:

Open Microsoft Word to a new, blank document
Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.

Now you can run your saved web query as described earlier.

Make a dynamic (parameter) web query

Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):

WEB

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=['QUOTE','Enterstock, fund or other MSN MoneyCentral Investor symbols separated by commas.']

Selection=EntirePage

Formatting=All

PreFormattedTextToColumns=True

Excel For Mac Web Query Example With Login And Password

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

Here's another example of a dynamic web query. This simple is for Yahoo Finance where jasz is a stock market symbol:

http://www.finance.yahoo.com/q?f=jasx

This example also queries Yahoo, but this one puts the results into a single column

http://finance.yahoo.com/d/quotes.csv?s=ORCL&s=CSCO&s=COHR&f=sl1

Working with query result sets

The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).

Figure 1 - Returning External Data to Excel

Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.

Figure 2 - External Data Range Properties

If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt. The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.

Note the option to 'Fill down formulas in columns adjacent to data.' Enabling this option lets you use calculated columns with the web query result set.

Figure 3 - Parameters

Web queries and Visual Basic for Applications (VBA)

You must use full URLs and they must be http:// type URLs. Here is a code example showing VBA syntax:

Sub MakeWebQuery()
With ActiveSheet.QueryTables.Add(Connection:= _
'URL;http://www.domainname.com', Destination:= _
Range('A1'))
.PostText = 'local'
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
End Sub

Use the following code to refresh a query. The selection cursor must be in the QueryTable before running this code. If a parameter query is refreshed, the user will be prompted for parameters.

Sub RefreshQuery()
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

Excel Web Query With Username And Password

The CData ODBC driver for Jira uses the standard ODBC interface to link Jira data with applications like Microsoft Access and Excel. Follow the steps below to use Microsoft Query to import Jira data into a spreadsheet and provide values to a parameterized query from cells in a spreadsheet.

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

To connect to JIRA, provide the User and Password. Additionally, provide the Url; for example, https://yoursitename.atlassian.net.

You can then work with live Jira data in Excel.

In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query.
Choose the JIRA DSN. Select the option to use Query Wizard to create/edit queries.
In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. Select the columns you want to import and click the arrow to add them to your query. Alternatively, select the table name to add all columns for that table.
The Filter Data page allows you to specify criteria. For example, you can limit results by setting a date range.
If you want to use parameters in your query, select the option to edit the query in Microsoft Query.
To set a parameter in the query, you will need to modify the SQL statement directly. To do this, click the SQL button in the Query Editor. If you set filter criteria earlier, you should have a WHERE clause already in the query.
To use a parameter, use a '?' character as the wildcard character for a field's value in the WHERE clause. For example, if you are importing the Issues, you can set 'ReporterDisplayName=?'.
Close the SQL dialog when you are finished editing the SQL statement. You will be prompted to enter a parameter value. In the next step, you will select a cell to provide this value. So, leave the box in the dialog blank.
Close Microsoft Query. The Import Data dialog is displayed. Enter a cell where results should be imported.
Close the Import Data dialog. You will be prompted to enter a parameter value. Click the button next to the parameter box to select a cell. Select the option to automatically refresh the spreadsheet when the value changes.

The data is now imported into Excel. When you change the value in cell B1, the data will be filtered by the specified search criteria.