+ Reply to Thread
Results 1 to 7 of 7

Download excel file from website

Hybrid View

  1. 10-09-2013, 10:54 PM #1
    Sharpbangbang
    Sharpbangbang is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Download excel file from website

    Hello everyone. I need some assistance to download excel file by type in date range in the following website using vba. To be honest, I don't really know anything about HTML, so if you can offer me some help, I will be really, really appreciated.

    The website is http://research.stlouisfed.org/fred2/series/BAMLC0A0CM/downloaddata, and there are two text boxes that require me to type in start date and end date, and the website also allows me to download excel data within the date range.

    So what I want is by typing in date range in my excel sheet, VBA can help me populate relative data in excel.

    Please help, and thank you in advance.
    Reply With QuoteRegister To Reply
  2. 10-10-2013, 01:27 AM #2
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: Download excel file from website

    Try something like this...

    Sub Get_FRED()
    Dim StartDate As Date, EndDate As Date
    
    StartDate = Range("A1").Value
    EndDate = Range("A2").Value
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .Navigate "http://research.stlouisfed.org/fred2/series/BAMLC0A0CM/downloaddata"
        Do Until .ReadyState = 4: DoEvents: Loop
        .Document.getelementbyid("obs_start_date").Value = Format(StartDate, "yyyy-mm-dd")
        .Document.getelementbyid("obs_end_date").Value = Format(EndDate, "yyyy-mm-dd")
        .Document.getelementbyid("download_data").Click
    End With
    
End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  3. 10-10-2013, 08:28 AM #3
    Sharpbangbang
    Sharpbangbang is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Download excel file from website

    Thank you for your reply, unfortunately this code didn't download data from the website. Instead, I got a run-time error '424' object required. Any suggestion?
    Reply With QuoteRegister To Reply
  4. 10-10-2013, 10:37 AM #4
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: Download excel file from website

    Quote Originally Posted by Sharpbangbang View Post
    Thank you for your reply, unfortunately this code didn't download data from the website. Instead, I got a run-time error '424' object required. Any suggestion?
    The code worked for me without error.

    Did you put dates in cells A1 and A2? When you click the DeBug button on the error dialog, what line of code is highlighted? Does Internet Explorer open to the FRED web page? Do you have Internet Explorer loaded on your computer? The more details you can provide, the better.
    Reply With QuoteRegister To Reply
  5. 10-10-2013, 11:28 AM #5
    Sharpbangbang
    Sharpbangbang is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Download excel file from website

    Yes I did put dates in A1 and A2, (8/1/2013 in a1, and 9/1/2013 in a2) when I debug .Document.getelementbyid("obs_start_date").Value = Format(StartDate, "yyyy-mm-dd") is highlighted. The IE did open the correct web page. I do have IE loaded on my computer. And the references that I chose in VBA are the followings: visual basic for applications, Microsoft excel 14.0 object library, ole automation, Microsoft office 14.0 object library, Microsoft internet controls, and Microsoft HTML object library.
    Reply With QuoteRegister To Reply
  6. 10-10-2013, 12:08 PM #6
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: Download excel file from website

    No change in the URL, right?
    "http://research.stlouisfed.org/fred2/series/BAMLC0A0CM/downloaddata"

    Maybe we have to put different code for it to wait for the web page to load.

    As a test; when it errors, look at IE and make sure the web page is done loading. Then click the debug button and click the run icon in the vba editor to resume the code. Does the code then continue?
    Reply With QuoteRegister To Reply
  7. 10-10-2013, 05:38 AM #7
    analystbank
    analystbank is offline
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2021
    Posts
    273

    Re: Download excel file from website

    Sir,

    In this thread u suggested, how to select date and pull web data into excel.
    http://www.excelforum.com/excel-prog...m-website.html

    Similarly, Can u suggest a code, to import CSV into excel worksheet (say Sheet1) from a website (http://nseindia.com/products/content...q_security.htm) into single excel sheet, where before importing, the website ask

    select a) type of data, (in given case - Security -wise Price Volume & Deliverable position data)
    b) Enter Symbol: - (say - LT)
    c) Select Series: - (Say - EQ)
    d) Select a Time Period : (Say - From: 01Oct2013 TO 09Oct2013)

    DAta should be kept from Cell C35 in Sheet1

    Before importing new data (sheet may have been used earlier) any existing web query or data in from Cell C35, which should be deleted/cleared. So only the latest data appear in sheet1 from cell C35. Row 1 to 34 should not be disturbed, as that would be used for charting purpose.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA - Go to website and download file from save prompt
    By randiba in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2013, 08:33 PM
  2. HTML dialog box to download file from website
    By CJK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2012, 12:19 PM
  3. Handling HTML dialog box to download file from website?
    By malkusm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2010, 01:13 AM
  4. file download from website
    By dgkindy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2009, 10:39 AM
  5. [SOLVED] want to download csv file from website and need to open in Excel.
    By Jeff in forum Excel General
    Replies: 2
    Last Post: 01-17-2006, 01:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1