PDA

View Full Version : Downloading an Excel file from a website



gtg430i
05-07-2014, 08:17 AM
Hello - This is a continuation of a previous problem which I successfully solved on my own. Basically this is what I need:

1 - I have a list of website that I want to open (Done)

2 - Each website may or may not have an Excel file to download. I want the code to try to open this file (Not completed).

3 - If a file is found, I want the data to be automatically downloaded in an Access Database if possible (Not completed).

Example of a website with an Excel file to download is below. The file if found on the top right of the middle table (arrow pointing down):


https://www.oceanschedules.com/schedules/schedule-search.do?&originId=216646&destinationId=170041&weeksOut=6&date=06-May-2014&origin=Miami, Florida,UnitedStates(USMIA)&destination=Beirut,Lebanon(LBBEY)&searchType=0&searchSort=2&showSurroundingPorts=Y&lad=05/06/2014&dad=06/17/2014&isp=1&PInUserRole=SchedulesInquiry&PInUserType=Public&PInINTTRAProduct=&carrierScac=


Example of a website with NO Excel file to download:


https://www.oceanschedules.com/schedules/schedule-search.do?&originId=211776&destinationId=1005497&weeksOut=6&date=06-May-2014&origin=Anchorage, Alaska,UnitedStates(USANC)&destination=Tanger Med, Tangier, Morocco (MAPTM)&searchType=0&searchSort=2&showSurroundingPorts=Y&lad=05/06/2014&dad=06/17/2014&isp=1&PInUserRole=SchedulesInquiry&PInUserType=Public&PInINTTRAProduct=&carrierScac=

I really appreciate your help and support.



Thank You,

kclewis0614
05-07-2014, 01:15 PM
For 2:


Sub navAndLogin()
Dim IE As New InternetExplorer
IE.Visible = True
IE.Navigate ("yoururlhere")
Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop
IE.Document.GetElementbyID("dwn_25").Click
Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop
For Each i In IE.Document.GetElementsbyName("userName")
i.Value = "email here"
Next i
For Each i In IE.Document.GetElementsbyName("passWord")
i.Value = "password here"
Next i
End Sub

kclewis0614
05-07-2014, 01:19 PM
I am not sure on the 3rd part. BTW, you can find out the field names in chrome or IE11 by right-clicking the object in question and selecting "inspect element" You should then see "id = " or "name = "

Such as:
<input name="userName" tabindex="1" type="text" size="30" maxlength="80" value="">

gtg430i
05-07-2014, 08:41 PM
Hello Kclewis0614 - Thank you very much for your help and tips. I tried your code and it does open the page, however I replaced Dim IE As New InternetExplorer with Set IE = CreateObject("InternetExplorer.Application") because it returned an error first.

But now how can we just open the Excel file? If necessary I can share with my username and password to your inbox to check what I need. We can for now just doenload the Excel file and save it in a folder. I can work on the Access part later.

Also - Thank you for the tip on getting the field names. In fact this how I got each country code because for some reason Chrome does not show the full URL.

Thanks