Web Form interface.....amount of lines affects speed???

tightwad · Sep 18, 2006

I built a web form interface which I use to adjust orders on a web-based system our company uses.

The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.

Here is the code I use:

Code:

Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie As Object
t = ThisWorkbook.Sheets("Adjust").Range("C3").Value 'checks for valid orders to adjust
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub
Set ie = CreateObject("InternetExplorer.Application")
With ie
        .Visible = True 'change to true to watch
        .Navigate "localintranet"
            Do Until .ReadyState = 4
                DoEvents
            Loop
        .Navigate ThisWorkbook.Sheets("Adjust").Range("H4").Value 'This is the page for the orders to be modified
            Do Until .ReadyState = 4
                DoEvents
            Loop
        ThisWorkbook.Sheets("adjust").Range("A19:A30000").ClearContents
    Dim D
    For D = 1 To ThisWorkbook.Sheets("adjust").Range("C3").Value 'this performs the modificationa action based on the number of rows in the downloaded report.
        With .document.forms(0)
        Set Box = .document.all.Item(ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 1).Value)
        Set Qty = .document.all.Item(ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 2).Value)
        If ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 3).Value = True Then
            Box.Click 'checks box of line needing ordered
            Qty.Value = ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 4).Value 'changes order amount to correct amount per the metrics specified on the lot tab
        End If
        End With
        ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 0).Value = "Done" 'notes that modification has been compeleted
    Next D
    MsgBox ("all rows finished, please check and click 'Update Status' ")
End With
Set ie = Nothing
Application.ScreenUpdating = True
End Sub

This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.

Did I do something wrong?

tightwad · Nov 8, 2006

Another questions arose from this code.

What happens if multiple items on the same page have the same name? I have a page where I have up to 99 boxes I could check, but each shares the same name in the HTML code, and is just differentiated by it's ID and Value. The name for each checkbox is "chkSkuList", the "value" is the actual SKU in question.

Norie · Nov 8, 2006

What should happen when there are multiple items with the same name?

Are you sure these items aren't parts of seperate objects, perhaps tables/forms/frames?

Legacy 98055 · Nov 8, 2006

Untested...

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Option Explicit

 Sub ToAdjustorder()
 Dim ie As InternetExplorer
 Dim sh As Worksheet
 Dim HtmlInputElements As IHTMLElementCollection
 Dim iElement As IHTMLElement
 Dim t As Long
 Dim D As Long
 Dim ReportRange As Range
 Dim c As New Collection

 Application.ScreenUpdating = False

 'get a reference to this worksheet ONCE and then reuse it
 Set sh = ThisWorkbook.Sheets("SOQAdjust by ARS")

 'checks for valid orders to adjust
 t = sh.Range("C3").Value
 If t = 0 Then MsgBox ("No Skus to add"): Exit Sub

 Set ie = New InternetExplorer

 'change to true to watch
 ie.Visible = True

 ie.Navigate "http://jcpdcmws.jcpenney.com/"
 Do Until ie.ReadyState = 4
 DoEvents
 Loop

 'This is the page for the orders to be modified
 'ie.Navigate sh.Range("H4").Value
 Do Until ie.ReadyState = 4
 DoEvents
 Loop

 sh.Range("A19:A30000").ClearContents

 Set ReportRange = sh.Range(sh.Cells(19, 1), sh.Cells(19, 5))

 'get a reference to all of your input elements located in form(0) here
 Set HtmlInputElements = ie.Document.getElementsByName("The name here...")

 For Each iElement In HtmlInputElements
 c.Add iElement, iElement.ID
 Next

 'sh.Range("C3").Value 'this performs the modification action based on the number of rows in the downloaded report.
 For D = t To 1 Step -1

 'your webpage only shows 500 lines per page.
 'where is the code to navigate to other pages?
 If ReportRange(4).Value = True Then
 'checks box of line needing ordered
 c("ID here...").Click
 'changes order amount to correct amount per the metrics specified on the lot tab
 c("ID here...").Value = ReportRange(5).Value
 End If

 'notes that modification has been compeleted
 ReportRange(1).Value = "Done"
 Set ReportRange = ReportRange.Offset(1)

 Next D

 MsgBox ("all rows finished, please check and click 'Update Status' ")

 Set ie = Nothing

 End Sub

</td></tr></table><button onclick='document.all("118200615374498").value=document.all("118200615374498").value.replace(/ \s\s/g,"");document.all("118200615374498").value=document.all("118200615374498").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("118200615374498").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="118200615374498" wrap="virtual">
Option Explicit

Sub ToAdjustorder()
Dim ie As InternetExplorer
Dim sh As Worksheet
Dim HtmlInputElements As IHTMLElementCollection
Dim iElement As IHTMLElement
Dim t As Long
Dim D As Long
Dim ReportRange As Range
Dim c As New Collection

Application.ScreenUpdating = False

'get a reference to this worksheet ONCE and then reuse it
Set sh = ThisWorkbook.Sheets("SOQAdjust by ARS")

'checks for valid orders to adjust
t = sh.Range("C3").Value
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub

Set ie = New InternetExplorer

'change to true to watch
ie.Visible = True

ie.Navigate "http://jcpdcmws.jcpenney.com/"
Do Until ie.ReadyState = 4
DoEvents
Loop

'This is the page for the orders to be modified
'ie.Navigate sh.Range("H4").Value
Do Until ie.ReadyState = 4
DoEvents
Loop

sh.Range("A19:A30000").ClearContents

Set ReportRange = sh.Range(sh.Cells(19, 1), sh.Cells(19, 5))

'get a reference to all of your input elements located in form(0) here
Set HtmlInputElements = ie.Document.getElementsByName("The name here...")

For Each iElement In HtmlInputElements
c.Add iElement, iElement.ID
Next

'sh.Range("C3").Value 'this performs the modification action based on the number of rows in the downloaded report.
For D = t To 1 Step -1

'your webpage only shows 500 lines per page.
'where is the code to navigate to other pages?
If ReportRange(4).Value = True Then
'checks box of line needing ordered
c("ID here...").Click
'changes order amount to correct amount per the metrics specified on the lot tab
c("ID here...").Value = ReportRange(5).Value
End If

'notes that modification has been compeleted
ReportRange(1).Value = "Done"
Set ReportRange = ReportRange.Offset(1)

Next D

MsgBox ("all rows finished, please check and click 'Update Status' ")

Set ie = Nothing

End Sub
</textarea>

tightwad · Nov 8, 2006

Run-Time error '457':

This key is already associated with an element of this collection

I read the help section, but I didn't understand what exactly was being identified. It happens after 9 loops through.

Legacy 98055 · Nov 8, 2006

It appears that the ID's are not unique either.

Norie · Nov 8, 2006

tightwad

Is it possible you could explain why this is anything to do with Excel?

tightwad · Nov 13, 2006

Norie,

I am using Excel to automate the work being done on the Web Page. So far I have adapted this same Macro for use in 3 systems, it has been great!

Web Form interface.....amount of lines affects speed???

tightwad

Well-known Member

tightwad

Well-known Member

Excel Facts

Norie

Well-known Member

Legacy 98055

Guest

tightwad

Well-known Member

Legacy 98055

Guest

Norie

Well-known Member

tightwad

Well-known Member

Similar threads

Forum statistics

Share this page

Web Form interface.....amount of lines affects speed???

tightwad

Well-known Member

tightwad

Well-known Member

Excel Facts

Norie

Well-known Member

Legacy 98055

Guest

tightwad

Well-known Member

Legacy 98055

Guest

Norie

Well-known Member

tightwad

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock