473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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?

Expand|Select|Wrap|Line Numbers
  1. ' Pull Data from the Web report data file 
  2.   With ActiveSheet.QueryTables.Add(Connection:= _
  3.         strWebSite & strVariables, _
  4.         Destination:=Range("A1"))
  5.         .Name = "ReportData"
  6.         .FieldNames = True
  7.         .RowNumbers = False
  8.         .FillAdjacentFormulas = False
  9.         .PreserveFormatting = True
  10.         .RefreshOnFileOpen = False
  11.         .BackgroundQuery = False
  12.         .RefreshStyle = xlOverwriteCells
  13.         .SavePassword = False
  14.         .SaveData = True
  15.         .AdjustColumnWidth = True
  16.         .RefreshPeriod = 0
  17.         .WebSelectionType = xlAllTables
  18.         .WebFormatting = xlWebFormattingNone
  19.         .WebPreFormattedTextToColumns = True
  20.         .WebConsecutiveDelimitersAsOne = True
  21.         .WebSingleBlockTextImport = False
  22.         .WebDisableDateRecognition = False
  23.         .WebDisableRedirections = False
  24.         .Refresh BackgroundQuery:=False   ' <===== Here is the error 1004
  25.     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
Mar 2 '22 #1
1 15742
zmbd
5,501 Expert Mod 4TB
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.
May 20 '22 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: dailem | last post by:
I have a bit of code tied to a command button in Access that runs a query then transfers it to a new Excel worksheet (& new file). It works fine EVERY OTHER TIME THAT I RUN IT....what the 'heck...
3
by: Maverick | last post by:
I'm new to this, but I have received so much great information here, I had to join. Here is a problem am encountering with the following code in MS Access: objSht.Activate ...
0
by: Jono | last post by:
Hello, I've been getting this message when closing excel (not necessarily when closing the workbook by itself, but when closing Excel and the workbook at the same time): ...
4
by: dilau | last post by:
I have a problem. The error in subject appear when i run the macro Can u tell me why Case "CENTRALIZATOR SURVEY" Dim SHT As Object Set...
0
by: forgedascendant | last post by:
Good day everyone, I am new to this site so please forgive me if this post isn't completly correct. For the past 2 weeks I have been beating my head against the wall trying to figure out what is...
1
by: asmith3088 | last post by:
I created a dashboard (two charts) based on six pivot tables whose data is pulled from an Access database. There are dropdowns in the dashboard that are linked to the pivot tables using named ranges...
0
by: grego9 | last post by:
When I run the following macro in Excel (using VBA) I get the run time error 1004 application defined or object defined error. I get the usual debug message and when I click end I actually get the...
5
by: titli | last post by:
Hi guys, I have a .mdb which generates , further some mini .mdbs..Later these generated mini mdbs are provided as input to excel viewer.Till yesterday everything was working fine.. But today after...
1
by: mattmasters | last post by:
Hi there I am new to this forum so would appreciate any help that is on offer. I am writing some VB in Excel and getting the "Run-Time error '1004'. Application- defined or object-defined...
16
by: aflores41 | last post by:
Sub macro1() 'Dim frm As UserForm Dim I As Integer 'To could be as many as you want. I only put 25 as a limit. For I = 1 To 25 Dim a As String a = Sheet1.Cells(I, 1).Value Dim b As String b =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.