Export data on excel from a website protected by a login

Question

Export data on excel from a website protected by a login

ANGELO 20

How can i export data on excel from a website protected by a login? of course i have access to it, but using the "get data" menu i can't do that. thanks for any help

(my final aim is to create a macro who can grant me auto access to the website, extract data and repeat the process every x minutes)

Accepted answer

0 additional answers

Your answer

Answer 1

Emily Hua-MSFT 27,796

Hi @ANGELO

but using the "get data" menu i can't do that

Does the "get data" menu mean the Power Query in Excel, like the image below?

Capture55

If not, you may try "From Web" option, enter the URL, the User name and Password, check whether you can get those data.

Capture56

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

ANGELO 20 Reputation points

2023-03-23T09:25:19.2+00:00

i still can't access the page. it said the credentials are not valid. i'm trying to do the same from a macro but ieApp.Navigate “https://ikeatracking.westeurope.cloudapp.azure.com/p236/443_p.php” this row is marked as an error.

Sub GetTable()

Dim ieApp As InternetExplorer

Dim ieDoc As Object

Dim ieTable As Object

Dim clip As DataObject

'create a new instance of ie

Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging

ieApp.Visible = True

'assume we’re not logged in and just go directly to the login page

ieApp.Navigate “https://ikeatracking.westeurope.cloudapp.azure.com/”

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names

With ieDoc.forms(0)

.login.Value = “login”

.Password.Value = “password”

.submit

End With

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'now that we’re in, go to the page we want

ieApp.Navigate “https://ikeatracking.westeurope.cloudapp.azure.com/p236/443_p.php”

Do While ieApp.Busy: DoEvents: Loop

Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'get the table based on the table’s id

Set ieDoc = ieApp.Document

Set ieTable = ieDoc.all.Item(“sampletable”)

'copy the tables html to the clipboard and paste to teh sheet

If Not ieTable Is Nothing Then

Set clip = New DataObject

clip.SetText “” & ieTable.outerHTML & “”

clip.PutInClipboard

Sheet1.Select

Sheet1.Range(“A1”).Select

Sheet1.PasteSpecial “Unicode Text”

End If

'close ‘er up

ieApp.Quit

Set ieApp = Nothing

End Sub
Emily Hua-MSFT 27,796 Reputation points

2023-03-24T08:55:44.6066667+00:00
Hi @ANGELO

Thanks for your reply.

If the Power Query does not work, you need to find out how the site is set up.

If it has API endpoint, please refer to following links to have a check.

https://www.excelforum.com/excel-general/1225078-power-query-reading-a-text-or-csv-file-that-is-publicly-available-on-the-web.html

https://stackoverflow.com/questions/70152753/get-data-from-password-protected-web

(Please Note: Since the web sites are not hosted by Microsoft, the links may changed without notice. Microsoft does not guarantee the accuracy of this information.)

The Basic login option above applies to basic Username / PW login.

Share via

Export data on excel from a website protected by a login

0 additional answers

Your answer