complete web form with excel vba

joppie · Sep 26th 2012

Hi,

I want to complete this web form (http://shipping-container-info…ber/check-digit-validator) in vba, and return the value in a cell next to the container number I will enter in excel. Once a container number is entered in excel the web form must automatically post the result. I just joined the forum although I have searched through all the questions, but they are all linked to websites and using the coding of the particular sites.

Please can somebody help me I have really been trying for 3 days straight now.

Best regards,

Joppie

mohan.pandey87 · Sep 26th 2012

Re: complete web form with excel vba

could you please make it more understandable

joppie · Sep 26th 2012

Re: complete web form with excel vba

Hi Mohan.

I wrote the post so fast and never proof read it sorry.

To explain: for example in column A I want want to have a heading saying containers: Down the rows I want to enter container numbers. In column B it must return the answer for each container number (from the website) in the corresponding row. So it must basically be a live validator.... Or at least after all the container numbers have been entered then I can run the macro to check all the numbers, but checking per cell would be the best.

Do you understand better?

Regards,

Joppie

Quote from mohan.pandey87;630017

could you please make it more understandable

Kyle123 · Sep 26th 2012

Re: complete web form with excel vba

As a UDF:

Code

Public Function ValidContainer(containerNumber As String) As String    
With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "http://shipping-container-info.com/container-number/check-digit-validator", False
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
        .send "containers_list=" & containerNumber
       ValidContainer = Clean(.responsetext, containerNumber)
    End With
End Function




Function Clean(src As String, c As String) As String




With CreateObject("VBScript.Regexp")
    .Global = True
    .ignoreCase = True
    .Pattern = "<h2>[a-z\s,]*</h2>\s<p>[" & c & "\s,]*<"
    Clean = Replace(Replace(Split(.Execute(src)(0), "</h2>")(0), "<h2>", ""), vbLf, "")
End With




End Function

Display More

Called with =ValidContainer(A1) Dragged Down

mohan.pandey87 · Sep 26th 2012

Re: complete web form with excel vba

Hope it helps you.

mohan.pandey87 · Sep 27th 2012

Re: complete web form with excel vba

Quote from mohan.pandey87;630039

Hope it helps you.

In my previous post in the attached file there is a small change
Press Alt + F11 and in VBA Editor in Module "modGetContainerStatus"

Change this line
.Range(.Range("A2"), .Range("A1048575").End(xlUp)).Resize(, 2)

With this one
.range("A1").Range(.Range("A2"), .Range("A59489").End(xlUp)).Resize(, 2)

Kyle123 · Sep 27th 2012

Re: complete web form with excel vba

Mohan, you can send multiple container numbers at the same time if you want to do a batch check, consider:

Code

Public Sub ValidContainer()
    Dim g, s, x As Long
    
    With Sheet1
        g = Application.Transpose(.Range("A2", Sheet1.Cells(.Rows.Count, "A").End(xlUp).Address).Value)
        
        With CreateObject("MSXML2.XMLHTTP")
            .Open "POST", "http://shipping-container-info.com/container-number/check-digit-validator", False
            .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
            .send "containers_list=" & Join(g, ",")
            s = Clean(.responseText)
        End With
        
        For x = LBound(g) To UBound(g)
            .Cells(x + 1, 2).Value = Replace(Replace(Split(Filter(s, g(x))(0), "</h2")(0), "<h2>", ""), vbLf, "")
        Next x
    End With




End Sub
 
Function Clean(src As String) As Variant
Dim g, x As Long, res() As String
 
    With CreateObject("VBScript.Regexp")
        .Global = True
        .ignoreCase = True
        .Pattern = "<h2>[a-z\s]*</h2>\s<p>[a-z0-9\s,]*<"
        Set g = .Execute(src)
        ReDim res(0 To g.Count - 1)
        For x = 0 To g.Count - 1
           res(x) = g(x)
        Next x
        Clean = res
    End With
     
     
End Function

Display More

That way, you only need to call the website once

You might also want to remove the second instancing of ie, it slows your code down

mohan.pandey87 · Sep 27th 2012

Re: complete web form with excel vba

Hi Kyle123

Thanks for the nice code i could never thought about this technique. Thanks for introducing this technique to me.

joppie · Sep 29th 2012

Re: complete web form with excel vba

Hi Guys.

Thank you very much for the help. I really appreciate it, and would never have thought it would be such few lines of coding. I wish I was better in VBA. Kyle123 seeing that you have it really under control, can I ask you maybe just to edit your code a bit to include the following:

I want to go to the same website, but use a different search, this one http://shipping-container-info.com/container-info/

Sample numbers: CRXU1264496, PONU0778190, UXXU2438988

For example if I enter the container numbers in column A, then the results of "check digit", "owner", "track and trace", "container spec", and "specification" must be returned as hyperlinks. Is it possible to have each as a UDF too?

And how would I be able to have a macro for batch processing for this?

Thank you very much for any help in advance, this forum is by far the best on the web!
Cheers!

joppie · Sep 29th 2012

Re: complete web form with excel vba

I just thought about it, and think UDF for each output won't be the best as updating for each container takes ages, and to run 20 containers with 5 updates each will be insane!

complete web form with excel vba

Participate now!

Share