Scraping web [VBA] xmlhttprequest

Question

I am stuck with this. I am trying to learn how to use xmlhttprequest with VBA. My intention is to access the following url: "https://micuil.net/".

Once there, I can send values to the following fields, as seen in the image: image1

After pressing the button, the page displays the following information with the data I want to extract: image2 (return value)

I am able to complete what you see in image 1 by code, but I don't know how to get the result (image 2). Any help please?

    Function CuitEstimado2(sDni As Variant, sSexo As String) As String
    Set oDoc = New HTMLDocument
    Set oHTTP = New XMLHTTP60
    sSexo = IIf(sSexo = "f", "Mujeres", "Varones")
    sUrl = "https://micuil.net/"
    
    oHTTP.Open "GET", sUrl, False
    oHTTP.send
    sRespuesta = oHTTP.responseText
    oDoc.body.innerHTML = sRespuesta
    
    oDoc.getElementById("dni").Value = sDni
    oDoc.getElementsByName("sexo")(0).setAttribute("checked") = True
    oDoc.getElementById("btn").parentElement.Click
    End Function

It is not possible to interact with xhr. No JS is executed and values that a human enters in input fields must be passed as parameters. For GET queries in the URL, for POST queries as payload in the HTTP header. I haven't looked at how this works for the page. Because when doing a test run with the specified no. and submitting via the submit button, the expected result is displayed, but no network traffic takes place, as far as I could understand. A JS is executed in the browser. Could it be that it is a conversion of the entered number that is taking place locally? — Zwenn
– Zwenn, Commented Aug 26, 2022 at 22:19
This page calculate the CUIT from the DNI. I understand that the CUIT is similar to the TIN of other countries (it is a tax identification number, which is obtained from a personal identification number) — core335
– core335, Commented Aug 27, 2022 at 10:18
Unfortunately, I have no idea about such numbers. But if there is a conversion rule, then it makes sense to try to figure it out and then write a VBA function that applies it to the known numbers. That would be much faster and all the web scraping would be eliminated. — Zwenn
– Zwenn, Commented Aug 27, 2022 at 12:03
Yes! I actually have a function that estimates that number. This is just to try to learn how xmlhttprequest works. Thanks for your help — core335
– core335, Commented Aug 27, 2022 at 16:05

Infrequent Coder · Accepted Answer · 2022-08-27 06:19:41Z

1

Without having done heavy research for the specific website you are using, it may still be possible that the result you are looking for can be found within the response text (assuming I am understanding your dilemma properly).

First, it is recommended to perform a loop through the innerHTML of each element contained in the HTMLDoc. During your loop, use the InStr function to locate the result code as a string. It is a good idea to store each element that contains that result code into a collection for easy access after the loop.

It does get a bit more complicated from here, because the innerHTML of the corresponding elements may differ when pasting them into Notepad vs. trying to utilize in the VBE. However, if you can identify any unique JS (or other language) characters that will consistently indicate the location of the result code for each request, you may be able to use the Mid function to return the desired result into a string variable.

answered Aug 27, 2022 at 6:19

Infrequent Coder

1095 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

core335 Over a year ago

thanks for the answer! I see that the result is saved in the following element: id="resultadoCUIL". But after execute the code above, i try odoc.getElementById("resultCUIL").innerText and returns null :(

Zwenn Over a year ago

@core335 As I wrote above. It is not possible to interact with the response from an xhr query. It is not possible to click a button, it is not possible to execute Java Script and it is certainly not possible to trigger a subsequent query. With xhr only a single file can be loaded from a web server.

core335 Over a year ago

Thank you Zwenn, now it's clear to me

Infrequent Coder Over a year ago

@zwenn If a loop is performed over all of the HTMLDoc elements for each run of the macro, performance will be rather slow. However, once locating the proper element, if certain identifying characters are consistent within the response text, it still seems possible to manipulate at some level with strings/scripting text files. The result may just be a bit clunky. The difference seems that "getElementById" will not be reliable and a loop must be run for every instance of the macro.

Infrequent Coder Over a year ago

For the loop, you would be using InStr function to locate the identifying characters that surround the result, rather than the actual result. Then use mid once more on that substring to extract the result.

|

Collectives™ on Stack Overflow

Scraping web [VBA] xmlhttprequest

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related