Beginner question

[ronnyj]

Hello all,
New to this forum. Appreciate the support.

I need some initial help, as I am beginner to VBA scripting in excel.

Could someone provide me a sample code for opening an internet browser, selecting an item from a first drop down, entering a search text and then selecting an item from another drop down, and finally clicking a 'lens' symbol to initiate a search??

regards,
Ronnj

[Blade Hunter]

This is no small feat for a beginner, I commend your enthusiasm tackling this at a beginner level.

In order to do this I have a question.

Is this an external website ie can I get to it?

The reason I ask this is because I will need to open the HTML and search through the elements looking for the things you have mentioned. You will not actually use VBA to Click things and Enter things, you will pass variables in and trigger the event that the click would in the browser.

There are a bunch of ways this can be done, you can read the page into ram and interact with it that way. In one particular project I had that couldn't be done because I couldn't pass in the password so the user had to have a level of interaction to click "Submit" when the password was entered:

Sub ScrapePage()


'This code passes in an industry standard UPC to a partner site and returns the partners unique code    


Dim MyItm As Object, ie As Object, Element As Object
Dim MyVal As Long
Dim strURL As String, strUsername As String, strPassword As String, ContentURL As String
Set ie = CreateObject("InternetExplorer.Application")
strURL = "https://REMOVED/"
strUsername = "REMOVED"
strPassword = "REMOVED"
ie.Navigate strURL
Do While ie.ReadyState <> READYSTATE_COMPLETE 'Wait for the page to load
    Application.Wait Now + TimeValue("0:00:01")
Loop
ie.Visible = True 'Make it visible
On Error GoTo LoggedIn
ContentURL = ie.Document.all("accountname").Value
ie.Document.all("accountname").Value = strUsername
ie.Document.all("accountpassword").Value = strPassword
MsgBox "Please click Sign In then Click OK on this box" 'I couldn't get the element to click OK so prompted the user
LoggedIn:
On Error Resume Next
Do While ie.ReadyState <> READYSTATE_COMPLETE 'Wait for the page to load after OK was clicked
    Application.Wait Now + TimeValue("0:00:01")
Loop
For Each Element In ie.Document.all.tags("A") 'Poll through the elements
    If Element.innerText = "Manage Your Content" Then 'Find the one called Manage Your Content
        ContentURL = Element 'Get the URL for that element
        Exit For
    End If
Next Element
ie.Navigate ContentURL 'Go to that URL (As if someone clicked the button)
Do While ie.ReadyState <> READYSTATE_COMPLETE 'Wait for the page to load
    Application.Wait Now + TimeValue("0:00:01")
Loop
For Each Element In ie.Document.all.tags("A") 'Poll through the elements
    If Element.innerText = Chr(13) & Chr(10) & "Music" Then 'Find the one called Music
        ContentURL = Element 'Get the URL for that element
        Exit For
    End If
Next Element
ie.Navigate ContentURL 'Go to that URL (As if someone clicked the button)
Do While ie.ReadyState <> READYSTATE_COMPLETE 'Wait for the page to load
    Application.Wait Now + TimeValue("0:00:01")
Loop
ie.Document.all("field_upc").Value = "REMOVED" 'Enter the UPC (which I have removed in this sample) into the field field_upc
ie.Document.all("0.9.7.3.1.1.3").Click 'Click the button
Application.Wait Now + TimeValue("0:00:02") 'Wait to let it load
MyVal = 0
For Each MyItm In ie.Document.all.tags("td") 'Read through all the item tags
    If IsNumeric(MyItm.innerText) Then 'If it's numeric
        MyVal = MyVal + 1 'Count how many numerics have occured
        If MyVal = 3 Then 'If it's the third one then it's the one we want
            MsgBox "ID: " & MyItm.innerText 'Prompt the user of the code.
        End If
    End If
Next MyItm
ie.Quit
End Sub

I set this code up so we could scrape the unique ID when we didn't have it. I had another function which would search through our database for the ID, if it wasn't found it would use this code then load it into the database, eventually we would end up with all the unique ID's in our own database. I never ended up releasing it though hence why it just returns the result to a msgbox

I have been through and put comments in there for you, post back if you get confused.

Cheers

Dan

[ronnyj]

Thanks for the reply. This is an internal webpage related to the company I work for. No one can logon from outside, only internal folks can access. It does not ask for password or username. For us it just like a webpage that i can access...typing the web address in teh address bar displays the web page with the radio button, text for search box and the lens icon for clicking on it to begin the search.

[Blade Hunter]

OK When you enter something into a search and click the lense manually, what does the URL change to?

[ronnyj]

First i just enter ababababababab and hit enter
first Drop down, I select AAAA from AAAA,BBBB,CCCC and DDDD
Search box i input "DATABASE" (without quotes)
Second Drop down , I select ZZZZ from XXXX,YYYY,ZZZZ
Then I click on lens icon or just hit enter.
The web page remains same as above ababababababab, but below a list comes up in tabular form with all the hits.

[Blade Hunter]

If it is an internal intranet, I can't help but thinking you would be better off using ADO to go straight to the database for any data you are trying to pull from the site.

What exactly is this data and is it in a database? It would be much cleaner to do that.