Badly Need Your Help in exporting data from a website

[blackorchids2002]

Hi Masters,

I have my code running well in logging into a webpage that requires a login, setting my parameters and filtered the queried data.The only missing part is how to code to export the data in excel.

The webpage has an excel icon to click where it enables you to export the data queried. In this part, I really don't know how to code that to export the data and save it into xls or xlsx format.


Here is the HTML code that I got in inspecting the element in the webpage.

[<img alt="Export to Excel" src="//shipcdn.com/Assets/Excel-icon.png" border="0" complete="complete"/>][/HTML]

[<a href="/Bizdesk/excelExport.aspx?new=excel" target="_blank">][/HTML]




In my code below, they are okay and the missing part of the code is to export the data into excel.

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 = "xxxx"
.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


I would really appreciate your help on this.


Thanks,
blackorchids

 

[blackorchids2002]

Any suggestions on this? I would really appreciate.

 

[chicagocomputerclasses]

As I understand if you click on Export to Excel button it downloads an Excel file.

if you navigate to

/Bizdesk/excelExport.aspx?new=excel it should download the file

ieApp.Navigate "http://www.intexpress.com/BizDesk/excelExport.aspx?new=excel"

After that you can see what name and folder it uses for downloaded files and open the file in VBA or otherwise. Try to see if navigating downloads the file first and go from there.

 

[blackorchids2002]

Hi,

I got the code working and thanks for this one.

However, I have another issue in my code where I need an error handling if I previously logged into the website.
I wanted to come that if I previously logged, I should logged out into the webpage and log in again.


This is the part of the code where I am getting an error if I previously logged into the website.

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 = "xxxx"
.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


Hope you can also help me on this.


Thanks a bunch.

As I understand if you click on Export to Excel button it downloads an Excel file.

if you navigate to

/Bizdesk/excelExport.aspx?new=excel it should download the file

ieApp.Navigate "http://www.intexpress.com/BizDesk/excelExport.aspx?new=excel"

After that you can see what name and folder it uses for downloaded files and open the file in VBA or otherwise. Try to see if navigating downloads the file first and go from there.

 

[chicagocomputerclasses]

There are 2 solutions for this:

1. the right one
See if there is anything in the status bar or browser window title that will let you recognize whether you are logged in or not.
After that you can use an IF statement on .StatusText or .document .Title to run the code.

2. the simple patch method. Go to your browser when you are logged in and see where logout button URL is pointed.
In the begging of your code, before anything else, navigate to your logout URL.

 

[blackorchids2002]

There nothing in the status bar that will let me recognize that I am logged in but there is a text displayed Welcome back xxxx.

[<span class="welcome">][/HTML]
Text - Welcome back
[<span class="welcome" id="ctl00_lblWelcome" style="font-weight: bold;">][/HTML]


When I logged out the URL is "ieApp.Navigate "http://www.intexpress.com/BizDesk/Logout.aspx"
Here is the html code [<a class="welcome" id="ctl00_lbLogout" href="Logout.aspx">][/HTML]

There are 2 solutions for this:

1. the right one
See if there is anything in the status bar or browser window title that will let you recognize whether you are logged in or not.
After that you can use an IF statement on .StatusText or .document .Title to run the code.

2. the simple patch method. Go to your browser when you are logged in and see where logout button URL is pointed.
In the begging of your code, before anything else, navigate to your logout URL.

 

[chicagocomputerclasses]

Your HTML is all messed up in posts, so I'm making assumptions of what it really is.

you may want to add something like this on top

On Error Resume Next

Then try something like this:

textcheck = ""
textcheck = [COLOR=#333333]ieApp[/COLOR].getElementById("[COLOR=#333333]ctl00_lblWelcome[/COLOR]").innerText

If textcheck Like "*Welcome back*" Then
    'do the logged in stuff or leave it blank and continue after IF statement
Else
   'login here
 End If

OR simply start by navigation to logout page and use your old code after
On Error Resume Next
navigate to this http://www.intexpress.com/BizDesk/Logout.aspx
Then the old code

 

[chicagocomputerclasses]

Sorry it should be ieDoc in the post above NOT ieApp