Will a kind soul help to provide a vba solution to insert text into webpage textbox

[API_newnoob]

Dear all, I am trying to input text into a webpage and search and am running into problems.

Below is my code

Sub GetHTMLDocument()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.IHTMLElement

    
    IE.Visible = True
    IE.navigate "http://shopee.sg"
    
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
    
    Set HTMLDoc = IE.document
    Set HTMLInput = HTMLDoc.getElementsByClassName("shopee-searchbar-input__input")
    HTMLInput.Value = "Excel VBA"
    
    
End Sub

Can someone help me please?

 

[API_newnoob]

Dear @Jaafar Tribak ,

I understand you are knowledgeable, will you be able to provide your valuable feedbacks?

 

[API_newnoob]

Dear @Yongle,

Are you able to provide any feedbacks?

 

[Dan_W]

You should add a (0) to the end of the following line, so it reads:

Set HTMLInput = HTMLDoc.getElementsByClassName("shopee-searchbar-input__input")(0)

That should solve the error message, but having just tried your code with that website, there is likely going to be a further problem in trying to automate the process because of a splash screen on the website that interrupts the code.

 

[API_newnoob]

Dear @Dan_W

Thank you very much for the feedback. I tried that variation before and I received an run-time error 91 message.

I forgot to mention i tried several methods and received errors 424, 438, 13 and 91 and some other which i couldnt recall.

 

[Dan_W]

Right, but as I explained in my post - the reason you're getting the errors is because you are coming up against a splash screen that is interrupting the code. Did you see splash screen? Do you know what I am referring to?

Try this:

Sub GetHTMLDocument()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.IHTMLElement

    IE.visible = True
    IE.navigate "http://shopee.sg"
    
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
    
    Set HTMLDoc = IE.document
    Pause 5
    HTMLDoc.getElementsByClassName("shopee-popup__close-btn")(0).Click
    Pause 2
    Set HTMLInput = HTMLDoc.getElementsByClassName("shopee-searchbar-input__input")(0)
    Pause 2
    HTMLInput.Value = "Excel VBA"
    
End Sub
Sub Pause(timePeriod As Long)
    t = Timer
    Do Until Timer > t + timePeriod
        DoEvents
    Loop
End Sub

 

[API_newnoob]

Dear @Dan_W,

Once again, thank you very much.

I am not sure why the splash screen appeared 2 times and disappeared after that.
My guess is that the website pops up the splash screen daily for a number of times and will no longer appear after it reaches the threshold. However, this is my guess and am not sure on how I should verify it.

On my first 2 tries of your code, an error occurred, therefore i modified the code. I added "Dim t As Long" into the sub "Pause". The error was then resolved.

Sub Pause(timePeriod As Long)
Dim t As Long 'newly added, error on my end.
    t = Timer
    Do Until Timer > t + timePeriod
        DoEvents
    Loop
End Sub

The problem occurred later on this line "HTMLDoc.getElementsByClassName("shopee-popup__close-btn")(0).Click". The error message was "91". I realized that the splash screen no longer appeared, and guessed that removing this portion might work and to my surprise, it did work. With that, i figured that "pause" was the answer, which leads to my first question.

1) Why did the pause solved the problem? What is the best time to pause? If the answer on the best time to pause depends on variables, how should i determine it (trial and error and see time works best[shorter is better?])?

2) With regards to my 2nd question, assuming my assumption that the splash screen appears for "x" number of times per day is correct and is causing problems?
Should I or can I write a code (e.g if "shopee-popup__close-btn" gives an error, on error resume next). Is this better?

 

[Dan_W]

I added "Dim t As Long" into the sub "Pause". The error was then resolved.

Excellent - well done. This means that you have "Option Explicit" at the top of your code module. I wasn't aware of that, but it is of course best practice to include that in every code module and to properly dimension any variables used in subroutines, functions. My apologies.

I realized that the splash screen no longer appeared, and guessed that removing this portion might work and to my surprise, it did work. With that, i figured that "pause" was the answer, which leads to my first question.

That is exactly right. If I had more time, a better approach would be first to test whether the popup was present before telling Excel to click on an HTML element that simply didn't exist, thus causing the error. In my testing, though, I noted that the splash screen came up everytime I ran the routine, so I didn't think to test whether or not it was coming up at all - the only difference I noticed each time I ran it, though, was in how long it took for the splash screen to appear, which is why I added a "Pause" sub.

What is the best time to pause? If the answer on the best time to pause depends on variables, how should i determine it (trial and error and see time works best[shorter is better?])?

No idea. I just guessed it at around 5 seconds for the splash screen (after some trial-and-error), thinking perhaps that speed was not a primarily concern for you.

2) With regards to my 2nd question, assuming my assumption that the splash screen appears for "x" number of times per day is correct and is causing problems?
Should I or can I write a code (e.g if "shopee-popup__close-btn" gives an error, on error resume next). Is this better?

On Error Resume Next would be the easy way of bypassing the problem, but you should remember to reset the error handling process with On Error Goto 0 immediately after that. The Resume Next approach may seem like it will solve any and all problems, but it can sometimes cause more problems than it solves. On that, I'd recommend learning about Error Handling. Excel Macro Mastery has a very good guide.

I think the better approach would be: (1) first test whether or not an HTML element with that class name exists in the code; and then (2a) if it does, click on it; or (2b) if it doesn't, just keep doing what you're doing. That would certainly improve the speed of your automation code rather than 'pausing' for an arbitrary number of seconds (which is my lazy approach to things...).

 

[John_w]

Dear all, I am trying to input text into a webpage and search and am running into problems.

You can put the search keyword(s) in the URL rather than the input element:

Public Sub IE_Search()
   
    Dim IE As Object
    Dim searchKeyword As String

    searchKeyword = InputBox("Search keyword")
    If searchKeyword = "" Then Exit Sub
   
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .navigate "https://shopee.sg/search?keyword=" & searchKeyword
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    End With

End Sub