Excel.Workbook.RefreshAll()

Question

I am using this to refresh some queries in an Excel workbook, but sometimes there is an odbc error or an issue. I know you can count for that in a try/catch block, but I do not want to do that because it will stop processing at the 1st error. Is there a way to set the RefreshAll(); statement with parameters to ignore errors so all data is refreshed in the workbook?

Vulpes · Answer

Yes, that's what I was thinking. Might be relatively slow to execute but could get rid of the error.

richard smith · Answer

So use a foreach loop to select 1st sheet, refresh queries, then select 2nd sheet, refresh queries and so on and so forth?

Vulpes · Answer

One thing you could try would be to call RefreshAll on the workbook after you have completed the queries for each worksheet so that you're only refreshing 8 queries at a time rather than 80.

richard smith · Answer

That would make sense. Usually the issue occurs when the workbook has 10+ worksheets. There are probably about 8 queries per worksheet, so it is refreshing 80+ query's. What would be a coding syntax to prevent the error with the loops you mentioned above?

Vulpes · Answer

The first error sounds to me as though some object is being created repreatedly without the earlier one(s) being released first. The second error could be related to the first if the object is, or is related to, an ODBC connection. I'd look to see if you have any code like that - perhaps in a for, foreach or while loop.

richard smith · Answer

The errors I get are 1 of 2: 1) A too many client task error -- which I don't know how to get around at all! 2) A GENERAL ODBC ERROR -- which usually just running the refresh command again will get through this type of error.

Vulpes · Answer

These sort of errors bubble up from the underlying COM interfaces and, whilst you can catch them, there's no way you can ignore them altogether. The best thing to do is to try and figure out what is causing the error in the first place. What sort of error messages are you getting?

Excel.Workbook.RefreshAll()

Answers (7)