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

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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]

Hi BR

Sorry, I have no experience with this to advise you.
I am sure that others can help you