PDA

View Full Version : Excel VBA to open a website and search for specific words in search box



gtg430i
04-20-2014, 09:31 AM
Hello - I appreciate all the help I got in the past from this forum and all the people who gladly cooperate.

I have a new problem to solve. I have an Excel sheet with about 1,000 rows. Each row has an origin and destination column. Now I need a code that opens a specific website and goes to a specific tab and then search for the origin and destination fields queries. The code then takes each row and place them in the search query. The result from each row can result a valid connection between an origin and a destination OR no connection exists. So simply if connection is successful, the code simply returns "yes" otherwise "no".

I can open the website from Excel, but what I need to click on specific tab where I do my searches. One of the problems I am facing is that when I try to click on the tab I need I get the message that I must use Internet Explorer or Chrome or Mozilla. Although Excel uses Internet Explorer to open the website. I am not sure why this error.

Any suggestion?

Thanks

ashleyuk1984
04-20-2014, 12:33 PM
Is it possible to for you to upload your spreadsheet with just a few of the examples, if not all.
I'm sure this can be done, but without seeing data, it's hard to know.

gtg430i
04-20-2014, 04:55 PM
Hello - Attached you will find the following:

1 - Few data rows that we need to run the loop on.

2 - A snapshot of the website that we need to input the data from the rows (FOLLOW LINK)

3 A snapshot to show you the tab ("Ocean Schedule") that we need to access after we open the main website in vba. (FOLLOW LINK)

https://www.dropbox.com/sh/7v0cc86qp29mkww/yrtJtTz02F

Thank YOU,

gtg430i
04-25-2014, 03:00 PM
No help? :(

pike
04-25-2014, 04:28 PM
Hi gtg430i,
maybe, what is the url?

pike
04-25-2014, 04:54 PM
Hi
Can you please add links to the cross post in mrexcel and in mrexcel to vbax

gtg430i
04-27-2014, 12:20 PM
Hello - I did mention that I cross posted this. Can you please tell me what you by posting links?

This is the URL for the website I am trying to run the code on:

http://www.oceanschedules.com/schedules/schedule-search.do

Thanks

pike
04-28-2014, 02:01 AM
what code are you using?

gtg430i
04-28-2014, 08:26 AM
I do not have a code yet. I just need help with filling the search boxes in a website and hit the search button.

Aussiebear
04-28-2014, 10:28 PM
Hello - I did mention that I cross posted this. Can you please tell me what you by posting links?

This lets those who feel able to assist you to link to the other site/s to see how much information has been offered already. Since those who contribute here do so on a voluntary basis, let us show respect by not wasting their time working on something that may already be solved.

gtg430i
04-30-2014, 03:27 PM
I totally agree. Here are the websites where I posted the same question:

http://www.mrexcel.com/forum/excel-questions/773643-open-website-input-text-search-box.html

http://www.excelforum.com/excel-programming-vba-macros/1005966-open-a-website-and-input-text-in-search-box.html#post3675966

I am still not getting on any help. I did not expect it to be a hard task. I had more difficult problems in the problem that were addressed and solved easily.

Thanks

gtg430i
04-30-2014, 03:29 PM
My apologies for the typo errors:

*I am still not getting any help. I did not expect it to be a hard task. I had more difficult problems in the past that were addressed and solved easily.

Aussiebear
04-30-2014, 04:08 PM
Hello - I did mention that I cross posted this.

I have re read your posts here and at no stage did you mention here that you have posted in other forums, prior to this post. Please remember that anyone reading the thread will form an opinion as to whether they might assist the OP based on the transcript. Its possible that most people may have turned away from offering assistance based on what has occurred here.

pike
04-30-2014, 11:14 PM
LOL it only easy if you know how to do the task .. anyway the site your working with is very very slow and unresponsive .It will be quicker if you first copy all the ports and put them into a validatioon list .. then run the search; otherwise it will take to long to retrieve the ports each time you need to find them place them in to a list and then run the query.. Can you attach a workbook with the layout and the list of the ports?

gtg430i
05-01-2014, 06:05 AM
Hi - Please see attached for the list of ports combinations.

This is the website again:

http://www.oceanschedules.com/schedules/search.do


Also note that once you type in the Origin or Destination boxes, the system automatically populates the name of the port. Please try it to see what I exactly mean.

Thank you for your cooperation.

pike
05-01-2014, 01:50 PM
hi,
Struggling to think on what you need ..excel doesnt have predictive text .. but we can go to the website retrieve all the ports and populate a list then you select from that dropdown list.. Once you have all these schedules .. do you need the pages of travel dates? .. as i have said the site is very slow and depending on what you need to do it may be quicked to just to copy the ports

gtg430i
05-01-2014, 05:43 PM
Hello,

Let's take the following example. If we type "Miami" in the Origin field and "Beirut" in the Destination field, the exact port names from each location are loaded automatically. In this case:

Miami, Florida, United States (USMIA)
Beirut, Lebanon (LBBEY)

If we press " Get SChedules", we will see all ships trips schedules on this route. I basically need this information.
The site also allows to download the schedules to excel if you can see on the top right of the middle table (It s the arrow pointing down). I was thinking if we can have the code automatically downloads the schedule in a master excel sheet or if the records become big download them to an access database.

In the Excel file I previously attached, we have ALL combinations of all ports that we can have. However, 2 ports may have no connections and the site will return no schedules found.

As a first step, it would be great if we can find which ports combination from the list I attached have a connection.

Please let me know if additional clarification is needed.

Thank You :)

pike
05-02-2014, 01:19 AM
wo, I'll help not do it all
first step get the schedules to sheet2

'This VBA project requires the following references (in Tools -> References):
'Microsoft Internet Controls
'Microsoft HTML Office Library
Option Explicit
Sub web_table_option_two()
Dim HTMLDoc As New HTMLDocument
Dim objTable As Object
Dim objHeader As Object
Dim lngHeader As Long
Dim lngRow As Long
Dim objRow
Dim objIE As InternetExplorer
Set objIE = New InternetExplorer
objIE.navigate "http://www.oceanschedules.com/schedules/portlist_en.do?locale=en&http://www.oceanschedules.com/schedules/search.do"
' objIE.Visible = True
Application.Wait (Now + TimeValue("0:00:30"))
HTMLDoc.body.innerHTML = objIE.document.body.innerHTML
With HTMLDoc.body
Set objHeader = .getElementsByTagName("h2")
Set objTable = .getElementsByTagName("ul")
For lngHeader = 0 To objHeader.Length - 1
lngRow = lngRow + 1
Debug.Print objHeader(lngHeader).innerText
Debug.Print objTable(lngHeader + 1).innerText
ThisWorkbook.Sheets("Sheet2").Cells(lngRow, 1) = objHeader(lngHeader).innerText
For Each objRow In Split(objTable(lngHeader + 1).innerText, vbCrLf)
ThisWorkbook.Sheets("Sheet2").Cells(lngRow, 2) = Trim(objRow)
lngRow = lngRow + 1
Next
Next lngHeader
End With
objIE.Quit
End Sub

form here you will need to create dynamic cascading lists to populate sheet1

gtg430i
05-02-2014, 06:29 AM
Hello - Thank you very much for the effort. I am afraid I dont quite understand what you are trying to do. I deleted all the data in the "Data" sheet and ran your code. The Data sheet is populated with the names of ports from the website. However, after few seconds I get the error as shown in the attached.

I am not sure why you are trying to get the names of the ports again. I actually provided in an Excel file (attached again) all possible combinations of ports. The way I am thinking is if we create a loop that reads the texts in each row and puts in the "Origin" and "Destination" fields and get the schedules.

Thank you again I know it s a bit complicated.

gtg430i
05-02-2014, 06:31 AM
And here is the Excel file...

pike
05-02-2014, 01:33 PM
Yep, didn't read the question .. you just require a yes or not for the schedule.. so
After studying the source code ..for this site you must enter the ports from the site listboxes selections .. so the code needs to navigate to the site then click the "as origin" link load the next page search for the line with the port then select "as Orgin" link then repeat this for the "As destination" . Once this is done click the "Get Schedules" link for a yes or no answer. Very trick operation especially the way its coded.
Not realy a site that is friendly to automation..

snb
05-02-2014, 02:24 PM
The site also gives you the opportunity to buy a program INTTRA which does exactly what you require.

gtg430i
05-06-2014, 08:02 AM
Hi guys - I didn't expect it to be that complicated. I am contacting INTTRA to If I can buy the program that does what I need.

But thank you all for your support.