1

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?

Community
  • 1
  • 1
user1242345
  • 67
  • 3
  • 8

1 Answers1

2

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

enter image description here

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

enter image description here

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

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 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. http://pastebin.com/cWrwfKBf – user1242345 Mar 09 '12 at 10:43
  • ok that helps :). Which textbox do you want to write to "feed_name" or "feed_url" – Siddharth Rout Mar 09 '12 at 10:56
  • 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 Mar 09 '12 at 11:04
  • When compiling, it seems to have an error with this line: Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Something about updating for 64-bit systems. – user1242345 Mar 09 '12 at 11:26
  • Or you can replace `Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)` by `Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)` – Siddharth Rout Mar 09 '12 at 11:35
  • again, thanks for your help. I have got this far, but the next step is how do i insert value from Cell A1 into feed name instead of "whatever" and then how do i get it to click button input. Then how do i get it to do it all again but for B1 next? – user1242345 Mar 09 '12 at 11:41
  • @user1242345: I can give you the exact code but at least make an attempt to write one yourself :) If you get stuck then simply post the code that you tried and I will help you with it. Hint: You will have to use a loop to pick up values from the Excel Sheet and update the textbox values in the Webbrowser. I am however updating the code to click on the button shortly. – Siddharth Rout Mar 09 '12 at 11:42
  • Unfortunately this is my first time using VB, so i'm failing at creating this loop.. :( – user1242345 Mar 09 '12 at 12:09
  • Thanks for this, I'm getting an error, Sub or function not defined. on line: Private Sub Wait(ByVal nSec As Long) – user1242345 Mar 09 '12 at 12:34
  • I just tested the code and it works fine on my end. Did you copy the complete code? Did the original code (The first code that I posted) work? – Siddharth Rout Mar 09 '12 at 12:40
  • Yeah I copied the complete code in the second box. Yeah the first one worked for me. See screeny: http://clip2net.com/s/1G77X – user1242345 Mar 09 '12 at 12:45
  • Can you also post the screenshot when you see the error message – Siddharth Rout Mar 09 '12 at 12:50
  • It is not because of that. Ok Do you know how to debug the code? – Siddharth Rout Mar 09 '12 at 12:58
  • Sorry, no. If its any help - when failing it seems to highlight "Sleep" – user1242345 Mar 09 '12 at 13:08
  • Taking Sleep 100 out seems to stop it from failing, but obviously this causes it to reload before it clicks the button... – user1242345 Mar 09 '12 at 13:23
  • Hey Siddharth, that didn't work, it gave the error "Constants, Fixed Length Strings, User Defined Types, and Declare Statements Not allowed as Public members of object modules" I fixed this by inputting the code "Private Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long) " into a module. Ok so now it runs, goes to the page inputs the correct data into the text boxes and then seems to reload the page, nothing is submitted. – user1242345 Mar 09 '12 at 13:35
  • By the way, i tried to fix this by doing: '~~> Click Button `div1 = Document.getElementByClassName("popupRow") WebBrowser1.Document.div1.getElementsByTagName("Input")(3).Click` because i thought that maybe it's getting confused by any other inputs on the page? Reece – user1242345 Mar 09 '12 at 13:42
  • If the website is capturing the 'X' and 'Y' Co-ordinates of the mouse then you will have to use Fiddler(http://en.wikipedia.org/wiki/Fiddler_%28software%29) to check that and then we will have to use a different approach. I have mentioned this behavior in this link (http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards/8846791#8846791) Unfortunately I cannot comment more on this till I see the actual website :( – Siddharth Rout Mar 09 '12 at 13:51
  • I figured this out by taking webbrowser1.navigate URL out of the loop and replacing the click with: `Set tags = WebBrowser1.Document.GetElementsByTagname("Input") For Each tagx In tags If tagx.Value = "add feed" Then tagx.Click End If Next` Thanks for all your help! – user1242345 Mar 09 '12 at 15:55