8
\$\begingroup\$

I am looking to speed up the exchange from vba to IE. The sendkeys works, but I was curious if there were a better way to do this?

The site that it routes to is a form, but there is no submit button. The only way to pull the data is to tab to the next box or click somewhere on the screen. I was hoping, however, to have all of this automated through VBA.

Thoughts?

Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long

Function FillInternetForm()
Dim HWNDSrc As Long
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
    'create new instance of IE. use reference to return current open IE if
    'you want to use open IE window. Easiest way I know of is via title bar.

HWNDSrc = ie.HWND

ie.Navigate "http://helppointinfo.farmersinsurance.com/OCR/Labor_Rates/laborrates.asp"
    'go to web page listed inside quotes
ie.Visible = True
While ie.Busy
    DoEvents  'wait until IE is done loading page.
  Wend
ie.Document.getElementById("DirectZip").Value = Sheets("NAT").Range("C2").Value

SetForegroundWindow HWNDSrc

Application.SendKeys "{TAB 11}", True
DoEvents
Application.SendKeys "{NUMLOCK}", True

End Function

Public Sub RunRates()

Call FillInternetForm

End Sub
\$\endgroup\$
7
\$\begingroup\$

Just reviewing what you've got here...

Indentation

The code would read much better with proper indentation:

Function FillInternetForm()
    Dim HWNDSrc As Long
    Dim ie As Object

    Set ie = CreateObject("InternetExplorer.Application")
    HWNDSrc = ie.HWND

    ie.Navigate "http://helppointinfo.farmersinsurance.com/OCR/Labor_Rates/laborrates.asp"
    ie.Visible = True
    While ie.Busy
        DoEvents  'wait until IE is done loading page.
    Wend
    ie.Document.getElementById("DirectZip").Value = Sheets("NAT").Range("C2").Value

    SetForegroundWindow HWNDSrc

    Application.SendKeys "{TAB 11}", True
    DoEvents
    Application.SendKeys "{NUMLOCK}", True

End Function

Public Sub RunRates()
    Call FillInternetForm
End Sub

Call Instruction

As answered in this StackOverflow question, the Call instrucation is a relic from ancient versions of VB, it's not needed and, IMO, only adds clutter.

Public Sub RunRates()
    FillInternetForm
End Sub

Coupling

The FillInternetForm function is needlessly coupled with the Excel object model - Sheets("NAT").Range("C2").Value should be passed as a String parameter to the function:

Function FillInternetForm(ByVal DirectZipValue As String)
    '...
    ie.Document.getElementById("DirectZip").Value = DirectZipValue
    '...
End Function

Public Sub RunRates()
    FillInternetForm Sheets("NAT").Range("C2").Value
End Sub

Function?

VB functions are procedures with a return value. If it's not specified, then it's returning a Variant - here FillInternetForm is never assigned a return value, and whatever it would be returning wouldn't be used. In other words, you have a procedure (Sub), not a function. The signature should be modified like this:

Public Sub FillInternetForm(ByVal DirectZipValue As String)

I like things explicit - if a member is going to be Private, it needs a Private access modifier; if it's going to be Public, I don't like relying on VB's "defaults", mostly because I code in different languages where these defaults differ (C#). Having explicit access modifiers eliminate the possible confusion, but that might be only me.

Lastly, I don't understand why FillInternetForm would have to press NUM LOCK, this looks misplaced, and has a side-effect that could be surprising to whoever is running that code.

\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you for the response - haven't gotten a chance, as of yet, to take your suggestions, but wanted to answer the Num Lock question. I have it in place because when I run the Sendkeys it actually turns the Num Lock off. By having this code it place it will turn it back on. \$\endgroup\$ – PlainsWind Apr 2 '14 at 20:48
  • \$\begingroup\$ That would make a good reason to put a comment, so that call doesn't get inadvertantly removed by a future maintainer (could be future you!) - feel free to upvote any useful answers you get ;) \$\endgroup\$ – Mathieu Guindon Apr 2 '14 at 20:59
  • \$\begingroup\$ My overall plan with this code is to return the values from the webpage back into the excel sheet: 1. Wouldn't I need to leave it as a funciton as I am seeking to return a value? 2. Do you know of any articles that will return results from iframe? \$\endgroup\$ – PlainsWind Apr 4 '14 at 13:28
  • \$\begingroup\$ If FillInternetForm should return a value, then you're missing a line of code that would assign such a return value (FillInternetForm = TheReturnValue). You're not showing the code that's calling FillInternetForm, but if it's a procedure (sub) it will look like FillInternetForm and if it's a function it will look like Value = FillInternetForm. I don't know of any articles that return results from iframe (maybe Google does), but my initial thought was that if the form is on a .asp page you probably could get away with sending a simple HTTP request with your parameters, to the URL. \$\endgroup\$ – Mathieu Guindon Apr 4 '14 at 13:38
  • \$\begingroup\$ It's not part of the code shown bc it's not created yet. I first wanted to make sure sending the information was quick and accurate, then I was going to work on the return portion of it. \$\endgroup\$ – PlainsWind Apr 4 '14 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.