NEED HELP: How to code correctly an HTML element to excel vba

blackorchids2002

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
138
Hi Masters,

I have posted a separate thread on how to get data from webpage that requires a log in.
I have sorted it out to start doing it but unfortunately, I have a debug error in running my macro in excel.
It will not select the value that I indicated in my macro to select the chosen "state". In the webpage that I am accessing, there's a drop down list of the state and I tried the code below and it didn't work.

Below are the codes that didn't work.
'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CALIFORNIA"
'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Select = "GEORGIA"


Here is the html code from the website:

[<select id="ctl00_ContentPlaceHolder1_drpFirstSenderState" name="ctl00$ContentPlaceHolder1$drpFirstSenderState"> <option selected="" value="0">Select...</option> <option value="AL">ALABAMA</option> <option value="AK">ALASKA</option> <option value="AZ">ARIZONA</option> <option value="AR">ARKANSAS</option> <option value="CA">CALIFORNIA</option> <option value="CO">COLORADO</option> <option value="CT">CONNECTICUT</option> <option value="DE">DELAWARE</option> <option value="FL">FLORIDA</option> <option value="GA">GEORGIA</option> <option value="HI">HAWAII</option> <option value="ID">IDAHO</option> <option value="IL">ILLINOIS</option> <option value="IN">INDIANA</option> <option value="IA">IOWA</option> <option value="KS">KANSAS</option> <option value="KY">KENTUCKY</option> <option value="LA">LOUISIANA</option> <option value="ME">MAINE</option> <option value="MD">MARYLAND</option> <option value="MA">MASSACHUSETTS</option> <option value="MI">MICHIGAN</option> <option value="MN">MINNESOTA</option> <option value="MS">MISSISSIPPI</option> <option value="MO">MISSOURI</option> <option value="MT">MONTANA</option> <option value="NE">NEBRASKA</option> <option value="NV">NEVADA</option> <option value="NH">NEW HAMPSHIRE</option> <option value="NJ">NEW JERSEY</option> <option value="NM">NEW MEXICO</option> <option value="NY">NEW YORK</option> <option value="NC">NORTH CAROLINA</option> <option value="ND">NORTH DAKOTA</option> <option value="OH">OHIO</option> <option value="OK">OKLAHOMA</option> <option value="OR">OREGON</option> <option value="PA">PENNSYLVANIA</option> <option value="PR">PUERTO RICO</option> <option value="RI">RHODE ISLAND</option> <option value="SC">SOUTH CAROLINA</option> <option value="SD">SOUTH DAKOTA</option> <option value="TN">TENNESSEE</option> <option value="TX">TEXAS</option> <option value="UT">UTAH</option> <option value="VT">VERMONT</option> <option value="VA">VIRGINIA</option> <option value="WA">WASHINGTON</option> <option value="DC">WASHINGTON D.C.</option> <option value="WV">WEST VIRGINIA</option> <option value="WI">WISCONSIN</option> <option value="WY">WYOMING</option></select>][/CODE]

Below is my VBA code.

Sub GetINTdata()

Dim ieApp As InternetExplorer
Dim ieDoc As Object


'create a new instance of ie
Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging
ieApp.Visible = True


'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "http://www.intexpress.com/BizDesk/Login.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.ctl00_ContentPlaceHolder1_txtUsername.Value = "xxx"
.ctl00_ContentPlaceHolder1_txtPassword.Value = "xxxx"
.ctl00_ContentPlaceHolder1_btnSubmit.Click
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop


'now that we’re in, go to the page we want
ieApp.Navigate "http://www.intexpress.com/BizDesk/AWBReport.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'fill in the parameters of the report and filter then export the data
With ieDoc.forms(0)
'Account
.ctl00_ContentPlaceHolder1_txtFilterAccount.Value = ""
'AWB#
.ctl00_ContentPlaceHolder1_txtFilterAWBNumber.Value = ""
'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CALIFORNIA"
'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Select = "GEORGIA"
'Start Date (From)
.ctl00_ContentPlaceHolder1_txtFilterStartDate.Value = "07/01/2015"
'End Date (To)
.ctl00_ContentPlaceHolder1_txtFilterEndDate.Value = "07/08/2015"
'Filter Button
.ctl00_ContentPlaceHolder1_btnFilter.Click
End With


Please help..

Many thanks in advance
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Can anyone please assist on how to control and code a website dropdown through Excel VBA?
I badly need your help. :(


blackorchids2002 said:
Hi Masters,

I have posted a separate thread on how to get data from webpage that requires a log in.
I have sorted it out to start doing it but unfortunately, I have a debug error in running my macro in excel.
It will not select the value that I indicated in my macro to select the chosen "state". In the webpage that I am accessing, there's a drop down list of the state and I tried the code below and it didn't work.

Below are the codes that didn't work.
'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CALIFORNIA"
'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Select = "GEORGIA"


Here is the html code from the website:

[<select id="ctl00_ContentPlaceHolder1_drpFirstSenderState" name="ctl00$ContentPlaceHolder1$drpFirstSenderState"> <option selected="" value="0">Select...</option> <option value="AL">ALABAMA</option> <option value="AK">ALASKA</option> <option value="AZ">ARIZONA</option> <option value="AR">ARKANSAS</option> <option value="CA">CALIFORNIA</option> <option value="CO">COLORADO</option> <option value="CT">CONNECTICUT</option> <option value="DE">DELAWARE</option> <option value="FL">FLORIDA</option> <option value="GA">GEORGIA</option> <option value="HI">HAWAII</option> <option value="ID">IDAHO</option> <option value="IL">ILLINOIS</option> <option value="IN">INDIANA</option> <option value="IA">IOWA</option> <option value="KS">KANSAS</option> <option value="KY">KENTUCKY</option> <option value="LA">LOUISIANA</option> <option value="ME">MAINE</option> <option value="MD">MARYLAND</option> <option value="MA">MASSACHUSETTS</option> <option value="MI">MICHIGAN</option> <option value="MN">MINNESOTA</option> <option value="MS">MISSISSIPPI</option> <option value="MO">MISSOURI</option> <option value="MT">MONTANA</option> <option value="NE">NEBRASKA</option> <option value="NV">NEVADA</option> <option value="NH">NEW HAMPSHIRE</option> <option value="NJ">NEW JERSEY</option> <option value="NM">NEW MEXICO</option> <option value="NY">NEW YORK</option> <option value="NC">NORTH CAROLINA</option> <option value="ND">NORTH DAKOTA</option> <option value="OH">OHIO</option> <option value="OK">OKLAHOMA</option> <option value="OR">OREGON</option> <option value="PA">PENNSYLVANIA</option> <option value="PR">PUERTO RICO</option> <option value="RI">RHODE ISLAND</option> <option value="SC">SOUTH CAROLINA</option> <option value="SD">SOUTH DAKOTA</option> <option value="TN">TENNESSEE</option> <option value="TX">TEXAS</option> <option value="UT">UTAH</option> <option value="VT">VERMONT</option> <option value="VA">VIRGINIA</option> <option value="WA">WASHINGTON</option> <option value="DC">WASHINGTON D.C.</option> <option value="WV">WEST VIRGINIA</option> <option value="WI">WISCONSIN</option> <option value="WY">WYOMING</option></select>][/CODE]

Below is my VBA code.

Sub GetINTdata()

Dim ieApp As InternetExplorer
Dim ieDoc As Object


'create a new instance of ie
Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging
ieApp.Visible = True


'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "http://www.intexpress.com/BizDesk/Login.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.ctl00_ContentPlaceHolder1_txtUsername.Value = "xxx"
.ctl00_ContentPlaceHolder1_txtPassword.Value = "xxxx"
.ctl00_ContentPlaceHolder1_btnSubmit.Click
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop


'now that we’re in, go to the page we want
ieApp.Navigate "http://www.intexpress.com/BizDesk/AWBReport.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'fill in the parameters of the report and filter then export the data
With ieDoc.forms(0)
'Account
.ctl00_ContentPlaceHolder1_txtFilterAccount.Value = ""
'AWB#
.ctl00_ContentPlaceHolder1_txtFilterAWBNumber.Value = ""
'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CALIFORNIA"
'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Select = "GEORGIA"
'Start Date (From)
.ctl00_ContentPlaceHolder1_txtFilterStartDate.Value = "07/01/2015"
'End Date (To)
.ctl00_ContentPlaceHolder1_txtFilterEndDate.Value = "07/08/2015"
'Filter Button
.ctl00_ContentPlaceHolder1_btnFilter.Click
End With


Please help..

Many thanks in advance
Click to expand...
 
Upvote 0
You have to use the option value or index of the option, not the visible text. Therefore try:
Code: 
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CA"
 
Upvote 0
SOLVED: Re: NEED HELP: How to code correctly an HTML element to excel vba

Thanks john for the help. It solved the problem. :)


john_w said:
you have to use the option value or index of the option, not the visible text. Therefore try:
Code: 
.ctl00_contentplaceholder1_drpfirstsenderstate.value = "ca"
Click to expand...
 
Upvote 0
Hi Masters,

I have the codes working on logging a webpage with username and password and enable all the parameters that I set, except for the export to excel button code is not working.

Here is the HTML code where you need to export the data.

[
Excel-icon.png
][/CODE]

[][/CODE]

Sub GetINTdata()

Dim ieApp As InternetExplorer
Dim ieDoc As Object


'create a new instance of ie
Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging
ieApp.Visible = True


'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "http://www.intexpress.com/BizDesk/Login.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.ctl00_ContentPlaceHolder1_txtUsername.Value = "xxx"
.ctl00_ContentPlaceHolder1_txtPassword.Value = "xxxx"
.ctl00_ContentPlaceHolder1_btnSubmit.Click
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop


'now that we’re in, go to the page we want
ieApp.Navigate "http://www.intexpress.com/BizDesk/AWBReport.aspx"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'fill in the parameters of the report and filter then export the data
With ieDoc.forms(0)
'Account
.ctl00_ContentPlaceHolder1_txtFilterAccount.Value = ""

'AWB#
.ctl00_ContentPlaceHolder1_txtFilterAWBNumber.Value = ""

'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CA"

'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Value = "GA"

'Start Date (From)
.ctl00_ContentPlaceHolder1_txtFilterStartDate.Value = "07/01/2015"

'End Date (To)
.ctl00_ContentPlaceHolder1_txtFilterEndDate.Value = "07/08/2015"

'Filter Button
.ctl00_ContentPlaceHolder1_btnFilter.Click
End With
End Sub




John_w said:
You have to use the option value or index of the option, not the visible text. Therefore try:
Code: 
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CA"
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Scrape data from password protected site without using Internet Explorer
Replies
6
Views
5K
Dan_W
D
T
  • Question
Need help with vba and Html dropdown.
Replies
0
Views
547
tburg73
T
S
  • Question
VBA: Working with Loops To Log into Website
Replies
1
Views
2K
tico_ocit
T
T
  • Question
Facing "Object variable or With block variable not set"
Replies
6
Views
749
smozgur
smozgur
A
  • Question
add value in textbox in webpage using vba
Replies
0
Views
774
Ajinkya_1988
A

Forum statistics

Threads
1,214,982
Messages
6,122,573
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top