VBA Select from dropdown in Internet Explorer

html vba excel internet-explorer-9

21,523

You need to actually navigate to the page first in order to load the document - then use the below code:

ie.Document.GetElementByID("ReportViewerControl_ctl01_ctl05_ctl00").Value = "EXCEL"

Use the option that is inside the <option> tag.

To navigate:

ie.Navigate ProductionAddress

While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend

ie.Document.GetElementByID("ReportViewerControl_ctl01_ctl05_ctl00").Value = "EXCEL"

21,523

Author by

elt2jdavis

BY DAY: Amateur Petroleum Engineer and Veteran Nuclear Engineer BY NIGHT: Do my best to teach myself VBA to make the company I work operate less inefficiently FOR FUN: I enjoy the usual things like playing sports and building rifles to shoot tiny things from very far away.

Updated on July 09, 2022

Comments

elt2jdavis almost 2 years

edit: I cleaned up the source code formatting and updated my VBA code. I'm no longer getting an error, but nothing is happening in my browser window (nothing selected, nothing clicked).

I have some VBA code that allows a user to enter information into a form in Excel. The code takes this information and generates an address to a page on our intranet. On that page there is a dropdown list to select export format and after a format is chosen the user can click to export the file.

The VBA code I have tried (and several variations) is below:

Public Sub window_Open(strLocation As String, Menubar As Boolean, height As Long, width As Long, resizable As Boolean)

With CreateObject("InternetExplorer.Application")
    .Visible = False
    .height = height
    .width = width
    .Menubar = Menubar
    .Visible = True
    .resizable = True
    .Navigate strLocation
End With

End Sub


    Private Sub OKButton_Click()
    Dim ProductionAddress As String
    ProductionAddress = "http://Collapsed"

    window_Open ProductionAddress, True, 800, 1000, False
        Dim ie As Object
        Set ie = CreateObject("InternetExplorer.Application")

        ie.Navigate ProductionAddress

        While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend

        ie.document.getelementbyid("ReportViewerControl_ctl01_ctl05_ctl00").Value = "EXCEL"

        Set objButton = ie.document.getelementbyid("ReportViewerControl_ctl01_ctl05_ctl01")
            objButton.Focus
            objButton.Click

    End Sub

The source code for the page I am attempting to navigate is below. Element for selecting EXCEL:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<style>
body, frameset, form {
    margin: 0px;
    padding: 0px;
}
.PageNumberText, input, button, select, table, body, frameset {
    font-size: 8pt;
    font-family: Verdana;
}
.ToolbarExport {
    display: inline;
}
.WidgetSet {
    height: 30px;
}
.ToolBarButtonsCell {
    padding-left: 6px;
}
.MenuBarBkGnd {
    background-color: #ECE9D8;
}
</style>
</head>
<body style="margin: 0px; overflow: auto;"><form name="ReportViewerForm" id="ReportViewerForm" action="ReportViewer.aspx?%2fProduction%2fWell+Daily+Production&amp;StartDate=01%2f01%2f2015+00%3a00%3a00&amp;EndDate=07%2f21%2f2015+00%3a00%3a00&amp;WellName=Acadia&amp;rs%3aParameterLanguage=&amp;rc%3aParameters=Collapsed" method="post"><table id="ReportViewerControl" style="width: 100%; height: 100%;" onclick="if (document.getElementById('ReportViewerControl_ctl00') != null) document.getElementById('ReportViewerControl_ctl00').ParametersController.HideActiveDropDown();" onactivate="if (document.getElementById('ReportViewerControl_ctl00') != null) document.getElementById('ReportViewerControl_ctl00').ParametersController.HideActiveDropDown();" cellspacing="0" cellpadding="0"><tbody><tr><td><div class="MenuBarBkGnd"><div class="ToolBarButtonsCell"><div class="ToolbarExport WidgetSet"><table style="display: inline;" cellspacing="0" cellpadding="0"><tbody><tr><td height="28"><select name="ReportViewerControl$ctl01$ctl05$ctl00" title="Export Formats" id="ReportViewerControl_ctl01_ctl05_ctl00" onchange="document.getElementById('ReportViewerControl_ctl01_ctl05_ctl01').Controller.SetViewerLinkActive(document.getElementById('ReportViewerControl_ctl01_ctl05_ctl00').selectedIndex != 0);"><option value="EXCEL">Excel</option></select></td></tr></tbody></table></div></div></div></td></tr></tbody></table></form></body>
</html>

And the source code for the export element:

    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<style>
.DisabledLink {
    font-size: 8pt;
    font-family: Verdana;
    color: gray;
    text-decoration: none;
    cursor: default;
}
body, frameset, form {
    margin: 0px;
    padding: 0px;
}
.PageNumberText, input, button, select, table, body, frameset {
    font-size: 8pt;
    font-family: Verdana;
}
.ToolbarExport {
    display: inline;
}
.WidgetSet {
    height: 30px;
}
.ToolBarButtonsCell {
    padding-left: 6px;
}
.MenuBarBkGnd {
    background-color: #ECE9D8;
}
</style>
</head>
    <body style="margin: 0px; overflow: auto;"><form name="ReportViewerForm" id="ReportViewerForm" action="ReportViewer.aspx?%2fProduction%2fWell+Daily+Production&amp;StartDate=01%2f01%2f2015+00%3a00%3a00&amp;EndDate=07%2f21%2f2015+00%3a00%3a00&amp;WellName=Acadia&amp;rs%3aParameterLanguage=&amp;rc%3aParameters=Collapsed" method="post"><table id="ReportViewerControl" style="width: 100%; height: 100%;" onclick="if (document.getElementById('ReportViewerControl_ctl00') != null) document.getElementById('ReportViewerControl_ctl00').ParametersController.HideActiveDropDown();" onactivate="if (document.getElementById('ReportViewerControl_ctl00') != null) document.getElementById('ReportViewerControl_ctl00').ParametersController.HideActiveDropDown();" cellspacing="0" cellpadding="0"><tbody><tr><td><div class="MenuBarBkGnd"><div class="ToolBarButtonsCell"><div class="ToolbarExport WidgetSet"><table style="display: inline;" cellspacing="0" cellpadding="0"><tbody><tr><td height="28"><a title="Export" class="DisabledLink" id="ReportViewerControl_ctl01_ctl05_ctl01" onmouseover="this.Controller.OnLinkHover();" onmouseout="this.Controller.OnLinkNormal();" onclick="&#10;var formatDropDown = document.getElementById('ReportViewerControl_ctl01_ctl05_ctl00');&#10;if (formatDropDown.selectedIndex == 0)&#10;return false;&#10;window.open(document.getElementById('ReportViewerControl').ClientController.m_exportUrlBase + encodeURIComponent(formatDropDown.value), '_blank')&#10;formatDropDown.selectedIndex = 0;&#10;document.getElementById('ReportViewerControl_ctl01_ctl05_ctl01').Controller.SetViewerLinkActive(document.getElementById('ReportViewerControl_ctl01_ctl05_ctl00').selectedIndex != 0);return false;" href="#"><script type="text/javascript">
                            document.getElementById('ReportViewerControl_ctl01_ctl05_ctl01').Controller = new ReportViewerLink("ReportViewerControl_ctl01_ctl05_ctl01", false, "ActiveLink", "DisabledLink", "", "", "");
                        </script>Export</a></td></tr></tbody></table></div></div></div></td></tr></tbody></table></form></body>
</html>

Thanks!

elt2jdavis almost 9 years

That did get rid of my error message, but it seems that still nothing is happening in my browser. I tried to use the same code to click on "News" on a google search page for 'cat' but it didn't work either (at least no error message): window_Open GoogleCats, True, 850, 1200, False Dim ie As Object Set ie = CreateObject("InternetExplorer.Application") ie.Navigate GoogleCats While ie.ReadyState <> 4 Or ie.Busy: DoEvents: Wend ie.Navigate GoogleCats Set objButton = ie.document.getelementbyid("gb5") objButton.Focus objButton.Click End Sub
elt2jdavis almost 9 years

I apologize, I was trying to get code formatting to work but then I got locked out of editing. Should I create a separate question for clicking on the Export element?
SierraOscar almost 9 years

You need to explain what window_Open does
elt2jdavis almost 9 years

window_Open opens an Internet Explorer browser window. I'll add the code to my question
SierraOscar almost 9 years

You're actually opening 2 instances of IE. Create a public variable and assign that inside your window_Open routine instead. Or just don't use the window_Open routine at all.