QueryTables.Add VBA run time error '1004' in EDGE, works in Explorer

[SkydiverMike]

I have inherited an issue trying to get Excel Workbooks to load data from an ASPx page to a backend SQL Database.

It works fine in Explorer, but not at all in Edge or Chrome.

It works fine in Explorer, because explorer authenticates the user before doing the QueryTables.Add. There is nothing in the code to prompt it to do it, but the windows security window pops up fine in explorer and asks the user to login but does not in Edge.

Since Excel doesn't authenticate the user, an error 1004 occurs because it does not have access to the file. How do I make Edge Authenticate the user?

' Pull Data from the Web report data file 
  With ActiveSheet.QueryTables.Add(Connection:= _
        strWebSite & strVariables, _
        Destination:=Range("A1"))
        .Name = "ReportData"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .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   ' <===== Here is the error 1004
    End With

Not that it matters (since it fails for hundreds of different reports), but the value of concatenated variables strWebsite & strVariables is:
URL;http://gemteam.Reports/ReportData.as...12&v0=10063832
I believe the issue might be something to do with referencing the ActiveSheet.
It works totally fine in explorer, but Edge seems to be blocking it somehow.

Any help would be greatly appreciated

[zmbd]

Good Morning SkydiverMike
Please do not bump your threads - especially by creating new threads - bad form to bump, very bad form to do so using multiple threads.
We empathize with the frustration you are feeling related to the inactivity within your post.
>Your post is being seen - as of this post you are at almost 9000 views- just the right person hasn't come along yet with the answer to your question!

Please keep in mind, no one here is paid, this is not our main job (in fact I am a Chemist, not a CompSci - but the job demands heavy data manipulations - databases are ideal).

So to your question:
+ MS in its wisdom has either eliminated or severely restricted alot of actions that used to happen between IE, MSOffice, and VBA due to all of the blackhat activities; thus, there may not be an easy workaround.

+ You haven't provided the entire message so we can only guess that you have one of the following RTE1004 subtypes (Once we have an idea as to what's going on with the subtype we can start looking for a workaround):

• That Name is already taken. Try a different One: (???)
• Method “Range” of object’ _ Global’ failed: (???)
• Select Method of Range class failed: (???)
• Method open of object workbooks failed: (???)
• Method Sorry We couldn’t Find: (???)
• Activate method range class failed: (???)
• (....) and there are a few more - these are just the ones I've ran into

As you can see RTE1004 is a hot-mess of an error message - technically correct and only slightly useful in pointing us to the root cause of the error.
> The error occurring on line24 is most likely a red-herring
> If the actual issue is "ActiveSheet" then explicitly name the sheet within the code should fix the issue; however, I don't think that's the root cause. We'll need the exact subvariant of the RTE1004 to go any farther.
(you can see another example of how insidious the RTE1004 can be in this thread
Run time error-1004 on query tables.add connections )

+ I ran into something similar with MSEdge/Chrome with a VBA that creates and then launches a local HTML file with a loading spinner gif and some user feedback that worked absolutely spotlessly with IE (needed the async user interaction to keep users from killing the access process as the query takes several minutes to complete) then the company upgraded everything to Win10-Enterprise and Office365 (with the desktop applications too) and the code broke due to the baked-in security within MSEdge. Kept getting a "RTE1004 - Range of Object" error, Drove me nuts, I finally figured out that MSEdge was dropping the object connections and all I had to do was step thru the open windows to find it and then reconnect to the object.