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

[tightwad]

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:

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?

 

[Legacy 98055]

You have to set the references I mentioned. If neccesary, download the workbook and check the references dialog.

 

[tightwad]

You have to set the references I mentioned. If neccesary, download the workbook and check the references dialog.

What/Where is the references dialog? I dl'd your sheet, but all i saw was a copy of the code you posted. I apologize for not knowing this stuff better.

Nevermind....I found it with a bit more searching. I thought those controls were specific to my Excel, and not to a specific workbook.

 

[tightwad]

Ok, now the error is on this line:

Set HtmlInputElements = .Document.forms(0).getElementsByTagName("input")

error 13, type mismatch. How do I find the error?

 

[tightwad]

I haven't yet been able to resolve this. I looked at the page using Firefox, and saw that the form is "form7". I never used that name, form(0) always worked right. If I substitute "form7" or "7" for the 0, it gives a different error, just as it woudl if I put any other form number in.

I looked at the help file, but couldn't see what the type mismatch was.

 

[Legacy 98055]

Hi TW. Could you please send me the workbook you are using and the source HTML of your webpage. I don't think I understand your form. I thought it had many elements.

 

[tightwad]

Files sent. I sent 2 emails, one file hadn't been saved and I didn't know what the changes were, so I sent the newer copy.

 

[Legacy 98055]

Tw. I played around with this and it appears that the following is as fast as I can get it. It averaged .0087 seconds per iteration on a total of about 500 items. I did not include the FALSE items that do not place data into the web page form. 4.331 total seconds for the entire Do...Loop segment. Make sure you use this code or a some version of this code. There were several errors in my first example.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>    <font color="#0000A0">Sub</font> ToAdjustorder()
       <font color="#0000A0">Dim</font> ie <font color="#0000A0">As</font> InternetExplorer
       <font color="#0000A0">Dim</font> sh <font color="#0000A0">As</font> Worksheet
       <font color="#0000A0">Dim</font> HtmlInputElements <font color="#0000A0">As</font> IHTMLElementCollection
       <font color="#0000A0">Dim</font> t <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       <font color="#0000A0">Dim</font> D <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
       <font color="#0000A0">Dim</font> ReportRange <font color="#0000A0">As</font> Range

       Application.ScreenUpdating = <font color="#0000A0">False</font>

      <font color="#008000"> 'get a reference to this worksheet ONCE and then reuse it</font>
       <font color="#0000A0">Set</font> sh = ThisWorkbook.Sheets("SOQAdjust by ARS")

      <font color="#008000"> 'checks for valid orders to adjust</font>
       t = sh.Range("C3").Value
       <font color="#0000A0">If</font> t = 0 <font color="#0000A0">Then</font> MsgBox ("No Skus to add"): <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>

       <font color="#0000A0">Set</font> ie = <font color="#0000A0">New</font> InternetExplorer

      <font color="#008000"> 'change to true to watch</font>
       ie.Visible = <font color="#0000A0">True</font>

       ie.Navigate "http://jcpdcmws.jcpenney.com/"
       <font color="#0000A0">Do</font> <font color="#0000A0">Until</font> ie.ReadyState = 4
           DoEvents
       <font color="#0000A0">Loop</font>

      <font color="#008000"> 'This is the page for the orders to be modified</font>
      <font color="#008000"> 'ie.Navigate sh.Range("H4").Value</font>
       <font color="#0000A0">Do</font> <font color="#0000A0">Until</font> ie.ReadyState = 4
           DoEvents
       <font color="#0000A0">Loop</font>

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

       <font color="#0000A0">Set</font> ReportRange = sh.Range(sh.Cells(19, 1), sh.Cells(19, 5))

      <font color="#008000"> 'get a reference to all of your input elements located in form(0) here</font>
       <font color="#0000A0">Set</font> HtmlInputElements = ie.Document.forms(0).tags("input")

      <font color="#008000"> 'sh.Range("C3").Value 'this performs the modification action based on the number of rows in the downloaded report.</font>
       <font color="#0000A0">For</font> D = T <font color="#0000A0">To</font> 1 <font color="#0000A0">Step</font> -1

          <font color="#008000"> 'your webpage only shows 500 lines per page.</font>
          <font color="#008000"> 'where is the code to navigate to other pages?</font>
           <font color="#0000A0">If</font> ReportRange(4).Value = <font color="#0000A0">True</font> <font color="#0000A0">Then</font>
              <font color="#008000"> 'checks box of line needing ordered</font>
               HtmlInputElements(ReportRange(2).Value).Click
              <font color="#008000"> 'changes order amount to correct amount per the metrics specified on the lot tab</font>
               HtmlInputElements(ReportRange(3).Value).Value = ReportRange(5).Value
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>

          <font color="#008000"> 'notes that modification has been compeleted</font>
           ReportRange(1).Value = "Done"
           <font color="#0000A0">Set</font> ReportRange = ReportRange.Offset(1)

       <font color="#0000A0">Next</font> D

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

       <font color="#0000A0">Set</font> ie = <font color="#0000A0">Nothing</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9272006222433812").value=document.all("9272006222433812").value.replace(/<br \/>\s\s/g,"");document.all("9272006222433812").value=document.all("9272006222433812").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9272006222433812").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9272006222433812" wrap="virtual">
Sub ToAdjustorder()
Dim ie As InternetExplorer
Dim sh As Worksheet
Dim HtmlInputElements As IHTMLElementCollection
Dim t As Long
Dim D As Long
Dim ReportRange As Range

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.forms(0).tags("input")

'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
HtmlInputElements(ReportRange(2).Value).Click
'changes order amount to correct amount per the metrics specified on the lot tab
HtmlInputElements(ReportRange(3).Value).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]

Right_Click,

THANKS!!! That is MUCH faster.

Was the key here the HTML elements, and referencing them that way? I am trying to understand why it is faster. Also, what does stepping by a -1 do in reality?

FYI-the reason the page I emailed was 500 lines is that 500 is the max by default. However, I am able to change the default in the URL, I just neglected to do so when I sent the code.

 

[Legacy 98055]

I am trying to understand why it is faster.

Set Box = .document.all.Item(BoxCell.Value)
Set Qty = .document.all.Item(QtyCell.Value)

These two lines were killing you. The syntax is ok but the logic is as such:

Document.FirstChildForm.ParentDocument.AllElements.SpecificElement

You were actually searching through the entire document's many, many tags by referring to the form's parent document. Because your page is very large and contains thousands of elements, it was taking quite a long time. This explains the proportion of time to the number of rows.

What we did was grab a reference to the child input elements of the form one time and reusing this collection and the references it contains throughout the rest of your code.

Also, what does stepping by a -1 do in reality?

For x = 100 To 0 Step -1

Instead of the default of adding 1 to each iteration, it subtracts one. Now I have no clue why i did that. Doesn't really make any sense. You could just as well reverse the loop and do it the other way around.

The other suggestions from my first post made some contribution though they were negligible by comparision to the above changes.

 

[tightwad]

That makes alot of sense, thanks for the detail.