Excel VBA: unable to access web controls using MSXML2.XMLHTTP

[somashekhar]

hi friends,

this is my first post here .

i am working on a small excel automation to login to a web portal with Baisc Authentication and generate excel report by selecting appropriate values in drop-box .

though i could login with Basic Authentication using MSXML2.XMLHTTP object, i am unable to access the web form controls in the code.

Set objRequest = New MSXML2.XMLHTTP
With objRequest
  .Open "POST", "http://XXXXXXXXXX?", False, "username", "pwd"
  .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  .send
   htfile = .responseText
   
End With

after this login, i am unable to retrieve the information/status of the web page controls [drop down box] and i am unable to assign the required values to it.

can anybody please help me understand how can i access the web page elements after login using above method!?

 

[somashekhar]

hi there, can anybody help me in this regard? i am unable to use internet explorer object either after the log on.

 

[John_w]

Assign the XMLHTTP ResponseText to a HTMLDocument object inside the With block:

Dim HTMLdoc As HTMLDocument
Set HTMLdoc = New HTMLDocument
HTMLdoc.body.innerHTML = .responseText

and go from there - without knowing the URL it's difficult to give specific help. NB the above code requires a reference to MS HTML Object Library in Tools - References in the VBA project.

 

[somashekhar]

hi John,
thanks for the reply. i have the references set in library. i can also see .responsetext [that's how i could make out the login was successful]. but after assigning .responsetext to variable [htfile is a htmldoc object - my apologies for not updating complete code] i could not access the drop down box on the page [i checked the dropdownbox control name in the html code of the page] to retrieve current status or assign the value.
PS: can't name the actual portal name as it is one of the client site.

Public Sub subx()

Dim strPostData As String
Dim objRequest As Object
Dim htfile As HTMLDocument


Set objRequest = New MSXML2.XMLHTTP
With objRequest
.Open "POST", "http://xxxxxxx.com/reports/", False, "login", "pwd"
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send
htfile = .responseText

End With

here is the part of source code of the page [i have renamed the controls]. i want to access the drop down control and select the option before generating the report.


________

     <td width="20%" align="right">      Axxx    </td><td align="left"><input type="hidden" value="agency_id" name="param_in"></input><select onchange="runRefresh('1')" value="agency_id" name="value_in"><option value="131">Company1    </option><option value="824">Company2    </option><option value="827">Company3    </option><option value="957">Company4    </option></select></td>

PS: i am unable to post the html code as is.. it is resolving into drop down box

 

[somashekhar]

how can i assign and retrieve value of 'agency_id'?

following code is not working for getting value out from 'agency_id'

idval = htfile.getElementsByID(agency_id)

also i am not aware how to assign the value into it.

 

[John_w]

You're assigning responseText to htfile incorrectly. Look at my code carefully and compare it with your code.

Sorry, but I can't really help you further without access to the site.

 

[somashekhar]

hi John,
i have corrected my code after your first post. i was looking for ways to retrieve/assign value to dropdownbox options.

and yeah i can understand it would be difficult to assist without access. sorry there! and thanks for the help !