Excel VBA and InternetExplorer causing errors on website

Question

I have some "clever" users who have written an excel spreadsheet that automates some of their tasks on our intranet/timesheet. It seems as if the instance of Internet Explorer that is created is not doing everything I expect a browser to do, e.g. set cookies. Is there a way to detect if a user is hitting a web site with a VBA com object instance of Internet Explorer? I can't keep users from building creative solutions but I want to be able to handle them. thanks

Answer 1

On your server you could try checking the UserAgent string in the RequestHeader. This may let you distinguish between standard IE and Excel VBA - depends how your power users are doing it. You could ask your users to set the UserAgent string they are sending to a known value.

Example:

Dim oHTTP As WinHttp.WinHttpRequest
Set oHTTP = New WinHttp.WinHttpRequest
oHTTP.Open Method:="GET", url:="http://stackoverflow.com/", async:=False
oHTTP.setRequestHeader "User-Agent", "ExcelVBA-UserApp"

Altenatively, Why not set up a separate access URL just for your excel user to use. Or create a simple RESTFul API for them to use. There are may ways this could be done - Try to keep it simple and easy to maintain.

Your "Clever" uses could try posting a question in SO - The functionality does exist to automate IE better than your question suggests.

Answer 2

Hi SWD, A few things come to mind. First off, unless someone is accessing restricted information or web-scraping data you didn't intend to make public, it's generally better to correct the site in such a way that the site works as intended, but the user's can still use VBA to access the site. VBA is an important productivity tool for a lot of businesses and can give people the flexibility to "extend" a site for niche needs that may not be suitable for a development request.

However if you insist on trying to break your user's application:)... Check the DOM's Window.Visible property. This isn't sure-fire, but a lot of web-scrapers are just "roboting" an instance of Internet Explorer invisibly. Also it may not be that cookies aren't being set, they are just be getting deleted on the fly. If you are trying to limit page-hits with cookies, then you should consider the basic truth that any data stored client side is subject to alteration. Try storing such information server-side instead. As mentioned previously, some methods don't submit proper headers, so you can check those, and finally there is the ever popular "Captcha". Of course, none of these methods is fool-proof and they all come with some drawbacks.

All that said, if there is one thing I have learned, it's that people will find a way to do what they perceive to be their job. You can lock down your interface as much as you want, but if a user believes whatever they are doing is necessary for them to do their job, they will be highly motivated to find a workaround to any "fix" you apply. And they will probably even earn praise and recognition for their efforts. The user will keep doing this until you meet the need they believe they have.

If you really want to kill the app, better to identify what need is not being met, and meet it. Managers don't want to have to support and maintain 100 different little applications if they don't have to. If there is a "legitimate" hassle-free way for the need to be met, it will generally be used. Why? Everyone is pressed for time, so the simplest solution wins. If you make your app better than the one you are trying to quash, then why bother with in-house development? A business unit manager will direct their employee back to business pretty quick if given an excuse.