How to query an Excel file on SharePoint using VBA / ADO / Other

[Jon73]

I have a system that currently operates on a network folder very well, but I am exploring transferring this to Sharepoint mostly for ease of access and regional VPN / network folder issues
So far everything I've tested works well, but I have some queries that operate out of the main excel front end of the system for doing things like checking user permissions, etc.
These details reside in a file that currently reside in the network folder but I intend to move to sharepoint
In the network folder I can query the file as follows, easy....it works fine!

Set connection = CreateObject("ADODB.Connection")
With connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPro & "\" & "TheFile.xlsx" & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With

But! the connection to a sharepoint folder falls flat on its face when it tries to open the connection

Set connection = CreateObject("ADODB.Connection")
With connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & "\\companyname.sharepoint.com\sites\Sitename\Folder1\Folder2\Folder3\TheFile.xlsx" & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With

After this point I have some SQL to pull back a limited recordset for evaluation

I have searched far and wide for some code that works to query an excel file on sharepoint
I can even open the sharepoint file using VBA no problems but I can't query it unless I use power query which will add many steps and slow things down.
Is it even possible? Is there a completely different way?
I expect there is something special in the connection string that I'm missing

Thanks for any help!