Excel VBA to Enter Data Online With IE and Loop Th

问题:

I'm trying to use Excel VBA to pull the info from columns A2-D2 and enter it into the web site and then click the "Next" button. The code below is what I have so far which works fine for entering the info found on row 2 only.

I'm hoping to achieve that IE opens a new window, enters the values in cells A2 through D2, clicks the "Next" button, and then loops to open another new IE window and enters the values in cells A3 through D3 until it hits an empty cell.

Here are the current numbers that I'm using for testing, http://imgur.com/a/88XEF.

Thanks in advance for any suggestions.

Sub FillInternetForm()
  Dim IE As Object
  Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
  IE.Navigate "https://mygift.giftcardmall.com/Card/Login?returnURL=Transactions"
'go to web page listed inside quotes
  IE.Visible = True
  While IE.busy
    DoEvents  'wait until IE is done loading page.
  Wend
'pause if needed
  Application.Wait Now + TimeValue("00:00:02")

  IE.Document.All("CardNumber").Value = ThisWorkbook.Sheets("sheet1").Range("a2")

  IE.Document.All("ExpirationMonth").Value = ThisWorkbook.Sheets("sheet1").Range("b2")

  IE.Document.All("ExpirationYear").Value = ThisWorkbook.Sheets("sheet1").Range("c2")

  IE.Document.All("SecurityCode").Value = ThisWorkbook.Sheets("sheet1").Range("d2")

'presses the next button
Set tags = IE.Document.GetElementsByTagname("Input")
For Each tagx In tags
    If tagx.Value = "Next" Then
        tagx.Click
        Exit For
    End If
Next

End Sub

回答1:

You just need to wrap your code in a loop..

Dim i as integer
i = 2
do while (ThisWorkbook.Sheets("sheet1").cells(i, 1).value <> "")
  'your code from Application.wait line to end of next button click
  i = i + 1
loop

This assumes an empty row can be identified by column A being empty. You could change the condition on the while loop if this assumption is bad

回答2:

sorry had to right a new answer because the formatting was going weird

ok makes sense, you just need to move the start of your loop further up the code so it encases the creation of the IE object and the navigation, you should also close the IE window before opening a new one:

move the chunk:

Dim i as integer
i = 2
do while (ThisWorkbook.Sheets("sheet1").cells(i, 1).value <> "")

right up to the top right after:

Sub FillInternetForm()

Add the following lines after the line "Next" right at the bottom but still enclosed by the loop

IE.Quit

Set IE = Nothing

回答3:

I was able to accomplish what I wanted with the following. Thanks to those that commented.

Sub FillInternetForm()

Range("A2").Select

Do Until IsEmpty(ActiveCell)

  Dim IE As Object
  Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
  IE.Navigate "https://mygift.giftcardmall.com/Card/Login?returnURL=Transactions"
'go to web page listed inside quotes
  IE.Visible = True
  While IE.busy
    DoEvents  'wait until IE is done loading page.
  Wend

'pause if needed
  Application.Wait Now + TimeValue("00:00:02")

  IE.Document.All("CardNumber").Value = ActiveCell.Value

  ActiveCell.Offset(0, 1).Select
  IE.Document.All("ExpirationMonth").Value = ActiveCell.Value

  ActiveCell.Offset(0, 1).Select
  IE.Document.All("ExpirationYear").Value = ActiveCell.Value

  ActiveCell.Offset(0, 1).Select
  IE.Document.All("SecurityCode").Value = ActiveCell.Value

  ActiveCell.Offset(1, -3).Select

'presses the next button
Set tags = IE.Document.GetElementsByTagname("Input")
For Each tagx In tags
    If tagx.Value = "Next" Then
        tagx.Click
        Exit For
    End If
Next

Loop

End Sub