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

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi tightwad,

I'm guessing that the problem is that all the End(xlUp) operations take more time as the size of the worksheet's UsedRange grows. Normally these are very fast operations, but since they are repeated several times and are inside the D loop, the time they take may be significant. It appears that all these operations are inside the D loop unnecessarily--that the number of items in column A is not changing dynamically while the D loop progresses. Assuming this is the case, the following modified version of your code should run much faster, especially as the number of rows on the worksheet and in the downloaded report increases.

Code:
Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie         As Object
Dim Adjust     As Worksheet
Set Adjust = ThisWorkbook.Sheets("Adjust")
t = 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
      Adjust.Range("A19:A30000").ClearContents
    Dim BoxCell   As Range
    Dim QtyCell   As Range
    Dim ModCell   As Range
    Dim D         As Integer
    With Adjust.Range("A65536").End(xlUp)
      Set BoxCell = .Offset(1, 1)
      Set QtyCell = .Offset(1, 2)
      Set ModCell = .Offset(1, 0)
    End With
    
    For D = 1 To 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(BoxCell.Value)
        Set Qty = .document.all.Item(QtyCell.Value)
        If ModCell.Offset(0, 3).Value = True Then
            Box.Click 'checks box of line needing ordered
            Qty.Value = QtyCell.Offset(0, 2).Value 'changes order amount to correct amount per the metrics specified on the lot tab
        End If
        End With
        ModCell.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

Please let me know if this works.

Damon
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609
I won't be able to check this until next monday, when i go through the process again. I do have a question however.

BoxCell and QtyCell values are declared outside of the loop. This means they don't change while the loop is progressing? If this is true, then this won't work, it will change each line to be exactly the same as the first line.
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again tightwad,

Yes, it was not obvious to me that BoxCell and QtyCell should change inside the loop. So rows are being added to the Adjust worksheet inside the loop? If so, are the same number of rows being added each iteration of the loop? And if that is the case they can still be taken outside the loop and replaced by a row counter.

Damon
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609

ADVERTISEMENT

rows are not being added. This could just be my way of making it work, but here is what I have it doing:

Rows 18 to however long it is contain data in various columns. Depending on the column value, I am adjusting a value in a text box, and clicking in a checkbox. Column A is used to mark when a row is finished, which is why I use the EndXLup command to find the next column. The text/check boxes are in numerical order, so one column is used to number the rows, starting with 0 and going as high as I have rows counted.

Row B is the box number for the check box (from 0 to the total # of rows)
Row C is quantity to be typed into the text box
Row D is True/False. If true, I want to check the box for that row, and adjust to quantity.
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609
Damon,

I took your advice, and removed the .end(xlup) part, by replacing with with a set variable and then adding 1 to that variable at the end of each loop. I thought that would increase the speed, but it didn't seem to. Any more thoughts on why it would be faster when shorter?

Code:
Sub ToAdjustorder()
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
    CR = 19
    Dim D
    For D = 1 To t '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("A" & CR).Offset(0, 1).Value)
        Set Qty = .document.all.Item(ThisWorkbook.Sheets("Adjust").Range("A" & CR).Offset(0, 2).Value)
        If ThisWorkbook.Sheets("Adjust").Range("A" & CR).Offset(0, 3).Value = True Then
            box.Click 'checks box of line needing ordered
            Qty.Value = ThisWorkbook.Sheets("Adjust").Range("A" & CR).Offset(0, 4).Value 'changes order amount to correct amount per the metrics specified on the lot tab
        End If
        End With
        ThisWorkbook.Sheets("Adjust").Range("A" & CR).Offset(0, 0).Value = "Done" 'notes that modification has been compeleted
        CR = CR + 1
        t = t - 1
    Next D
    MsgBox ("all rows finished, please check and click 'Update Status' ")
End With
Set ie = Nothing
Application.ScreenUpdating = True
End Sub

*edit* I event tried removing the offsets, and hardcoding the column letter. It didn't make a marked improvement. So far I am at about 2 loops per second, when the # of loops is 4200. This is 35 minutes, which I am not pleased with.
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609

ADVERTISEMENT

Today is the day I finally get to use this again.

So far it is faster, but only because I am not declaring Box and Qty unless the line needs ordered. That make a big difference because in some cases only 1/2 need ordered.

The rest are still taking about .5 seconds per line, and it is definantly in the way it declares Box and Qty and then clicks the box and adjusts the qty. I would love to find a way to speed this up even more, if anyone has any ideas.
 
L

Legacy 98055

Guest
1. Use early binding for your Internet Explorer object. See how I declared ie. You will need to set a reference to "Microsoft Internet Controls" or "Microsoft Browser Helpers".

2. Store a reference once instead of having to grab a reference to a member of a collection many times. See the 'sh' variable and how a reference is obtained to the worksheet, "ThisWorkbook.Sheets("Adjust")", one time, and is then reused throughout the procedure.

3. Where is the variable 't' declared? Make sure you have Option Explicit at the top of your modules and avoid using Variants. If you do not declare your variables, they are variant and result in the same overhead that early binding produces.

4. Disable Excel events that are not needed while running your procedure.

5. Dim D ? You have delcared a variant. Should be Dim D As Long. Do away with CR. Don't need it.

6. You are using a five cell range. Get a reference to the entire range once, and then refer to each item(cell). Offset the entire range.
See ReportRange.

7. Once again, use early binding. Set a reference to Microsoft HTML Object Library. Dim a variable of the type HTMLElementCollection. Instead of creating a variant collection, on the fly, thousands of times; create a collection one time, before your loop, and then use the key to grab each reference. i went under the assumption that your Checkbox was an input element of type="checkbox" and your value destination was an input element of type="text".

8. Step seven does away with the line of code, "With .Document.forms(0)". When you use "With" and a latebound reference, VB maintains this as a variant that must be re-evaluated for every period. In your former case, twice per loop.

viewtopic233530.zip

I don't really understand your For D = 1 To t:Next D Loop??? I just tried to duplicate what your code was doing and had no way of testing this...

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Sub</font> ToAdjustorder()
       Application.ScreenUpdating = <font color="#0000A0">False</font>

       <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> HTMLElementCollection

      <font color="#008000"> 'I dont know what number t can hold so I just chose the largest number available</font>
       <font color="#0000A0">Dim</font> t <font color="#0000A0">As</font> <font color="#0000A0">Double</font>

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

      <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="#0000A0">With</font> ie

              <font color="#008000"> 'change to true to watch</font>
               .Visible = <font color="#0000A0">True</font>
               .Navigate "localintranet"
                   <font color="#0000A0">Do</font> <font color="#0000A0">Until</font> .ReadyState = 4
                       DoEvents
                   <font color="#0000A0">Loop</font>

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

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


           <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

           <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 = .Document.forms(0).getElementsByTagName("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 = 1 <font color="#0000A0">To</font> t

                   <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) = ReportRange(5).Value
                   <font color="#0000A0">End</font> <font color="#0000A0">If</font>

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

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

           MsgBox ("all rows finished, please check and click 'Update Status' ")
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>
       <font color="#0000A0">Set</font> ie = <font color="#0000A0">Nothing</font>

       Application.ScreenUpdating = <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("925200616119562").value=document.all("925200616119562").value.replace(/<br \/>\s\s/g,"");document.all("925200616119562").value=document.all("925200616119562").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("925200616119562").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="925200616119562" wrap="virtual">
Option Explicit

Sub ToAdjustorder()
Application.ScreenUpdating = False

Dim ie As InternetExplorer
Dim sh As Worksheet
Dim HtmlInputElements As HTMLElementCollection

'I dont know what number t can hold so I just chose the largest number available
Dim t As Double

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

'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

With ie

'change to true to watch
.Visible = True
.Navigate "localintranet"
Do Until .ReadyState = 4
DoEvents
Loop

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

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


Dim D As Long
Dim ReportRange As Range

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 = .Document.forms(0).getElementsByTagName("input")

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

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) = ReportRange(5).Value
End If

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

Next D

MsgBox ("all rows finished, please check and click 'Update Status' ")
End With
Set ie = Nothing

Application.ScreenUpdating = True
End Sub</textarea>

viewtopic233530.zip
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609
1. Use early binding for your Internet Explorer object. See how I declared ie. You will need to set a reference to "Microsoft Internet Controls" or "Microsoft Browser Helpers".

2. Store a reference once instead of having to grab a reference to a member of a collection many times. See the 'sh' variable and how a reference is obtained to the worksheet, "ThisWorkbook.Sheets("Adjust")", one time, and is then reused throughout the procedure.

3. Where is the variable 't' declared? Make sure you have Option Explicit at the top of your modules and avoid using Variants. If you do not declare your variables, they are variant and result in the same overhead that early binding produces.

4. Disable Excel events that are not needed while running your procedure.

5. Dim D ? You have delcared a variant. Should be Dim D As Long. Do away with CR. Don't need it.

6. You are using a five cell range. Get a reference to the entire range once, and then refer to each item(cell). Offset the entire range.
See ReportRange.

7. Once again, use early binding. Set a reference to Microsoft HTML Object Library. Dim a variable of the type HTMLElementCollection. Instead of creating a variant collection, on the fly, thousands of times; create a collection one time, before your loop, and then use the key to grab each reference. i went under the assumption that your Checkbox was an input element of type="checkbox" and your value destination was an input element of type="text".

8. Step seven does away with the line of code, "With .Document.forms(0)". When you use "With" and a latebound reference, VB maintains this as a variant that must be re-evaluated for every period. In your former case, twice per loop.

Right_click

Thanks for your advice. I will try to address each item:

1. I still do not fully understand the difference between early and late binding. I will look into that more, the difference I saw from your code is " Dim HtmlInputElements As HTMLElementCollection " which I assume is used to grab the specific html name for the items being interacted with?

2. I was unfamiliar with setting a reference that way, it will be very helpful to me!

3. I missed declaring t. t is the number of rows I am reviewing/modifying. It shouldn't be more than 25,000 ever.

4. What events do I still need to disable? I got Screen Updating and calculation covered.

5. I must admit, on this one I copied another loop I have used, which was a copy of another loop....a bad cycle for sure. CR is used as the starting row. If #6 works, I can do away with it.

6. Iunderstand this idea, but I am confused about how this will work with the HTML of the page...more on that with #7.

7. This is the html code name for the check box I am accessing "chkreleasenew0" and for the text box: "TxtAdjQtyNew0". There is also a radio button, and some hidden boxes on this page. It is this step that really confuses me in the code you wrote.

8. If I do away with the line of code "With .Document.Forms(0)", how does the form get populated?

my workbook has 5 columns that are used at this time. Column B is the check box names, from row 19 to the end of the sheet. Column C is the text box names, from row 19 to the end of the sheet.

I will see if I can get this working, regardless of if I do, I look forward to understanding how this works. Thanks for your help!
 

tightwad

Well-known Member
Joined
Feb 22, 2006
Messages
609
I am getting errors of "User Defined type not Defined" concerning the HTML references, and the "Dim ie as InternetExplorer"
 

Forum statistics

Threads
1,141,626
Messages
5,707,489
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top