Create a macro to copy from a cell in excel to a text box in a browser, then click add

Question

I have already created a Macro using a 3rd party application that does what i want 80% of the time. The problem is that i can't leave it running because it sometimes bugs out, or the browser takes a little longer to respond.

I would like to know if i could create what i wanted in the macro manager that comes with excel (VBA).

I want to:

1. copy from A1, go to browser, paste in text box 1 (about half way down the page)
2. Go back to excel, copy from B1, go to browser, paste in text box 2
3. Click button in browser that says "Add"
4. Wait for pop up box (javascript alert) to disapear
5. Rinse and repeat X times, this time from A2 and B2.

Can you help?

Answer 1

user1242345, there are two ways to go about it.

Way 1

You can launch the URL in WebBrowser1 from VBA and then write to the textbox directly using .GetElementByID

For example

WebBrowser1.Document.getElementById("TextBoxName").Value = "Whatever"

Way 2

Use XMLHTTP. This is way much faster than Way 1

If you can share the link then I can give you an exact answer?

FOLLOWUP

Thanks for your response.. i can't give you the link as it's a password protected page. However here is the html for the form i want to paste into if this helps. pastebin.com/cWrwfKBf – user1242345 17 mins ago

Both, i would like to copy from cell A1 and paste into Feed_name, then go back to excel and copy from B1, and paste into feed_url. Thanks for your help i really appreciate it. – user1242345 5 mins ago

I copied the source code in a text file and saved it as Test.Htm on my desktop. Please see the example below on how to write to the first textbox. i am sure you can replicate it for the next ;)

To run this, create a userform in Excel and place the WebBrowser1 control and a CommandButton Control in the form. See Snapshot.

SNAPSHOT 1

Paste this code in the code area of the userform.

Private Sub CommandButton1_Click()
    Dim url As String

    url = "C:\Documents and Settings\Siddharth Rout\Desktop\Test.Htm"

    WebBrowser1.Navigate url
    WaitForWBReady

    WebBrowser1.Document.getElementById("feed-create-feed_name").Value = "Whatever"
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While Timer < nSec
        DoEvents
    Wend
End Sub

Private Sub WaitForWBReady()
    Wait 1
    While WebBrowser1.ReadyState <> 4
        Wait 3
    Wend
End Sub

When you click on the button the text gets auto populated as show below.

SNAPSHOT 2

MORE FOLLOWUP

Unfortunately this is my first time using VB, so i'm failing at creating this loop.. :( – user1242345 2 mins

I usually don't post a solution in such a scenario but advise the Asker to learn VBA but since I have already posted a major chunk so I will finish it for you. But any more questions from you this point onwards has to be followed by the code that you have written yourself :)

UNTESTED

Try this

Private Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long) 

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim url As String
    Dim lastRow As Long

    url = "C:\Documents and Settings\Siddharth Rout\Desktop\Test.Htm"

    '~~> This is the sheet where the values has to be picked up from
    Set ws = Sheets("Sheet1") 

    With ws
        '~~> Get the Last Row in Sheet1
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop through the range
        For i = 1 To lastRow
            '~~> Navigate to the URL
            WebBrowser1.Navigate url
            WaitForWBReady

            '~~> Input Values
            WebBrowser1.Document.getElementById("feed-create-feed_name").Value = .Range("A" & i).Value
            WebBrowser1.Document.getElementById("feed-create-feed_url").Value = .Range("B" & i).Value

            '~~> Click Button
            WebBrowser1.Document.getElementsByTagname("Input")(3).Click

            WaitForWBReady
        Next
    End With
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While Timer < nSec
        DoEvents
        Sleep 100
    Wend
End Sub

Private Sub WaitForWBReady()
    Wait 1
    While WebBrowser1.ReadyState <> 4
        Wait 3
    Wend
End Sub

HTH

Sid