VBA Please help. Is there a way to make a list from data in a Web Form?

[Peter h]

I was just curious if there was a way to automatically make a list (lets just say Sheet1 A1 to lastrow), of items in a "Combobox" type control on a web form? Just as an example, if I'm on the Autotrader website, and I'm selecting what type of cars I want to look at I've got a few controls that allow the website to filter what it shows me. The first combobox style control has a list of all "Makes" ("Chevrolet, Ford, Dodge...etc"). Is it possible for a code to navigate to that url, and put each item in that control into a list?

I've got a code that navigates to the correct url, and is able to make the appropriate selection in the control based on my userform data. My comboboxes in my userform are populated with lists that I made manually to mirror those on the webform, but I'm worried the lists on the web will be changed or updated with new items, and it will throw my entry off until I update my list. So I was hoping that I could add some code that I could easily update my lists every now and then, without having to go to the web form and check each item in each control to make sure it's all the same.

Make any sense? lol. Thanks for any help you guys got for me.

 

[cerfani]

i would update the combobox on the form load event or whatever is equivalent

I assume you can just get the html string and parse the make options. Like a web scraper. You should be able to do it. What is the problem?

 

[Peter h]

i would update the combobox on the form load event or whatever is equivalent

I assume you can just get the html string and parse the make options. Like a web scraper. You should be able to do it. What is the problem?

Well, the problem is that I hadn't even tried to do it yet, and not super familiar with how to do it, so I wasn't even sure if it was possible to do it in the first place. I'm not sure what a web scraper is. I'll do some research, see what I can come up with, and give it a shot. Thanks.

 

[Peter h]

Ok, I did a little research and came up with a code that seems to do what I want (see what happens when you apply yourself? lol.). I do have 1 question though... First here's my code:

Sub skimmerlist()
Dim lrow As Long
Dim IE As InternetExplorerMedium
Dim svalue As String
Dim i As Long
Set IE = New InternetExplorerMedium
lrow = ThisWorkbook.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row + 1
With IE
    .Navigate "[URL]http://miap33wsapx16/asoma/asomaentryform.aspx[/URL]"
    .Visible = False
End With
Do While IE.Busy
    DoEvents
Loop
     
     IE.Document.getElementById("ddlSelection").selectedIndex = 4
     IE.Document.getElementById("ddlSelection").FireEvent ("onchange")
         
    Do
        DoEvents
    Loop While IE.Document.getElementById("Skimmer") Is Nothing
Set sample = IE.Document.getElementById("Skimmer")
For i = 0 To 55
sample.selectedIndex = i
svalue = sample.value
    ThisWorkbook.Sheets("Sheet1").Cells(lrow, 2) = svalue
    lrow = lrow + 1
Next i
End Sub

I put "i = 0 to 55", because I know how many items are in the control, but what would I put if I don't know the number of items? i = 0 To ???

 

[cerfani]

Nice work.
i dont use those html methods too often. I actually prefer to do the httpwebrequest (https://coderwall.com/p/pbxsyw/vba-web-requests) and just get the html as a string and use string functions like InStr and Mid etc. You dont need IE that way.

But what is sample? is that an array? Can you count the items and use that? like do UBound(sample) ??

 

[Peter h]

Nice work.
i dont use those html methods too often. I actually prefer to do the httpwebrequest (https://coderwall.com/p/pbxsyw/vba-web-requests) and just get the html as a string and use string functions like InStr and Mid etc. You dont need IE that way.

But what is sample? is that an array? Can you count the items and use that? like do UBound(sample) ??

You'll have to excuse me. I'm still pretty new to most of this, and all self-taught, so there are still lots of things I don't know about.

I had no idea about the webrequests. I'll have to check that out and see what I think. Thanks for the heads up.
"sample" was just a way of shortening the "IE.Document.getElementById("Skimmer")" so that I didn't have to type it out every time. It's not an array, it's the actual combobox on the web form. And actually "sample" isn't an accurate name for it because for this code I was getting a list of names. I had just been testing the code on a different element, and forgot to change the name.
I'm not really sure how to use UBound. I know I've probably used it, but not exactly sure how to apply it. I'm not familiar with javascript (which is the language the web form is written in), but the control is basically a combobox, and in the javascript code there is a list of "option values" (the items listed that I'm trying to extract).

 

[cerfani]

ok well instead of doing For i = 0 to 55, you can try to use a while loop.

So what would happen if you used i = 56? would it crash? You can actually handle the error and tell your code to go to another line in your code

On Error (do something)

the idea would be to do

pseudocode...

    Dim i as Long
    i = 0
    Do While True
        On Error Goto LoopExit

        'do something with i

        i = i + 1
    Loop
LoopExit:

Error Handling In VBA

 

[Peter h]

ok well instead of doing For i = 0 to 55, you can try to use a while loop.

So what would happen if you used i = 56? would it crash? You can actually handle the error and tell your code to go to another line in your code

On Error (do something)

the idea would be to do

pseudocode...

    Dim i as Long
    i = 0
    Do While True
        On Error Goto LoopExit

        'do something with i

        i = i + 1
    Loop
LoopExit:

Error Handling In VBA

Thanks. I'll give it a try when I go into work. It looks like it should work. Thanks for the help.

 

[Peter h]

ok well instead of doing For i = 0 to 55, you can try to use a while loop.

So what would happen if you used i = 56? would it crash? You can actually handle the error and tell your code to go to another line in your code

On Error (do something)

the idea would be to do

pseudocode...

    Dim i as Long
    i = 0
    Do While True
        On Error Goto LoopExit

        'do something with i

        i = i + 1
    Loop
LoopExit:

Error Handling In VBA

I'll read up more on Error Handling, but I just tried putting a number bigger than what I know is actually there in the element, and it didn't crash. It goes through the loop just fine. So I just set the number higher than I know the list will ever be, and I should be set. I put I = 0 To 100, and it was a bit slow, so I dropped it down to 75, and it works great. This may not be the most efficient way of doing it, but it seems to do exactly as I need. Thanks for the help on this.