Excel VBA to open a website and search for specific words in search box

[gtg430i]

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]

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]

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]

No help?

[pike]

Hi gtg430i,
maybe, what is the url?

[pike]

Hi
Can you please add links to the cross post in mrexcel and in mrexcel to vbax

[gtg430i]

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/schedu...dule-search.do

Thanks

[pike]

what code are you using?

[gtg430i]

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]

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]

I totally agree. Here are the websites where I posted the same question:

http://www.mrexcel.com/forum/excel-q...earch-box.html

http://www.excelforum.com/excel-prog...ml#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]

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]

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]

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]

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]

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]

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]

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/schedu...ules/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]

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]

And here is the Excel file...