Re: WORKBOOKS.OPEN (URL) - How to trap a connection-lost/ timeout

From: BHARATH RAJAMANI (BHARATHRAJAMANI_at_discussions.microsoft.com)
Date: 09/18/04 

Date: Sat, 18 Sep 2004 06:19:04 -0700

Thx so much Ron!!, I tried the following:

a. (Debug) Watch URL:
http://www.nseindia.com/content/historical/DERIVATIVES/2003/MAY/fo26MAY2003bhav.csv
Copied this URL to Internet Explorer - it works fine

b. Wrote a new sub with a simple Workbooks.Open
("http://www.nseindia.com/content/historical/DERIVATIVES/2003/MAY/fo26MAY2003bhav.csv") statement .. which does not execute either.

My bandwidth is okay and the file opens via IE. Is there anyway to specify
wait time etc in VBA, I dont want to end-task MS Excel each time, on a simple
Workbooks.Open statement!!

I need to retrieve the market data from a remote location, so there's no
business alternative for me. Either the exchange website or a Bloomberg/
Reuters machine on the network.

Is the Workbooks.Open (URL) function RELIABLE to retrieve remote flat-files
(loop 60 to 90 CSV files of 5000 rows each)? Would I be better off using an
API .. Can I use the URLDownloadToFile API from vbnet.mvps.org?

The usual tricks .. restart reboot etc did not help either. I need a way for
my macro to exit if it does not receive a response from a remote computer for
a file request. Any thoughts?

TIA !!

Regards,
BR

"Ron de Bruin" wrote:

> Maybe you can try this
>
> You can check your connection before you run your code
> See the website from Randy Birch
> http://vbnet.mvps.org
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "BHARATH RAJAMANI" <BHARATHRAJAMANI@discussions.microsoft.com> wrote in message
> news:EB0E4878-22DE-4975-A11E-699AC0F2E187@microsoft.com...
> >
> > Set wbkMyWorkbook = Workbooks.Open(URL)
> >
> > Using this statement, I try to open a .csv file on a remote (securities
> > exchange) website. How might I trap the error(s) of: network connection
> > timeout, Excel process not responding, HTTP file not found.
> >
> > My macro was working great until yesterday, but today it eats up CPU
> > resources - process-not-responding. I am able to debug, independently
> > retrieve the URL string & download the CSV file by pasting the (debug) URL
> > string on the browser window.
> >
> > I am on Excel XP & a 64kbps connection to the Internet.
> >
> > Any ideas why the macro dies?
> >
> > TIA!!
> >
> > Regards,
> > BR
> >
> > --
> > Manager, International Private Banking, International Banking Group, ICICI
> > Bank
> > East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
> > 400051
>
>
>

Relevant Pages