I use Power Query to get data from Web as your steps in answer forum: Then I will get the result: What I did, I use Chrome as default browser and login the web with the password. Then I save the password in Chrome. Then I go to Excel and load data, first time I failed to get by using legacy From Web option.

1. Web queries are available in Excel 1997 or later for Windows and Excel 2001 or later for the Mac. Getting Started Creating a Web query is relatively easy—all you need is the Web address (URL) of the Web site from which you want to collect data and the tables on that page containing the data you wish to capture.
2. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel. Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel.

Pretty much anything you can do in Excel, you can do in VBA too - and that includes extracting information from a website's table.

The Excel Principle

Supposing that you want to create an Excel workbook with an up-to-date list of all of the Wise Owl Excel VBA courses (who wouldn't want to do this?). You could do this in 3 easy stages. First, choose to get data from a website:

Choose the option shown on the Data tab of the Excel ribbon.

Secondly, type or paste in a URL (website address) and click on Go:

Set the address you want to go to and click on Go.

Thirdly, click on the yellow-and-black arrow next to the table you want to import, and then click on the Import button:

The arrow gets a green background when you select it; the Import button is at the bottom right of the form.

After going through one more fairly self-explanatory dialog box, you'll see the imported data:

The standard import doesn't bring in any formatting.

The method shown above is for older versions of Excel, although the principle is the same for newer versions. You can find out much more about Excel on one of our online or classroom Excel courses or VBA courses.

Reproducing this in VBA

To import this table of data in VBA, first tell your program where the website can be found:

Sub GetCourseList()

'add a table of courses into an Excel workbook

'the website containing the files listing courses

Const prefix AsString = WiseOwlWebsiteAddress & '/training/dates/'

Const FileName AsString = 'microsoft-excel-advanced'

Dim qt As QueryTable

Dim ws As Worksheet

'using a worksheet variable means autocompletion works better

Set ws = ActiveSheet

The next thing to do is to create a query table, specifying where the results should go and where it should get its data from:

'set up a table import (the URL; tells Excel that this query comes from a website)

Set qt = ws.QueryTables.Add( _

Connection:='URL;' & prefix & FileName & '.htm', _

Destination:=Range('A1'))

Note the URL; prefix, to tell VBA that it should get the data from a website, rather than a database or any other file format.

You can now set various parameters:

Just a few of the many ways in which you can customise a query table (although the default settings usually work OK).

Here is an example of some customisation for your query table:

'tell Excel to refresh the query whenever you open the file

qt.RefreshOnFileOpen = True

'giving the query a name can help you refer to it later

qt.Name = 'ExcelAdvancedCoursesFromWiseOwl'

'you want to import column headers

qt.FieldNames = True

'need to know name or number of table to bring in

'(we'll bring in the first table)

Excel For Mac Web Query

qt.WebSelectionType = xlSpecifiedTables

qt.WebTables = 1

The website might contain several tables; here we're choosing to import the first one only.

Finally, we need to run the query!

'import the data

qt.Refresh BackgroundQuery:=False

EndSub

When you run this macro, you should get a current list of all Wise Owl Excel VBA courses in your active worksheet.

Using the Imported Data

Now that you've got a set of rows, it's up to you how you use them. Here's one example, which displays the imported course dates in a message box:

The code below would display this message at the time of writing (clearly by the time you read this, the list will be different!).

Here's some sample code to create and display this message:

Sub UsingCourseList()

Dim StartCell As Range

Dim c As Range

Dim msg AsString

'go to top left cell

Set StartCell = Cells.find('Start date')

If StartCell Is Nothing Then

MsgBox 'No start cell found'

ExitSub

Excel Web Query With Username And Password

EndIf

'start message to show

Excel For Mac Web Query Example With Login And Password Change

msg = 'Wise Owl Excel VBA courses are:' & vbCrLf

'loop over all cells in this column, adding to list

ForEach c In Range(StartCell.Offset(1, 0), StartCell.End(xlDown))

Excel For Mac Web Query Example With Login And Password Free

'add this course into message, with blank line

msg = msg & vbCrLf & Format(c.Value, 'dd/mm/yyyy')

Next c

'display results

MsgBox msg

EndSub

Importing data tables like this is much the easier of the two methods covered by this blog, but what happens when you want to get less structured data from a web page? For that, you need to parse the web page's HTML (a process known as scraping websites).