CODEWITHSUNDEEP
excelvbaweb-scrapingwebformssubmit
Brady Tech
Brady Tech

Reputation: 25

Fill web form in MS Excel VBA silently

I'm learning VBA.

I want to fill a web form in silence e.g 

Set IE = CreateObject("internet explorer.Application")
IE.VIsible = False

When I load the url with this it says I should use another browser to open it.

I want to make it compatible on any OS. I found this

ActiveWorkbook.FollowLinkAddress "myurl.com"

but I don't know how to set it into a variable like 

Set IE = ActiveWorkbook.FollowLinkAddress "myurl.com"
IE.Visible = false

Then I can do things like fill input field, click buttons, .....

Set btn = IE.getElementById(...........
btn.Click = true

This is the URL giving me headache:

https://sellercentral.amazon.com/hz/fba/profitabilitycalculator/index?lang=en_US

Upvotes: 0

Views: 3582

Answers (1)

QHarr
QHarr

Reputation: 84475

Using a hyperlink is not a good way to go. You want to programmatically interact with the webpage so you need an automated browser. IE works just fine. I assume it is a typo where you write internet explorer.Application as it is InternetExplorer.Application.

NB: I show some code for finding default browser at end if you decide to write branched code for different browsers by having selenium basic installed.

You should have a proper page load wait 

While .Busy Or .readyState < 4: DoEvents: Wend

after the submit click but here you can also monitor one of the page properties for a change indicating load has completed (style attribute change)

Internet Explorer:

Option Explicit
'VBE > Tools > References:
' Microsoft Internet Controls
'
Public Sub UseIE()
    Dim ie As New InternetExplorer
    With ie
        .Visible = False
        .Navigate2 "https://sellercentral.amazon.com/hz/fba/profitabilitycalculator/index?lang=en_US"

        While .Busy Or .readyState < 4: DoEvents: Wend

        .document.getElementById("search-string").Value = "1408893339"
        .document.querySelector("#a-autoid-1 .a-button-input").Click

        'While .Busy Or .readyState < 4: DoEvents: Wend

        Do
        Loop While .document.querySelector("#searchProduct").Style = "display: block;"

        Debug.Print .document.querySelector("#product-info").innerText

        Stop
        .Quit
    End With
End Sub

Other browsers using selenium:

If you want to use alternate browsers then consider selenium basic vba which extends browser choice to Opera, Chrome, FireFox, PhantomJS amongst others. After installing selenium ensure latest applicable driver e.g. ChromeDriver.exe is in the selenium folder, then go VBE > Tools > References > Add reference to Selenium Type Library.

Example Selenium with Chrome:

Option Explicit 
Public Sub EnterInfo()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const URL = "https://sellercentral.amazon.com/hz/fba/profitabilitycalculator/index?lang=en_US"

    With d
        .AddArgument "--headless"
        .Start "Chrome"
        .get URL
        .FindElementById("search-string").SendKeys "1408893339"
        .FindElementByCss("#a-autoid-1 .a-button-input").Click

        Do
        Loop While .FindElementByCss("#searchProduct").Attribute("Style") = "display: block;"

        Debug.Print .FindElementById("product-info").Text

        Stop                                     '<==delete me later
        .Quit
    End With
End Sub

Determining default browser:

If you really want to write some convoluted code to determine the default browser you can retrieve the details for it from the registry and then use branched code to launch the appropriate browser (if can be automated). You could alter the following quick test example to a function which returns browser type. You would need selenium installed to use browsers other than IE.

Note: There is a probably a better way using ProgID.

Public Sub Test()
    Dim defaultBrowserInfo As String, browsers(), i As Long, found As Boolean, browser As String
    browsers = Array("Chrome", "InternetExplorer", "FireFox")

    defaultBrowserInfo = CreateObject("wscript.shell").exec("cmd /c REG QUERY HKEY_CLASSES_ROOT\http\shell\open\command").StdOut.ReadAll
    For i = LBound(browsers) To UBound(browsers)
        If InStr(defaultBrowserInfo, browsers(i)) > 0 Then
            found = True
            browser = browsers(i)
            Exit For
        End If
    Next
    If Not found Then
        MsgBox "Browser not in list supplied"
    Else
       MsgBox browser
    End If
End Sub

Changing the cmd line to 

REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\As
sociations\UrlAssociations\http\UserChoice

or 

REG QUERY HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\As
sociations\UrlAssociations\https\UserChoice

returns progId.

Example return:

enter image description here

Although using C# there is a nice code structure here.

Upvotes: 5

Related Questions