In Excel’s Power Query tool, you get the option to directly import data from a website using a URL address. However, if the website requires a login with a username or password, you must go through the authentication process using the correct credentials.
For a website that uses basic authentication, you can enter the credentials directly from Excel. If the website uses a more complex login system, a VBA macro may help you with the login.
➤ In a blank sheet where you want the scraped data, go to the Data tab >> Get Data drop-down >> From Other Sources >> From Web. In the From Web dialog box, click on Basic for a single website and paste the website URL (after logging in) in the URL box. Press Ok.
➤ From the Access Web Content box, choose Basic for a general website login or Windows to log in with a Microsoft account. Enter your Username/Email and Password. Click Connect.
➤ Now, your data from the web will be loaded in the Power Query Editor. Select Table 1, Table 2, or other options to find the right table with the correct data. Transform the data if you want, and click on the Load drop-down to select a location to input the scraped data.
This article covers how to extract data from a website that requires a login using Power Query and VBA coding.
Extract Data from Website Using Power Query
To explain the methods, we’ll use the Quotes to Scrape website to extract the following data:
Excel’s Power Query tool can handle logins for many standard web pages. It works for sites that return HTML or JSON after a simple POST form and don’t require JavaScript-heavy authorization. Here’s how to use it:
➤ Log into the website you want to extract data from and copy the URL of the webpage containing your desired data.
➤ From the sheet where you want to put the extracted web data, go to the Data tab and navigate to the Get & Transform Data group.
➤ Click on the Get Data drop-down, select From Other Sources, and choose From Web from the menu.
➤ As Excel opens the From Web box, choose Basic to extract from a single website or select Advanced to get data from multiple pages. Enter the website URL that you copied earlier and press Ok.
➤ Now, in the Access Web Content box, click on Basic for a standard website login. Choose Windows if the website requires a login with your Microsoft account. You can also choose Web API if you have the API website’s official API key. Click on Organizational Account to log in using your organization’s server.
➤ In the given boxes, enter the Username or Email and Password to log into the site. Click Connect.
➤ Excel will now start extracting the data and put it into tables. As the Navigator Pane opens on the right side of your screen, click on each Table option (e.g., Table 1, Table 2, Table 3, etc.) to find the table with the correct dataset.
➤ Use the Transform Data button at the bottom to make changes to the table. You can also click on Web View to select which table data you want to extract.
➤ Once the table is ready, click on the Load drop-down and select a location to insert the table.
➤ Here’s our extracted data:
Customized VBA Macro for Complex Website Logins
Some websites have more advanced security, and Excel can’t handle the login natively. So, we’ll use a VBA macro to log into a website within Excel and extract data (tables, lists, paragraphs, etc.). For this example, we’ll extract data from Product and Pricing Selector | Tableau from Salesforce. Below are the steps:
➤ First, copy the website login URL and the webpage URL containing the data you want to extract. After that, press Alt + F11 to open the VBA Editor.
➤ Go to the Insert tab and choose Module to create a new module.
➤ In the new module box, copy and paste the VBA code below:
Option Explicit
' ================================
' MAIN ENTRY POINT
' ================================
Sub ScrapeSiteSmart()
On Error GoTo ErrHandler
Dim loginUrl As String, targetUrl As String
Dim userName As String, passWord As String
loginUrl = InputBox("Enter the login URL:", "Login Page")
If loginUrl = "" Then Exit Sub
targetUrl = InputBox("Enter the target page URL (data page):", "Target Page")
If targetUrl = "" Then Exit Sub
userName = InputBox("Enter your username or email:", "Login Info")
passWord = InputBox("Enter your password:", "Login Info")
If userName = "" Or passWord = "" Then
MsgBox "Username or password missing. Exiting.", vbExclamation
Exit Sub
End If
LoginAndSmartExtract loginUrl, targetUrl, userName, passWord, 30
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Description, vbCritical, "ScrapeSiteSmart"
End Sub
' ================================
' MAIN PROCESS: LOGIN + EXTRACT
' ================================
Private Sub LoginAndSmartExtract(loginUrl As String, targetUrl As String, _
userName As String, passWord As String, _
Optional timeoutSeconds As Long = 30)
On Error GoTo ErrHandler
Dim ie As Object, doc As Object
Dim f As Object, inputEl As Object
Dim foundUser As Boolean, foundPass As Boolean, foundSubmit As Boolean
Dim tbls As Object, ws As Worksheet, el As Object
Dim textChunks As String
' Create IE object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate loginUrl
If Not WaitForReady(ie, timeoutSeconds) Then
MsgBox "Login page did not load properly.", vbCritical
GoTo Cleanup
End If
Set doc = ie.document
' Try to detect and fill login form
For Each f In doc.forms
If Not foundUser Then foundUser = FieldExistsThenSet(f, Array("username", "user", "email", "login"), userName)
If Not foundPass Then foundPass = FieldExistsThenSet(f, Array("password", "passwd", "pass"), passWord)
' Look for submit button
If Not foundSubmit Then
On Error Resume Next
Set inputEl = f.querySelector("input[type='submit'], button[type='submit']")
If Not inputEl Is Nothing Then foundSubmit = True
On Error GoTo 0
End If
If foundUser And foundPass Then Exit For
Next f
If foundSubmit Then inputEl.Click
WaitForReady ie, timeoutSeconds
' Navigate to target URL
ie.navigate targetUrl
If Not WaitForReady(ie, timeoutSeconds) Then
MsgBox "Target page failed to load.", vbCritical
GoTo Cleanup
End If
Set doc = ie.document
' --- Extract all TABLES ---
Set tbls = doc.getElementsByTagName("table")
If tbls.Length > 0 Then
Dim i As Long
For i = 0 To tbls.Length - 1
Set ws = AddResultSheet("Table_" & (i + 1))
DumpTableToSheet tbls.Item(i), ws
Next i
End If
' --- Extract readable TEXTS if no tables found ---
If tbls.Length = 0 Then
textChunks = SmartExtract(doc)
Set ws = AddResultSheet("Extracted_Text")
ws.Range("A1").Value = "Extracted Web Data"
ws.Range("A2").Value = textChunks
ws.cells.WrapText = True
ws.Columns("A:A").AutoFit
End If
MsgBox "Extraction completed!", vbInformation
Cleanup:
On Error Resume Next
ie.Quit
Set ie = Nothing
Exit Sub
ErrHandler:
MsgBox "Error during extraction: " & Err.Description, vbCritical
Resume Cleanup
End Sub
' ================================
' SUPPORTING FUNCTIONS
' ================================
Private Function FieldExistsThenSet(formObj As Object, fieldNames As Variant, fieldValue As String) As Boolean
Dim nm As Variant, el As Object
On Error Resume Next
For Each nm In fieldNames
Set el = formObj.querySelector("input[name='" & nm & "'], input[id='" & nm & "']")
If Not el Is Nothing Then
el.Value = fieldValue
FieldExistsThenSet = True
Exit Function
End If
Next nm
End Function
Private Function WaitForReady(ie As Object, Optional timeoutSeconds As Long = 15) As Boolean
Dim t0 As Double: t0 = Timer
Do
DoEvents
If Timer - t0 > timeoutSeconds Then Exit Do
If Not ie.Busy And ie.readyState = 4 Then
WaitForReady = True
Exit Function
End If
Loop
WaitForReady = False
End Function
Private Function NzString(v As Variant) As String
On Error Resume Next
If IsNull(v) Or IsEmpty(v) Then
NzString = ""
Else
NzString = Trim(CStr(v))
End If
End Function
Private Function SmartExtract(doc As Object) As String
Dim paras As Object, divs As Object, spans As Object, txt As String
Dim el As Object
On Error Resume Next
Set paras = doc.getElementsByTagName("p")
Set divs = doc.getElementsByTagName("div")
Set spans = doc.getElementsByTagName("span")
For Each el In paras
If Len(Trim(el.innerText)) > 0 Then txt = txt & el.innerText & vbCrLf
Next el
For Each el In divs
If Len(Trim(el.innerText)) > 0 Then txt = txt & el.innerText & vbCrLf
Next el
For Each el In spans
If Len(Trim(el.innerText)) > 0 Then txt = txt & el.innerText & vbCrLf
Next el
SmartExtract = txt
End Function
Private Function AddResultSheet(sheetName As String) As Worksheet
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sheetName)
If Not ws Is Nothing Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0
Set ws = ThisWorkbook.Sheets.Add
ws.Name = sheetName
Set AddResultSheet = ws
End Function
Private Sub DumpTableToSheet(tbl As Object, ws As Worksheet)
Dim rows As Object, r As Long, cells As Object, c As Long
Set rows = tbl.getElementsByTagName("tr")
For r = 0 To rows.Length - 1
Set cells = rows.Item(r).getElementsByTagName("th")
If cells.Length = 0 Then Set cells = rows.Item(r).getElementsByTagName("td")
For c = 0 To cells.Length - 1
ws.cells(r + 1, c + 1).Value = NzString(cells.Item(c).innerText)
Next c
Next r
ws.Columns.AutoFit
End Sub
➤ Press F5 or click on the Run tab >> Run Sub/UserForm.
➤ In the Login Page dialog box, enter the login page’s URL. Press Ok.
➤ In the following dialog box, insert the webpage URL, username/email, and password. Click Ok each time.
➤ A new Internet Explorer window opens with the webpage you entered.
➤ Excel will now redirect to the website and extract data from the webpage. However, this method isn’t suitable for complex JavaScript-heavy sites. You can find all the available tables in different worksheets. While texts are extracted, this code can’t export special characters.
Frequently Asked Questions
How to automatically refresh extracted web data in Excel?
You can make Excel automatically refresh data pulled from a website at set intervals or when the workbook opens. Go to the Data tab >> Queries & Connections >> right-click your query >> Properties. Under the Usage tab, check Refresh Every and set the time (e.g., every 10 minutes). Or, check Refresh Data When Opening the File.
Can I import data as a CSV file with login from Excel?
To import data as a CSV file from a website that requires a login, first, log in manually on the site and download the CSV. In Excel, go to the Data tab >> Get Data drop-down >> From Text/CSV and load it directly. If the site provides an API endpoint for CSV data, go to the Data tab >> Get Data drop-down >> From Other Sources >> From Web. For authentication, use the Basic or Web API option in the login prompt and enter the API or CSV link (e.g., https://example.com/data/export.csv?token=yourkey).
How to combine data from multiple secured websites into one Excel sheet?
Go to the Data tab >> Get Data drop-down >> From Other Sources >> From Web. To connect to the first website, enter the site or API URL in the From Web dialog box and load the extracted data. Repeat the steps for other websites and create a new query for each site using its own login credentials. Finally, combine the data in Power Query by clicking the Home tab >> Append Queries (to stack them vertically) or Merge Queries (to join side by side by matching columns). Click Close & Load to place the combined data into one sheet.
Concluding Words
As extracting data from a secure website can be unethical, many websites use high security measures to prevent Excel from accessing their data. Therefore, even the VBA macro might not always work. To gain access, you’ll need a third-party scraping tool (usually paid) or Python coding to create a workaround by manually inspecting the coding elements of the site.
