excel external data range failed to refresh - second try


John Rodgers

hello. I posted this about a week ago, and got zero replies. is there something wrong with my post? any help, either with the answer, or guidance on how I might get a response, would be most appreciated. thanks

I have an excel 2007 workbook, with several external data ranges. The query definition for each external data range is the local path to an html file(eg, file:///D:/localfiles/filename.html). Each html files contains a data table. This works perfectly most of the time. (And there is a reason weare stuck using the html approach)

The workbook is set up to auto-refresh all queries every five minutes. I have a separate function that updates the content of the html files. Again,the system basically works, almost all the time.

The trick is the almost -- about one of 10 times that the excel file opens,I get a "failed to refresh data range: 'range_name'. continue to refresh all?" And the program sits and hangs until I respond. This is a showstopper because these files must run automatically.

The "failed to refresh" only happens on workbook startup, and only about one of 10 times. Sometimes the workbook works perfectly, sometimes data range: 'html file 1' fails, other times 'html file 1' works, and 'html file 2' fails, etc. (ie, the behavior is inconsistent)

When this happens, I can verify that the subject html file does exist, and that it does have content. The format and fields of the content are exactly the same when it does not work as when it does work.

Does anyone know how to troubleshoot this? Or any advice specific to this challenge? (again, I am stuck using these html files and this general approach of excel auto-refresh of external data ranges.)

Thanks very much in advance.


What triggers the refresh when the file opens?

What state is macro security in with respect to that file?

I've often had startup VBA not executed in files where I get the macro
security warning under the FormulaBar. I don't get any issues when the
macro security dialog displays, though. My suspicion is that something
messes up when the security warning displays below FormulaBar because
the file is already open. The macro security dialog displays before the
file is open (ie: visible) and so I suspect there's a bug in the
initial macro security behavior.

I don't get this behavior when addins open, or when files from
'Trusted' locations are opened.


Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question