Get data from web with credentials

thakks 

As I stated, I know next to nothing about VBA and Excel Macros, but I (laborously) managed to come up with a working solution.
I followed a couple of online tutorials, one of which is here: 

http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/

I believe I had to activate the option of forms in Excel, but I can't remember how right now.

As this was something I used to do on a daily basis, I also set up a Macro to execute the data extraction automatically on opening the file and then saving the file once done. I also set up my Windows to open the file as pat of the start process, so the whole process would take place every time I started my PC. This way Power BI will pick up the latest version on refresh.

Not very efficient I'm afraid, but it worked for my particular needs.

The (relevant) code I used is this (as I say I know very little about VBA so won't be of much help if the macro doesn't work properly for you). Most of this comes from the website I quoted above:

Sub ImportData()

'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://www.YOURWEBSITELOGINPAGE.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)

.UserName.Value = "NAME SURNAME"

.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://www.YOURWEBSITEPAGEWITHDATA.com"

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("TABLENAME")

'

' ImportData Macro

' Connect to TABLE website: this code is for my particular case

'

'

   Range("A2:N110").Select

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;https://www.YOURWEBSITEPAGEWITHTABLE.com", Destination:= _

        Range("A1"))

        .Name = "NAMEOFPAGE"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .WebSelectionType = xlAllTables

        .WebFormatting = xlWebFormattingNone

        .WebPreFormattedTextToColumns = True

        .WebConsecutiveDelimitersAsOne = True

        .WebSingleBlockTextImport = False

        .WebDisableDateRecognition = False

        .WebDisableRedirections = False

        .Refresh BackgroundQuery:=False

    End With

‘this step is to paste into a selection range in Excel

Range("A2:N150").Select

    'close 'er up

ieApp.Quit

Set ieApp = Nothing

End Sub