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?

Damon Ostrander · Sep 19, 2006

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 · Sep 20, 2006

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 · Sep 20, 2006

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 · Sep 22, 2006

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 · Sep 22, 2006

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 · Sep 25, 2006

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.

Legacy 98055 · Sep 25, 2006

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

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> 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
</td></tr></table><button onclick='document.all("925200616119562").value=document.all("925200616119562").value.replace(/ \s\s/g,"");document.all("925200616119562").value=document.all("925200616119562").value.replace(/ /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 · Sep 25, 2006

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 · Sep 25, 2006

I am getting errors of "User Defined type not Defined" concerning the HTML references, and the "Dim ie as InternetExplorer"

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

tightwad

Well-known Member

Excel Facts

Damon Ostrander

MrExcel MVP

tightwad

Well-known Member

Damon Ostrander

MrExcel MVP

tightwad

Well-known Member

tightwad

Well-known Member

tightwad

Well-known Member

Legacy 98055

Guest

tightwad

Well-known Member

tightwad

Well-known Member

Similar threads

Forum statistics

Share this page

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

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Legacy 98055

Guest

Well-known Member

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