Hello,
After summer of learning excel and VBA I feel like a pseudo guru with macros, TY much to all the boards!
I have a macro that logs into my work site and runs through thousands of rows of customer &/or carrier pages, only thing it is making the click sound when the online form is submitted. It would be fine if only once or twice now and then but this macro runs all the time and I am tired of hearing this CLICK sound when it runs.
I have tried .audible .sound .noise .stopannoyingfu64sound -ok the last wast on a humbug
this one goes to however many pages in column A but when it submits the form and clear or start over it clicks...this is annoying because it will go through many pages in a minute = LOTS OF CLICKING SOUND...and IE is not visible.
Ooo would also like a timer to see how fast it has processed so many rows of data if you know of how.
Any ideas are much appreciated, I have been scouring here and Google.
After summer of learning excel and VBA I feel like a pseudo guru with macros, TY much to all the boards!
I have a macro that logs into my work site and runs through thousands of rows of customer &/or carrier pages, only thing it is making the click sound when the online form is submitted. It would be fine if only once or twice now and then but this macro runs all the time and I am tired of hearing this CLICK sound when it runs.
I have tried .audible .sound .noise .stopannoyingfu64sound -ok the last wast on a humbug
this one goes to however many pages in column A but when it submits the form and clear or start over it clicks...this is annoying because it will go through many pages in a minute = LOTS OF CLICKING SOUND...and IE is not visible.
Code:
Dim ieDoc As Object
Dim ieEle As Object
Set ie = CreateObject("InternetExplorer.Application")
ieDoc.all.UserName.Value = "haha"
ieDoc.all.Password.Value = "hehe"
ieDoc.forms(1).submit 'its the second form
Do Until .ReadyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop
With ie
.Visible = False
'.audible = False
.Navigate url
Do Until .ReadyState = 4: DoEvents: Loop
Set ieDoc = ie.Document
k = 2
laneID = Range("a" & k)
oZip = Range("e" & k)
dZip = Range("h" & k)
puDate = Range("p" & k)
While laneID <> ""
lastrow = ActiveSheet.UsedRange.Rows.Count
Application.StatusBar = " " & k - 1 & " of " & lastrow - 1 & " " & oZip & " - " & dZip
If k Mod 500 = 0 Then
ThisWorkbook.Save
End If
On Error Resume Next
If k Mod 500 = 0 Then
ThisWorkbook.Save
End If
ieDoc.all.originZipCode.Value = oZip
ieDoc.all.destinationZipCode.Value = dZip
ieDoc.all.pickupdate.Value = puDate
ieDoc.all.selectedTimeOfDay.Value = 4
ieDoc.forms(2).submit
Do Until .ReadyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop
'scrape
Set s = ie.Document.getelementsbytagname("TD").Item(73)
txt = s.getAttribute("innertext")
Worksheets("wkst").Range("m" & k).Value = txt
Set s = ie.Document.getelementsbytagname("TD").Item(74)
txt = s.getAttribute("innertext")
Worksheets("wkst").Range("n" & k).Value = txt
Set s = ie.Document.getelementsbytagname("TD").Item(75)
txt = s.getAttribute("innertext")
Worksheets("wkst").Range("o" & k).Value = txt
Set s = ie.Document.getelementsbytagname("TD").Item(76)
txt = s.getAttribute("innertext")
Worksheets("wkst").Range("q" & k).Value = txt
'Reset for Next load
Set ObjCollection = ie.Document.getelementsbytagname("input")
I = 0
While I < ObjCollection.Length
If ObjCollection(I).Value = "Start Over" Then
Set ObjElement = ObjCollection(I)
Else
If ObjCollection(I).Value = "Clear" Then
Set ObjElement = ObjCollection(I)
End If
End If
I = I + 1
Wend
ObjElement.Click
Do Until .ReadyState = 4: DoEvents: Loop
Do While .Busy: DoEvents: Loop
k = k + 1
laneID = Range("a" & k)
oZip = Range("e" & k)
dZip = Range("h" & k)
Wend
Application.StatusBar = ""
ie.QuitOoo would also like a timer to see how fast it has processed so many rows of data if you know of how.
Any ideas are much appreciated, I have been scouring here and Google.
