Error - 2147024891 Access Denied

[ExcelWombat]

On the 31st August my code to retrieve data from a website suddenly failed with this error.
I have 4 PC's, two of them have exactly the same configuration (Windows 10, Office 365 Business) and the code fails on one and works on the other. The other two are both Windows 7 and the code works perfectly as well. The code fails at the xml.send command with this error.

Please Login or Register  to view this content.

To make this more confusing the code is in a loop. At each loop the URL is updated with the page number to retrieve data that is on multiple pages and it retrieves the first product on 4 pages successfully and then the URL is changed to retrieve the first page of the second product it fails but only on the one PC.
This program is distributed widely and so far I have had two other users report the same problem. Given the distribution, this is relative low but each reports the program working successfully until August 31.
I have tried removing all Microsoft updates but that did not work. I have tried various changes to the code but that does not work either.

This is way too deep for me and any help would be greatly appreciated. I am willing to try anything to get this fixed so all suggestions welcome (no matter how weird they are).

[Kyle123]

I suspect that you'll get that error if someone is trying to call an https page over http. The easiest thing to change the rules to https or possibly change the Msxml object to winhttp

[ExcelWombat]

Thanks for the response however it does not explain why the code works successfully to exactly the same site from every PC except for three isolated systems. I have tried https and a lot of other variations of the code but I do not believe it to be an issue with the code. The fact that it stopped working on three PC's all in different locations at exactly the same time would indicate an automatic system update of some type. I have removed updates, restored to restore points, tested it on other PC's all to no avail. I have 4 PC's all operating out of the same internet connection and they all work except for one.
I will try the code change anyway because it may result in a satisfactory workaround and I will report back.

[snb]

If this works:

Please Login or Register  to view this content.

It means xml (which I would always avoid) is reserved for something else on this computer.

[ExcelWombat]

SNB, Thanks for the suggestion. I tried the code you suggested but it still does not work. I am not convinced that this is a coding problem because the code works perfectly on other PC's. If it is related to the code then something else has come into play that makes it invalid on only a handful of PC's. I have over 100 users of this program and apart from mine, three others have reported the same issue. This may be because the program is not being used or the selection criteria that gives rise to the error is not being used.
The one common element so far is that all the systems that are failing are using Windows 10.
I have since found that on the day this started two windows security updates were installed automatically, KB3176935 and KB3176937. I have removed both but the error is not fixed.
I have also found a post relating to the same error that started at the same time, also for no apparent reason. The error reported is in Microsoft Access and the forum is UtterAccess. This problem has been resolved but the solution does not appear to be relevant to Excel VBA.

I very much appreciate everyone's thoughts and advice.

[ExcelWombat]

I have made some progress. I have not worked out why the code started to fail or why it only fails on some PC's but I may have found a work around.
The program gathers data from 5 websites and the code above is executed numerous times, just with a different URL.
I changed the code for the first two websites (it was failing on the second one) to this.

Please Login or Register  to view this content.

With this code it has started to fail on the third occurrence of the original code rather than the second.
This is way beyond my level of understanding so I am just going to change the whole program.

Thanks everyone.

[Kyle123]

Could you post the actual full code you are using. Have you tried using the winhttp object as I suggested?

[AB33]

Hi Kyle,
I thought on post 1 "Post" method is used, but the parameters do not seem to be included.
In post 7, the method has change to "Get".

[Doc.AElstein]

Hi Kyle,
I thought on post 1 "Post" method is used, but the parameters do not seem to be included.
In post 7, the method has change to "Get".

Those were excactly my thoughts, as my code with "Post" had an in between line with parameters. ( a setRequestHeader thing ) .... But I thought I had missed something obvious

I believewith "Get" you do not need that inbetween line


( this was my code snippet for "Post", given to me by kyle

Please Login or Register  to view this content.

with "Post" extra stuff is also tacked on after the .Send in my case

I have found that I can sometimes edit out the in between line. So there may be something in that. That is to say, it might sometimes work without it. That might explain the descrepencies ??

Alan

[Kyle123]

I'm a little unconvinced by the whole thing, I suspect the issue may lay elsewhere which is why I asked to see the rest of the code, but it's difficult to diagnose from conflicting snippets as you have quite rightly mentioned.

[Kyle123]

@Alan, parameters sometimes need to be sent in the request header depending on the website (irrelevant of post or get)

[ExcelWombat]

The original code I posted was all of it except for the Dim xml As Object statement. The code works and/or fails depending on the PC regardless of whether "Post" or "Get" is used. I have used post in ignorance, as a dinosaur from a previous era I am more comfortable with Cobol than VBA so I just copy code and hope it works. It usually does.
I did try winhttp as suggested but got errors relating to ActiveX and decided not to pursue it.
I am not convinced that this is a coding problem, however I don't want to overlook the possibility that a solution can be found with different code.
If I look at the facts -
1. Three PC's all failed on the same day, all owned by different people and all in different locations and all with Windows 10 installed.
2. Only one of my four PC's failed using the same code and accessing the same website with the same URL.
3. Two of my PC's have identical configurations, same Windows, Office, Antivirus, IE yet one works and one fails.
4. I went to a friends house and tried it on his PC and it worked (Windows 10)

There has to be a common denominator other than the software mentioned and it must have forced a change that is way to deep for me to even contemplate.

Thanks everyone for your thoughts.

[ExcelWombat]

There is one thing I would like to add to the discussion and my lack of understanding is probably leading me astray when it comes to analysing the problem.
The code fails on the "Send" command and the Access Denied is the result. Where is the access being denied, at the website addressed by the URL or the location of wherever the Send is sending the data. And where is it sending the data anyway? Is this just an issue relating to Administrator Rights?
I tried running Excel as an Administrator but that didn't work either.

Thanks again>

[Kyle123]

Hi,

It can't be all of it, where is selected url defined? What is the URL that fails?

Other contributing factors are whether you are using this inside a loop and the scope of your variables so it would be really useful if you'd post all your code.

What sort of authentication does the site have, if any? Is the site on an intranet

Edit: I've just reread your initial post, you're web scraping so please post your code, what you posted definitely isn't all of it.

[Doc.AElstein]

Hi Wombat,

... and my lack of understanding is probably leading me astray when it comes to analysing the problem.
....The code fails on the "Send" command ... And where is it sending the data anyway? ......

I am following this Fred with interest as I am quite dependant on a scrapping code, and I got caught out when it suddenly stopped working due to a website change**. I only know just what I have learnt here at Excel Forum. Maybe my lack of knowledge can sometimes help get some info across to someone that likewise does not fully understand what is going on:


So
Layman’s explanations: ......( the “MSXML“ or „xmlHTTP stuff“ or „"simple xml request“ stuff )

_ .open : This prepares the request type. Sort of Preparing the Path, telling “the software” ( VBA , or whatever comes out after compiling deep in the innerds of the computer ) that a particular sort of “requests” is coming
I think it is checking that that “path” is valid ( syntaxly ) etc.. etc.. It is following some particular rules, for example:
It will error if your URL does not “look” like a URL , at least from the left hand side. - I note I can add in my code some arbitrary string on the right, like URL & “AnyBloodyCrapHere” – with this abuse my code still works then if my URL is a website like http://www.______________.php. So I could change that to http://www.______________.ploopypooBolloxs and my code still works.
## If on the other hand I use a file which is a .txt or .html File of the Web site source text, like "H:\__________.html" then I cannot put some arbitrary thing on the end like "H:\__________.htmlPloppyPoo" – as this, I find, will error.

In Our codes it is worth noting we are in the “MSXML“ or „xmlHTTP stuff“ or „"simple xml request“ bit . MSXML is a library for creating, reading and manipulating XML “Files”. In other words “getting at XML HTML and the such along with some emphasis on http type web stylio stuff”.
So .open would appear to be checking for a valid „HTML“ type thing. If it „“sees“ a path to a File, all is well and good as long as it sees a complete valid syntaxly Full File Path and File Name. If it sees a http or similar at the start then it would appear at this point to take anything up to the last / in my case. As example: this is the actual URL to a site I scrape-...
http://www.ernaehrung.de/lebensmitte...eischkaese.php
( ha tea tea pea ://www.ernaehrung.de/lebensmittel/de/W233000/Fleischkaese.php )
_....But strangely this will work ( in my browser and my code ) to give exactly the same results
http://www.ernaehrung.de/lebensmitte...llyMakroModPod
( ha tea tea pea ://www.ernaehrung.de/lebensmittel/de/W233000/PloppyPooFukYouFtangFtangOLEWiggyWamDuMoleyWollyMakroModPod )

_..........Clearly there are some rules that the MSXML library knows about ..
_................................................................

_ .setRequestHeader : From what Kyle said in Post #11 , I think I am getting the point now that a request header ( or header of some form ) is probably always sent. And you may need to set / change it. In my codes I seem to be able to arbitrarily include or omit this line, for most* cases, ( both for the case of accessing the web site or a File of the source code ) . I can even muck about and do _...
_ .setRequestHeader bstrheader:="Ploppy", bstrvalue:="Poo"
_.... and most* of my codes still work.
So my codes are mostly* ignoring the .setRequestHeader

So that was my point in Post #9: That point being that maybe for some obscure reason you do unusually need to include it to get the code working on the occasions when your code is not working. That was a wild guess, as in your case I would not have expacted it. But then after that Kyle suggested / supported this maybe a bit , in Post #11, as he said “...parameters sometimes need to be sent in the request header depending on the website (irrelevant of “POST” or “GET”)“….. – I have not encountered that yet with a „GET“ - that was new to me_...
If I use both “POST” and an argument with the .send , then I do need it.... more to all that later_..
Possibly someone who knows what they are doing could supply us with an arsenal of the two arguments in .setRequestHeader and you could try them out to see if that cures the problem. But I may be talking bollox here – I have this
http://i.imgur.com/r1Ie7Le.png
-.. from Kyle, but I cannot see how to get there, that is to say I cannot find that yet in my Browser ( Google Chrome ) development Tools.
http://www.excelforum.com/excel-prog...ml#post4446710

_.............................................................

_ .send : Up until now in the code, before this code point, in Laymen’s terms, we have “done”, I think, nothing in terms of real “work” as it were. We have been , as it were, preparing to do something.
Up until now , The “MSXML - xmlHTTP - simple xml request stuff“ Library has sort of “.opened” up an order form or request or started a new page in an Order Book, and possibly filled “set” Header and maybe some other possibilities we have no idea about.
Now, .send does actually then “send” or make the “request or “do something” or do the requested" rather than just talking and writing / filling in stuff. Now this could then be where you would expect to get a “real error”, as you are doing stuff. So a run time error. . ( see ## _ 2) below for an idea to narrow down your error source a bit )

_.. I am not sure if I am finally answering in a very long round about sort of a way your following question

“....where is it sending the data anyway...”-. _....

_.. How’s this ? : - Think of it as if you are a Physics or Electronic Development Engineer and want to get something.
So to that end, You did the necessary, providing your Sales Department with possibly an authorising signature, and / or a work ticket with details on it of what you want and possibly some instructions on how. etc. That is pseudo you writing the code.
The “MSXML - xmlHTTP - simple xml request stuff“ Library is your Sales Department. Where ( if ) they go ( anywhere ) is determined by them, not you. They might “send” an order form to a Company, ( pseudo the Web site ), or they may go the shed around the back or the Cellar and look for what you want ( pseudo a .txt or a .html File of the web site page source ) .
(At this point I might suggest that the word .open ( with optional .setRequestHeader ) might be better described as “prepare ( make ) the request” and .send might be better described as “do the requested” )
If you use “POST” that might be like giving the Sales Department a Big Folder ( with all sorts of documents and protocols in it). ( You will also have the work ticket or order form in it. )
If you use “GET” you just give them the Work Ticket or order form.
Later when your Sales department are ready to actually do something, you might want them to go in a car so as to tell and supervise exactly how you want your stuff, ( or organise transport to get a large amount of stuff ) or give some extra instructions to say how the thing should be made, which ingredients ( “pseudo” the extra argument that can be included in .send ). It probably does no harm to give him the Folder while the work ticket or order is .“open” for a very simple request. But if you have a more complicated request requiring later instructions or you are wanting a massive amount of stuff like a production run, rather than just some development prototypes and samples, then just giving him the Work ticket or order form will probably not work.
This leads on to the next point
_..........................................................
“POST” versus “GET”
_.. I find that I can use “POST” where I do not need it, ( when I do not include any argument info with the .send, for example ). I cannot do “GET” in those cases. This suggests the idea that “POST” gives more possibilities, but might therefore get hung up due to more complexities compared to the “GET”. If you have a month spare, then you can look through all that a Google search throws up for this Theme. Maybe then you can decipher it into English. I have not got that far yet, especially as some article say very different things. The best initially I have is very crudely:
“GET” is more limited, possibly in some simple request quicker, and like when you put a work ticket or order form in your Out Tray, everyone can see it.
“POST” is less limited both in size and what Options are available. You may be more “hidden” also. ( The POSTman cannot see inside the Folder ) . When I include Data when I “do the required” (like instructions, ingredient, specifications like in my .send “ThisOptionhere” ), then maybe I am POSTing as it were, data to a server, so I need to tell it what format I am sending it in. Hence I need to give the :=”Type of Content” sent in the body of the request, which means in such a case I need the .setRequestHeader in between code line. ( As mentioned it is new to me, ( from Post #11 ) , that I may need a .setRequestHeader in between line for the simpler "GET" case, so I am still a bit curious on this one.. )


So at the end of the day, it might be a bit of ”suck and see” sometimes when deciding which one of these to use. There are too many variables for anyone person to learn and know about

.....There has to be a common denominator ......

_......
_.. maybe there is no common denominator. – possibly there are too many variable for anyone to contemplate or understand. Maybe you just will just need to do this ( pseudo code, - I am not sure of the exact syntax )

On Error Resume Next
Do the Option that works on some computers
If Error Value = 0 Then Go To TurnErrHandlerOff ‘ case no Error as the above first option worked.
Do the Option that works on other computers ‘ case the first option did not work
TurnErrHandlerOff:
On Error GoTo 0



_..................................

Maybe some of all the above makes some sense. It doesn’t just now to me. It sounded OK at the time. Maybe later I will understand a bit from what I have written. Maybe someone will pop by and correct or clarify a few things further..

Alan

P.s., some shots in the dark
** _1) Maybe on 31st August the web site changed a bit, and depending exactly how you access the site ( which could vary a bit with computer type / processing speed etc. ) you may need to change from the True / FalseFalse Option ( I have not covered that yet , maybe inn next post.. ) , or “cover your back” with setting / changing the request header ...

## _2 ) All my scrapping codes work if instead of having a URL like _....
URL = “http://www.______________ .php”
_.... I use the Full Path and File Name of a .txt or .html File that contains the Web site source code, like
URL = “C: \ Desktop \___________ . txt”
It might be an interesting experiment and may reveal something interesting if you make such a file and use that as your URL in your code and try again on each computer.
( To make such a file is easy
_ if you have Google Chrome:
–---- when you are looking at the web site in Google Chrome , --- right mouse click and select something like “source code”, --- copy the whole HTML text to the clipboard, ----- then open Notepad, or a Text editor, or Notepad ++ etc.. , then paste in the HTML text from the clipboard, ----- save that as .txt or.html, ---- then use the Full Path File Name into that File in place of your URL
_ If you have Internet Explorer, .. I am not too sure... maybe
------- click Wheel Top right --- Development Tools --- look for something like a Script tab )

_3) Based on all the above, in the next post I will give some random code alternatives ( ‘ and include some explaining ‘comments ). If, as Kyle suggested, you give your full code, we could go a bit further with for example the .txt and .html file. – or at least try your codes on our computers
(Occasionally I will use named arguments :=__, _____:=____, ____ := ___ as alternative sometimes to ___ , ____, ____ as the name can help to give a bit of info, but there is no particular significance to that )


Alan

Rem References:
‘ http://www.excelforum.com/showthread...54#post4454654
‘ http://www.excelforum.com/excel-prog...te-change.html
‘ http://www.excelforum.com/showthread.php?t=1148621
‘ https://www.youtube.com/watch?v=ivFYVAntpw0

[Doc.AElstein]

Some code alternative ideas based on the ramblings in the last post.
(Occasionally I will use named arguments :=__, _____:=____, ____ := ___ as alternative sometimes to ___ , ____, ____ as the name can help to give a bit of info, but there is no particular significance to that )





Option 1

I read this in a few places_.....
“.....This .open method is synchronous or asynchronous, depending on the value of the varasync:= parameter in the open method call. The True/False argument of the HTTP Request is the Asynchronous mode flag. If set False then control immediately returns to VBA after Send is executed. If set True then control is returned to VBA after the server has sent back a response.....”
I do not know what that bollox means , but maybe :
If .open is called with varasync:=False, this call does not return until the entire response is received or something times out, bombs out , crashes or, whatever....
If .open is called with varasync:= True, this call returns immediately to the code "running" , ( VBA or whatever ? ).
Let’s say some Niggly little thing tries to be done that you do not want or need but never never gets done, ( like someone spying to get all your personal data ). With False you may wait for ever until something times out or hangs up, crashes etc... .
So try a True

‘ 1a)

Please Login or Register  to view this content.

‘ 1 b)

Please Login or Register  to view this content.

‘1c) , ‘1d) maybe try the above two with “GET”

_...........................

Option 2


‘2) just some guesses with a .setRequestHeader
‘2a)

Please Login or Register  to view this content.

‘2b)

Please Login or Register  to view this content.

‘2c) ‘2d) etc...etc.... varasync:=False in the above two and then variations of the While and Wait stuff......

_......................



Option 3 .....
_..
I notice in Post #1 you use MSXML2.xmlhttp and in Post #6 MSXML2.ServerXMLHTTP
Trying to understand from a google search the difference in those does my head in. But they do mention that MSXML2.ServerXMLHTTP is better for between servers but that it does not have some auto recognising stuff. I notice that trying to use my .html Full path and File name String idea instead of the Web site http URL dies not work with .ServerXMLHTTP _... so maybe it does not “recognise” my .html File and only has “eyes” for a Web site URL. Laymen thought is that it is more “narrow minded”, and does not get distracted and less susceptible to missing s sss.. sss sir security , bit I have not the slightest idea what I am talking about there,.... - I just dreamt It up.

Option 4 .....
_.. from last post # 15

Pseudo code: !!!

Please Login or Register  to view this content.

Alan

P.s. A last “DE.request” from me @ ExcelWombat, .. maybe you might want to change your Thread title to
“Web Scrapping . Error - 2147024891 Access Denied”. This might catch the attention of some other Web experts here that may have some input
http://www.excelforum.com/showthread.php?t=1125967

[ExcelWombat]

Alan, Thanks for the explanation, it has certainly cleared up a few things in my mind and will help me to understand the nature of the problem. I still have not found a solution but have given up trying. I have been spending time working on code that does work but even this has proven to be unreliable. The URL that I am scraping is present with 20 items on each page and there are multiple pages. What I am finding is that it randomly misses some pages. On one run it will miss page 3 and then on the next it will miss page 5. I am still experimenting with various loops and status checks but have not found a solution. Your explanation above has been a great help.

[Doc.AElstein]

Alan, Thanks ....

Hi Wombat
You is welcome, and thanks for the feedback, always appreciated
I still think if you could show us the full code it would be useful. I expect kyle would probably have a ggod idea as to the problem if he had the whole code.

As for missing pages .. I do not know if that has anthing with the Token thing.. I never really undertood what that was about, - it came up in another Scrapping Thread just now
http://www.excelforum.com/showthread...t=#post4477194

Alan

[ExcelWombat]

I have given up on the original problem and have copied some code I found that is successful most of the time. I say most of the time because on occasions not all of the data is retrieved from the website correctly even though the ready state and status would indicate that it has been successful. This appears to be random so I just check the data and it does not contain the data I expect then I repeat the code and so far the second attempt has been successful. I have tried Post as well as Get, also inserted some time delays and lastly a check on the status at each step. The code will occasional fail on the first pass but be successful on the second, crude, yes! but at this stage I don't really care.

Please Login or Register  to view this content.

This is what I ended up doing if anyone has the same issues. Thank you all for your patience and help.
I apologise for my reluctance to post all of the code. I have a matter of commercial confidence to deal with and while my code is not doing anything illegal or violating any copyrights I would still prefer to keep it private.

[Kyle123]

It's entirely possible that this is designed behaviour by the website to prevent this type of automated scraping

in your above code the loops do nothing, they're only used when calling the service asynchronously, which you aren't. To check if it's successful you'd be better checking the status code rather than error state, but it's not likely to be much help.

[Doc.AElstein]

Duplicate Post

[Doc.AElstein]

Hello Kyle

It's entirely possible that this is designed behaviour by the website to prevent this type of automated scraping

_.. Interesting. Because....I am using the codes like what you gave me to Scrap a massive data base ( of Food products and their many Nutritional values ) . It is occasionally updated and I fill in a List ( Diet Protocol ) of mine as and when I use / include the products in that list ( Protocol ). Now I was considering writing a Program and letting it run over night ( or a week probably with my slow computer ) to bring out all that data and have it in a massive file and so be done with it, more or less....

_.. Questions ( black and white )

_1 ) Do you think if I did that “automated” web scraping, and the Site owners noticed ( if they can ? ) , they could do something to make the code error like in WomBat’s case, possibly. Or worse they could “Ban my IP”.
( BTW. The info is all free manufactures info, freely available, but not in such a nice complete list as in that Web site. – so the Web site owners might want to encourage one to “visit” for every product, so as to see all their sponsoring adds )

_2) I was thinking of adding a few seconds ( the actual number of seconds randomly generated ) between all runs of the code, so as maybe to avoid a built in software thing detecting a program which would normally run at more consistent intervals..
Does that sound like a good idea..and / or can you think of anything else to make it look less like I am “automating”, as it were..

_._______________-

..
_....in your above code the loops do nothing, they're only used when calling the service asynchronously, which you aren't. To check if it's successful you'd be better checking the status code rather than error state, but it's not likely to be much help.

OK, I fear I may be not equipped to understand this, but I thought one try will at least give you the chance to swear at me
From my Post #16 above:
http://www.excelforum.com/excel-prog...ml#post4475912

_1)
Synchronously
varasync:=False
„….If set False then control immediately returns to VBA after Send is executed ….“

-2)Asynchronously
varasync:= True
“....If set True then control is returned to VBA after the server has sent back a response........“

?? Please do not be too mad with me but it makes no sense to me. Both could be interoperated as the same .. or similar..

_1) after Send is executed ?? So does that mean I might not yet have , for example, my response text back, but I go on anyway. So Wombat’s idea of adding a Waiting a bit makes sense, ( to me ! ) but you do not agree?? Why ??

_2) That sound as though I am waiting anyway, “.....until the server has sent back a response....” So this suggest to an idiot like me that that a next code line(s) like
While .readyState <> 4: DoEvents: Wend
will not be done until the server has sent back a response.
So after I get a response I go to a code line saying “if you have not got a response then “ Do Events “... anyway ??

Or

somehow that code line(s) is done before the server has sent back a response in which case that code line(s) makes _2) do what _ 1 ) does.

I know I am talking Bolloxs. But is it possible to explain what is going on here in something approaching English.
If not, have a rant again.
I love it when you talk dirty to me

x

Alan