Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA Code Efficiency Needed 1

Status
Not open for further replies.
DrMingle

DrMingle

Technical User
May 24, 2009
116
US
Can anyone help make this code more efficient?

The code works fine...would be possible to launch all seven websites at once instead of succession...?

Sub SpeedSearch()

Dim objIERev
Dim objIEWP
Dim objIEMD
Dim objIEGov
Dim objIEFSMB
Dim objIEGoogle
Dim objIENPI

'************Quotation
Dim myCell As Range
'************

Set objIERev = CreateObject("InternetExplorer.Application")
objIERev.Visible = False
Set objIEWP = CreateObject("InternetExplorer.Application")
objIEWP.Visible = False
Set objIEMD = CreateObject("InternetExplorer.Application")
objIEMD.Visible = False
Set objIEGov = CreateObject("InternetExplorer.Application")
objIEGov.Visible = False
Set objIEFSMB = CreateObject("InternetExplorer.Application")
objIEFSMB.Visible = False
Set objIEGoogle = CreateObject("InternetExplorer.Application")
objIEGoogle.Visible = False
Set objIENPI = CreateObject("InternetExplorer.Application")
objIENPI.Visible = False

'************************Application Activate
Set WshShell = CreateObject("WScript.Shell")
'******************************************

'Whitepages.com
objIEWP.navigate2 "objIEWP.navigate2 "objIEWP.Visible = True
Application.Wait (Now + TimeValue("0:00:5"))
While objIEWP.busy
Wend
objIEWP.Visible = True
objIEWP.Visible = True

WshShell.AppActivate "Name or Category"

Name = Range("I8")
WshShell.SendKeys Name

'******************last name

WshShell.SendKeys "{TAB}"
Name = Range("I9")
WshShell.SendKeys Name

'**************zip
WshShell.SendKeys "{TAB}"
Name = Range("I14")
WshShell.SendKeys Name

WshShell.SendKeys "{Enter}"
'**************

'Medicare.gov

objIEGov.navigate2 "objIEGov.Visible = True
Application.Wait (Now + TimeValue("0:00:6"))
While objIEGov.busy
Wend
objIEGov.Visible = True
objIEGov.Visible = True

WshShell.AppActivate "Name or Category"
Application.Wait (Now + TimeValue("0:00:6"))
'***************Zip
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Enter}"
Application.Wait (Now + TimeValue("0:00:6"))

'******************Physician
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Enter}"
WshShell.AppActivate "Name or Category"
Application.Wait (Now + TimeValue("0:00:6"))

'************************Zip Search
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "%(L)"
WshShell.SendKeys "{Tab}"
WshShell.AppActivate "Name or Category"
Name = Range("I9")
WshShell.SendKeys Name



'Google.com
objIEGoogle.navigate2 "objIEGoogle.Visible = True
Application.Wait (Now + TimeValue("0:00:6"))
While objIEGoogle.busy
Wend
objIEGoogle.Visible = True
objIEGoogle.Visible = True

'****************Google Quotes*****************Sub AddQuote()
For Each myCell In Range("J8")
Range("J8") = Range("I8") & " " & Range("I9")
If myCell.Value <> " " Then
myCell.Value = Chr(34) & myCell.Value & Chr(34)
End If
Next myCell
'**********************************************

WshShell.AppActivate "Name or Category"

Name = Range("J8") & " " & Range("I15") & " " & Range("I13")
WshShell.SendKeys Name

WshShell.SendKeys "{Enter}"

'************************

'WebMD.com
objIEMD.navigate2 "objIEMD.Visible = True
Application.Wait (Now + TimeValue("0:00:6"))
While objIEMD.busy
Wend
objIEMD.Visible = True
objIEMD.Visible = True

WshShell.AppActivate "Name or Category"
Application.Wait (Now + TimeValue("0:00:6"))

'****************Zip
WshShell.AppActivate "Name or Category"
Name = Range("I14")
WshShell.SendKeys Name
'****************Last Name
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
Name = Range("I9")
WshShell.SendKeys Name

WshShell.SendKeys "{Enter}"

'FSMB.org
objIEFSMB.navigate2 "objIEFSMB.Visible = True

'Revolutionhealth.com
'*******************Rev Search
objIERev.navigate2 "objIERev.Visible = True
Application.Wait (Now + TimeValue("0:00:6"))
While objIERev.busy
Wend
objIERev.Visible = True
objIERev.Visible = True

WshShell.AppActivate "Name or Category"
Application.Wait (Now + TimeValue("0:00:5"))
'***************Zip
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.AppActivate "Name or Category"

Name = Range("I14")
WshShell.SendKeys Name
'*********************100miles
WshShell.SendKeys "{Tab}"
Name = 11
WshShell.SendKeys Name

'****************Last Name
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
Name = Range("I9")
WshShell.SendKeys Name

WshShell.SendKeys "{Enter}"
'************

'NPI Registry
objIENPI.navigate2 "objIENPI.Visible = True
Application.Wait (Now + TimeValue("0:00:6"))
While objIENPI.busy
Wend
objIENPI.Visible = True
objIENPI.Visible = True

WshShell.AppActivate "Name or Category"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Enter}"
Application.Wait (Now + TimeValue("0:00:6"))
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
WshShell.SendKeys "{Tab}"
'**************NPI Number
Application.Wait (Now + TimeValue("0:00:6"))
Name = Range("I10")
WshShell.SendKeys Name
WshShell.SendKeys "{Enter}"
End Sub
 
Sort by date Sort by votes
Yes, switch to IE7 or IE8 as was suggested in the previous thread. VBScript processes a script line by line, so you won't get them all to open at exactly the same time. Even in IE7 there would be a delay as each tab was opened.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Unfortunately...I have to use what I am given. Corporate will not allows for any downloads...

I have to try and make do with what I have...

By the way Mark, if it weren't for your insight on multiple post I wouldn't have gotten as far as I have

Thanks...

 
Upvote 0 Downvote
Happy to assist.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.
 
Upvote 0 Downvote
Corporate are idiots IMHO, IE7 is a critical update and will allow you to be more efficient and productive, instead they prefer you to waste hours and try and write something to improve your efficiency.
I could not help but laugh when I saw the bottom of Mark's post: Work SMARTER not HARDER. There you go, good advice - now sell it to the corporate idiots.
 
Upvote 0 Downvote
interesting initial question DrMingle, i think the answer lies in your implementation though. seeing as you are using SendKeys then you wouldnt want them all the process literally at once, as your sendkeys would go flying off in all directions?

the subject line contains 'Efficiency', seeing as vbscript could been seen as processing line by line, and hence you need to therefore hit each site in succession,,,,you therefore can re-use the same IE session, i.e. no need for all your 'ugly'

Set objIERev = CreateObject("InternetExplorer.Application")
objIERev.Visible = False
Set objIEWP = CreateObject("InternetExplorer.Application")
objIEWP.Visible = False
Set objIEMD = CreateObject("InternetExplorer.Application")
objIEMD.Visible = False
Set objIEGov = CreateObject("InternetExplorer.Application")
objIEGov.Visible = False
Set objIEFSMB = CreateObject("InternetExplorer.Application")
objIEFSMB.Visible = False
Set objIEGoogle = CreateObject("InternetExplorer.Application")
objIEGoogle.Visible = False
Set objIENPI = CreateObject("InternetExplorer.Application")
objIENPI.Visible = False

one IE session will do.

.SendKeys is vbscripts fashion polices 'red alert' key word. i would strive to have it your personal ambition to remove all such method calls from your scripts. :) on a more constructive note some websites can be 'automated' by finding out what the user input actually does, e.g. for google putting the word 'test' and hitting 'search' is really the as hitting the webiste url direct of " so would have eliminated the need to hit then SendKeys Tab, SendKeys 'test', Send,...etc etc

Good luck
 
Upvote 0 Downvote
Set WshShell = CreateObject("Wscript.Shell")
WshShell.Run("cscript.exe IESession.vbs /url:" /appactivate:"Name or Category" /keystroke:Tab,Tab,Tab,Tab,I4, 1, False)
WshShell.Run("cscript.exe IESession.vbs /url:" /appactivate:"second one" /keystroke:Tab,Tab,Tab,"test",Tab,G8, 1, False)

you would need to move your actual code into a more general script which accepts the parameters you are interested in passing it, you could then launch each one in its own 'thread'.....but then you would run into the .SendKeys issues
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Peterdo
  • Locked
  • Question
Read code
Replies
1
Views
258
strongm
strongm
jtm2020hyo
  • Locked
  • Question
What is wrong with this code?
Replies
3
Views
278
strongm
strongm
Katto
  • Locked
  • Question
Excel VBA functions in VBScript
Replies
4
Views
323
dhookom
dhookom
bdwilcox
  • Locked
  • Question
Script seeing input variable value as blank but sees hard-coded value 4
Replies
6
Views
713
bdwilcox
bdwilcox
Brianfree
  • Locked
  • Question
Windows VBA file
Replies
0
Views
112
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top