Hi Masters,
I have figured it out how to start the code but I'm stuck in doing the vba code to select the value from a drop down list in the website that will match my input parameters in my excel. The below codes where I got an error to select the dropdown value.
'Sender State
.ctl00_ContentPlaceHolder1_drpFirstSenderState.Value = "CALIFORNIA"
'Reciever State
.ctl00_ContentPlaceHolder1_drpFirstRecipientState.Select = "GEORGIA"
This is the html element/code
cODE FOR INNER HTML
<select name="ctl00$ContentPlaceHolder1$drpFirstSenderState" id="ctl00_ContentPlaceHolder1_drpFirstSenderState">
CODE FOR OUTER HTML
<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>
Here is the code that I am trying to do but I will hide the username and password in my code as this is confidential.
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
Hi Everyone,
This is my first time to do a vba macro in excel that will get data from a website that requires a login.
I have the required login and password, but I cannot figure out how to make excel automatically get the data.
I can provide the login details thru private message.
Here is the link where I used to export the data queried "http://www.intexpress.com/BizDesk/Login.aspx".
In my excel file, I have a control sheet named "Master Control" where I will input the parameters to query the data I needed and click the "Run Report" button. Once done running the report, the data should be place into a worksheet named "Data - All Details".
The link is provided below to view the screenshot of my file for the parameters to use.
https://drive.google.com/open?id=0B_bNc33ZoeKtb3VNRXJ5TmpnQlk
Thanks,
blackorchids
