0

I've successfully automated a VBA Excel macro to iterate through a loop and hit a series of URL's to trigger a server-side script - this is simply done with:

myIE.Navigate ("http://someURL.php?VARIABLE=" & var_string)

where var_string is assigned within the loop as it iterates through. Before this, I've cleared cache, cookies and history with:

Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess #

I've tried many #'s including 8,2, 16 etc to see if any of these had an effect (and combination of #'s).

The issue I am having, is that although the entire script SOMETIMES works, if I were to run it a second time the line where I navigate to the URL fails to call the URL even though the domain/URL is fully live and functional. Any other URL I manually type into the IE window works just fine - just not the one I am calling inside the loop. IE is locking me out of that domain temporarily. If I come back to the script a few hours from last running it, it generally works.

Again the domain is functional and script is fine - I verify it all the time with another machine.

It's as if I am setting something environmentally and breaking Internet Explorer within VBA even though the script is absurdly simple.

I've tried CreateObject(), GetObject as well as InternetExplorerMedium for myIE object.

Braiam
  • 1
  • 11
  • 47
  • 78
FullMetalProgrammer
  • 3
  • 2
  • It's possible that "someURL" is blocking you based on too many requests in a given time period. – Tim Williams May 21 '14 at 21:36
  • @TimWilliams Unfortunately not. The code executes in miliseconds, and I own the domain so I've made sure permissions were 755/777 etc as well. I can easily click the URL myself to trigger the code on another machine. It just breaks when VBA executes it and then my entire IE environment on that machine where the macro ran breaks. – FullMetalProgrammer May 21 '14 at 21:46
  • If all you need to do is call the URL to trigger some process on the server then maybe use XMLHTTP instead. Do you need to get a return value? – Tim Williams May 21 '14 at 22:01
  • No need for a return value. It only triggers a script to run, which writes a file to the server. I know if the URL is hit if the file is written. How would I hit the URL with XMLHTTP inside VBA? – FullMetalProgrammer May 21 '14 at 22:27

1 Answers1

1

If all you need is to "touch" that URL for its side effects, you can as well use a XMLHTTP object. In VBA, go to menu Tools, then References and choose Microsoft XML, v6.0. Then:

Dim Request As New XMLHTTP
Request.open "GET", Url & "?VARIABLE=" & var_string, False
Request.send
' Check Request.status, probably for 200

Some notes:

  • You may want to use POST instead of GET, if you're having problems with caching
  • You should pass the data in the POST body, if the server can handle it
  • The value of var_string should be escaped, in this case, URL encoded
  • If you don't want to block waiting for responses, you can make requests asynchronously (True third argument to open)

Following these notes, here's a more elaborate example:

Dim Request As New XMLHTTP
Request.open "POST", Url, True
Dim Handler As New CXMLHTTPHandler
Handler.Initialize Request
Set Request.onreadystatechange = Handler
Request.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"    
Request.send UrlEncode("VARIABLE") & "=" & UrlEncode(var_string)
' This returns immediately, the check must now be done in the Handler

For the missing pieces, here's the code for CXMLHTTPHandler, which I actually found through stackoverflow, and a definition of UrlEncode at stackoverflow.

You should specialize CXMLHTTPHandler for your needs, probably even make it accept an AddressOf a procedure and call it in the actual default procedure. The default procedure should set the m_xmlHttp to Nothing when m_xmlHttp.readyState is 4.

EDIT 1: If your request code is in a loop, you need to break the Dim ... New statements in two, to ensure you're using fresh objects:

Dim Request As XMLHTTP
Set Request = New XMLHTTP
Request.open "POST", Url, True
Dim Handler As CXMLHTTPHandler
Set Handler = New CXMLHTTPHandler
Handler.Initialize Request
Set Request.onreadystatechange = Handler
Request.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"    
Request.send UrlEncode("VARIABLE") & "=" & UrlEncode(var_string)
' This returns immediately, the check must now be done in the Handler
Community
  • 1
  • 1
acelent
  • 7,965
  • 21
  • 39
  • "As New XMLHTTP" is causing weird messages. Is replacing with Set Request = CreateObject("MSXML2.XMLHTTP") then proceeding to Request.open and Request.send satisfactory in this regard? – FullMetalProgrammer May 22 '14 at 04:03
  • If it complains about not knowing about `XMLHTTP`, I'd prefer to add a reference to MSXML (see first paragraph in the answer) than to create it dynamically, but that is "satisfactory", i.e. it works. – acelent May 22 '14 at 13:53
  • If you're having other problems, e.g. having the `Dim` statement inside a loop, then you must split the statement in two: `Dim Request As XMLHTTP: Set Request = New XMLHTTP`; because otherwise, it'll reuse the same object in each iteration (i.e. the variable is initialized only once) rather than creating a new one. – acelent May 22 '14 at 13:54
  • Microsoft XML 6.0 was added as a reference but still caused an error message on the line for 'new XMLHTTP', but the CreateObject (comment 1) does not throw that error. The lines are inside the loop, so I would prefer to get your syntax working rather than the CreateObject method -- the later of which, do I need to kill the object in each iteration? – FullMetalProgrammer May 22 '14 at 15:20
  • You don't need to kill objects, but you need to create new ones. I've updated the answer. – acelent May 22 '14 at 16:01
  • I have the .open and .send methods working on one computer, but .send fails on another even though the references are exactly the same. I am working with the simplified GET version you have above as I only need to touch the URL to let the PHP run before moving onto other things. – FullMetalProgrammer May 23 '14 at 12:59
  • That's the subject of a new question, and you must provide more information, e.g. the error number, description, firewall settings, etc. – acelent May 23 '14 at 23:25