Accessing Web data source with credentials on Excel for Mac

Question

Hi,

I work on a 2020 M1 Mac running Big Sur 11.6 and with Excel for Mac 16.70.

I want to connect to a web-based external data source (a webbased DB) but need to be able to enter credentials.
I have figured out how to connect based on the answer below (though the query folder is totally elsewhere).

But there is nowhere that I can see to save my username / password for the external database.

https://answers.microsoft.com/en-us/msoffice/forum/all/web-data-import-mac-does-anybody-know-how-we-add/1b69b0d5-c411-412b-913a-52b6d43c75b5

When I connect through Excel, I get an error message that says - Unable to open https://www.activityinfo.org/resources/query/v43/form/cls8grklasfXXX. Cannot locate the Internet server or proxy server.

(I've changed the URL as I don't want to share the data).

When I enter the URL in my standard browser (Firefox), I can see / access the data.

So I googled that error message, and came across this page https://learn.microsoft.com/en-us/office/troubleshoot/office-suite-issues/cannot-locate-server-when-click-hyperlink That mentions this only works if Microsoft Explorer (?!) is your default browser.

So I downloaded MS Edge and made it my default browser.

When I enter the http address for the DB / data webpage there, I get this error message:

{"code":"PERMISSION_DENIED","message":null,"localizedMessage":null}

When I run the sample web queries that come with Excel as a default (SampleWebQuery01.iqy), those work.

So all that leads me to thinking it must be a credentials issue.

Any thoughts? I'd be most grateful. Thank you.

Best,

Shirine

Answer 1

You are welcome. Gald to hear this update.

Shape data with Power Query Editor in Excel for Mac (office.com) - Blog

You can also give feedback on it. Have a nice day:)

Answer 2

No I haven't tried with another webpage, as this webpage is generated from the database that I would need to get the data out of.

So - other webpages would theoretically prove that this works in Excel, and that it's an issue with the page / DB that I am accessing.

While helpful in narrowing down the issue, it doesn't solve my problem. :(

I have contacted the company that runs the DB / website. So far, no joy = no solution yet from there either.

Thank you.

Answer 3

Have you tried with other webpage? It could be your webpage is not compatible with power query. I have no idea on it:(

Answer 4

No, we are back to the start 😩 - I still can't access the data via Excel.

I can access the webpage over Edge and see the data, but Excel gives me this error message:

This is the webpage with the data: https://www.activityinfo.org/resources/query/v43/form/cls8grklasfpeg53

My query looks like this:

WEB

1

https://www.activityinfo.org/resources/query/v43/form/cls8grklasfpeg53

Selection=1

Formatting=None

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

DisableDateRecognition=False

DisableRedirections=False

So, is it the query?

Or that I can't enter login credentials in Excel to make this possible?

Thank you.

Answer 5

Just updated my Excel and it seems this should now work!

What amazing timing.

Blog post to this:

https://insider.office.com/en-us/blog/shape-data-with-power-query-editor-in-excel-for-mac

Thanks for your support Snow Lu - and fingers tightly crossed.