Automated form submit/Data Extraction help

[SeanDamnit]

Hello collective consciousness of MrExcel.com. I'm hoping you can help me out with a project.

I am trying to :
-Take a serial # from excel and enter it in to a search form on a site
-Return specific info from the results back in to the excel document
-Rinse and repeat for the next serial number

I am unfortunately not familiar enough with macros and data extracting to figure out how to do this, so I'm looking for guidance on whether or not excel has this capability, and if so, where is the best place for me to begin learning how to do this.

I've attached sample data I'm working with. Links to the websites I'm getting the data from aren't available to the public, so I saved the source code for one instance and attached it. Here's the specifics:

1. Grab serial # from D3, stick it in "ESN/MEID/MSN :" field on DARA search page.html and click "Search for Dates" button
2. Results is DARA results page.html. Grab "Activation Date" and "Deactivation Date" fields and import in to I3 and J3.
3. Grab same serial #, an run search via the "ESN/MEID (for Sprint Devices)" field on BP Search Page.html
4. Results is BP results page.html. Grab "Order #" and "Item Code" values and import in to L3 and M3.
5. Get next serial number and repeat.

Any help is appreciated, thank you!

Files are here:
http://www.mediafire.com/?t1k9bu3nb385qcw

 

[SeanDamnit]

gonna give this a bump before bed.

I'm not looking for someone to create this for me - I'm looking for someone to tell me if it's possible, and to point me in the right direction.

Thanks!

 

[SeanDamnit]

new bump for the new day

any help is appreciated

 

[SeanDamnit]

we'll try this one more time...

 

[Norie]

Sean

It probably is possible, but it's also probably quite hard to give specific help without valid URLs to work with.

I've had a quick look at some of your files.

If those are the actual web pages, or representative of them then I can see some obstacles.

For example the 3 forms on the DARA search page have the same name, 'msnSearchForm'.

So something would need to be done to identify which one you wanted.

Not too difficult but a little annoying and might not be very reliable - what if they decide to change/delete/add something.

The BP search page and both results pages look pretty straightforward.

Also, I'm pretty sure that there's some sort of server-side stuff going on.

Probably just simple data retrieval and nothing really to worry about.

Though methods that would work for 'normal' HTML pages might not work for these.

Actually that reminds me, are these pages actually HTML or are they JSP, ASP, PHP, whatever...?

 

[SeanDamnit]

Sean

It probably is possible, but it's also probably quite hard to give specific help without valid URLs to work with.

I've had a quick look at some of your files.

If those are the actual web pages, or representative of them then I can see some obstacles.

For example the 3 forms on the DARA search page have the same name, 'msnSearchForm'.

So something would need to be done to identify which one you wanted.

Not too difficult but a little annoying and might not be very reliable - what if they decide to change/delete/add something.

The BP search page and both results pages look pretty straightforward.

Also, I'm pretty sure that there's some sort of server-side stuff going on.

Probably just simple data retrieval and nothing really to worry about.

Though methods that would work for 'normal' HTML pages might not work for these.

Actually that reminds me, are these pages actually HTML or are they JSP, ASP, PHP, whatever...?

Hello Norie, thank you for the response.

They are actual web pages - but look funny because I forgot to grab the css file, and of course images and some other items aren't going to work correctly. I'm not sure how to answer what type of pages these are (html, jsp, asp, etc.) does looking at the link help? If so:

DARA - https://iscportal.sprint.com/<wbr>whalecomc935ff70a6ac03279509bf<wbr>c77a05cfbd863d65/whalecom1/<wbr>apps/msnsearch/actions/<wbr>msnDateSearch
BP - https://www.brightpointonline.<wbr>com/bpo/secure/reports/<wbr>SerialInquiry.do?action=search

I understand the difficulty not having live links to work with. So how about we do an example of the concept I can pick apart and apply to my needs?

1. Lets take a site where a search returns a data table. Say...http://www.sherdog.com/stats/fightfinder
2. Then lets enter a few last names in to the spreadsheet....say Silva, Couture, Aoki
3. Now lets automate a search using the name in A1, searching the Last Name field, and import the table of results into excel
4. Retrieve the full name from that imported table and place it in B1
5. Repeat process for A2/B2

If you can show me how to do that, then I'm sure I can identify any further difficulties and ask for help.

I appreciate it!

 

[Norie]

Sean

Either doing it with the files you uploaded or with another URL really will only give you the basics.

I can almost guarantee when you try it for 'real' it won't work.

That said I'll post something for both the files and the example URL you posted when I get a chance.

In the meantime, this is basically how I would approach it.

1 Create an instance of Internet Explorer.

2 Navigate to the web page in question.

3 Grab references for the relevant input fields.

4 Fill in values.

5 Submit page's form/whatever.

6 Extract data from the results page.

3-6 are all done by referring to the document behind the page via IE.

 

[Norie]

Here's a start.

Option Explicit
 
Sub test()
Dim IE As Object
Dim doc As Object
Dim strURL As String
Dim strPage As String
Dim txtSerial As Object
Dim btnSubmit As Object
 
    ' change the following to the real URL
    strURL = ThisWorkbook.Path 
 
    ' page name - might not be necessary if full URL is in strURL
    strPage = "Dara Search Page.html" 
 
    ' create an instance of Internet Explorer
    Set IE = CreateObject("InternetExplorer.Application")
 
    IE.Visible = True ' optional
 
 
    IE.navigate strURL & "\" & strPage
 
    ' create a reference to the document    
    Set doc = IE.document
 
    ' create a reference to the input box for the serial number and enter the value from D3
    Set txtSerial = doc.getelementbyid("msn")
 
    txtSerial.Value = Range("D3")
 
    ' create a reference to the date search button
    Set btnSubmit = doc.getelementbyid("SearchForDates")
 
    ' click the button    
    btnSubmit.Click
 
    Set IE = Nothing
 
End Sub

This works but there are few things missing, eg a simple loop after navigation to ensure the page is loaded and we can proceed with the rest of the code.

Oh, and it's based on your files saved locally - part of the reason for no loop.

 

[SeanDamnit]

Here's a start.

Option Explicit
 
Sub test()
Dim IE As Object
Dim doc As Object
Dim strURL As String
Dim strPage As String
Dim txtSerial As Object
Dim btnSubmit As Object
 
    ' change the following to the real URL
    strURL = ThisWorkbook.Path 
 
    ' page name - might not be necessary if full URL is in strURL
    strPage = "Dara Search Page.html" 
 
    ' create an instance of Internet Explorer
    Set IE = CreateObject("InternetExplorer.Application")
 
    IE.Visible = True ' optional
 
 
    IE.navigate strURL & "\" & strPage
 
    ' create a reference to the document    
    Set doc = IE.document
 
    ' create a reference to the input box for the serial number and enter the value from D3
    Set txtSerial = doc.getelementbyid("msn")
 
    txtSerial.Value = Range("D3")
 
    ' create a reference to the date search button
    Set btnSubmit = doc.getelementbyid("SearchForDates")
 
    ' click the button    
    btnSubmit.Click
 
    Set IE = Nothing
 
End Sub

This works but there are few things missing, eg a simple loop after navigation to ensure the page is loaded and we can proceed with the rest of the code.

Oh, and it's based on your files saved locally - part of the reason for no loop.

This works, but only when I step in to it and allow the page to load (this must be the loop you are talking about)

Next questions are:
1. How do I make it wait for the page to load
2. How do I extract the information on the results page

Thanks for your help Norie. If this works it's going to save me a ridiculous amount of time.

 

[SeanDamnit]

I tried applying this template to the BP search page, and everything seems to work, except it doesn't submit the form. Code is:

Option Explicit
 
Sub test()
Dim IE As Object
Dim doc As Object
Dim strURL As String
Dim strPage As String
Dim txtSerial As Object
Dim btnSubmit As Object
 
    ' change the following to the real URL
    strURL = "https://www.brightpointonline.com/bpo/secure/reports/SerialInquiry.do?action=search"

    ' create an instance of Internet Explorer
    Set IE = CreateObject("InternetExplorer.Application")
 
    IE.Visible = True ' optional
 
    IE.navigate strURL
 
    ' create a reference to the document
    Set doc = IE.document
 
    ' create a reference to the input box for the serial number and enter the value from D3
    Set txtSerial = doc.getelementbyid("serialNumber")
 
    txtSerial.Value = Range("D3")
 
    ' create a reference to the date search button
    Set btnSubmit = doc.getelementbyid("action")
 
    ' click the button
    btnSubmit.Click
 
    Set IE = Nothing
 
End Sub

I'm certain the search button is referenced properly:

Any ideas?