I would like to run a query in Excel that gets data from a website
table, that requires the user to be logged in to the site first.

At the moment, I get around this by right clicking on the cell that
"contains" the web query to edit the query. A window pops up that
allows me to 'edit' the table downloaded by the query. I use this
window to login to the website and navigate to the correct page on the
site. Once on the correct page at the site, I click "cancel" on the
edit web query window to close it. I can now run the web query from the
page using VBA scripts to do so.

Is there any way I can get Excel to send the relevant login and password
information to the site before running the query?

Or is there a better way to achieve my goal?

Any help is appreciated.