Automating Downloading of Data from Bank of England website

Please explain more thoroughly. The link you posted is downloading a spreadsheet to my PC. Here it is:

5279310.zip

Hi

Indeed, the spreadsheet downloaded but I want to know how to automate this even further in a couple of respects:

1) I want Excel to automatically download the spreadsheet to the c drive without any interaction from the user e.g. clicking on save or open etc.

2) I also want to download data for other series in one go. Ideally I want to store several codes in the excel sheet (XUDLGBD in the current case) and run a macro that will download all spreadsheets for each code with the least amount of interaction from the user.

So a sheet in my Excel workbook might look like this:

Code
XUDLGBD
XUDLGPS

I would want excel to download spreadsheets for each one of these codes after clicking on a macro. One problem is I see no reference to each one of these codes in the URL link so am confused how the URL link can be manipulated to refer to each different series.

Hope all this makes sense.

Thanks.

Thanks

Lucas

The link may have an expiration and I would not depend on the links unless you have knowledge that they are immutable.

XUDLGBD
&C=DSJ

XUDLGPS
&C=ECW

This is the only argument that varies in the URL's. Also,

excel97.x=23&excel97.y=22

is looking to validate an actual mouse click on the Excel image. You may need to provide random numbers for the x and y coords. If the server keeps getting requests from the same IP and the mouse is always in the exact same position, they may disallow the download. This is a weak method of discouraging automation.

If you have to dissect many of these series codes, there are other alternatives...

Here is the code. See the example download below for usage...

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> URLDownloadToFile <font color="#0000A0">Lib</font> "urlmon" <font color="#0000A0">Alias</font> "URLDownloadToFileA" _
        (ByVal pCaller <font color="#0000A0">As</font> Long, _
        <font color="#0000A0">ByVal</font> szURL <font color="#0000A0">As</font> String, _
        <font color="#0000A0">ByVal</font> szFileName <font color="#0000A0">As</font> String, _
        <font color="#0000A0">ByVal</font> dwReserved <font color="#0000A0">As</font> Long, _
        <font color="#0000A0">ByVal</font> lpfnCB <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  
  <font color="#0000A0">Sub</font> DownloadSelection()
     <font color="#0000A0">If</font> Selection.Cells.Count <> 1 <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
     Selection.Offset(, 2) = "Downloading. Please wait..."
     <font color="#0000A0">If</font> URLDownloadToFile(0, "http://213.225.136.206/mfsd/iadb/fromshowcolumns.asp?" & _
         "Travel=NIxSCxSUx&FromSeries=1&ToSeries=50&DAT=RNG&FD=1&FM=Jan&FY=1963&" & _
         "TD=3&TM=Apr&TY=2006&CSVF=TT&FN=N&C=" & Selection.Offset(, 1) & _
         " &excel97.x=23&excel97.y=22", "C:\" & Selection & ".xls", 0, 0) = 0 <font color="#0000A0">Then</font>
         <font color="#008000"> 'ok. Run Excel's query on the local file</font>
          Selection.Offset(, 2) = "Downloaded to: C:\" & Selection & ".xls"
     <font color="#0000A0">Else</font>
         <font color="#008000"> 'failed to save page</font>
          Selection.Offset(, 2) = "Download Failed."
     <font color="#0000A0">End</font> <font color="#0000A0">If</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton1_Click()
       DownloadSelection
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>

DownloadUrlBOE.zip

Hello,

Thanks for the code 'rightclick'. It works great but your suspicions about the roboustness of the links were correct. I am still able to download the file using the same links but the downloaded file contains only old data - i.e. the most recent data values say for the last 10 days are not there. So it seems it's downloading an old file probably based on when the file was originally downloaded. Does this imply there could be something indicative of date field/argument in the link they we could potentially manipulate?Just a thought.

Thanks,

Lucas

Updated version seems to solve this problem...

DownloadUrlBOE_2.zip

Hey,

Thanks for all your efforts on this. Don't know exactly what you changed but the code works great.

Lucas

The procedure was so short that I did not really bother with an explanation. I simply added the ability to download more than one file and I added some date variable into the URL...

Hi,

Does URLMon support POST methods?

don't want to thread crap, but this is in regards to a Wininet post

BTW... tip for web content in tables: replace {/TR} with breaks, {/TD} with tabs, and then use a regex to replace <[^>], and you can easily split this up into arrays to work with datasets. It's much, much faster than loading it into a worksheet and using the excel convertor for such things.

http://support.microsoft.com/kb/q165800/
http://support.microsoft.com/default.aspx?scid=kb;en-us;280522

interesting, looks like it may be possible to POST data with URLMon