C# Excel Named Range Cannot be Found Due to Web Service Data Delay
 

Hello,

I am having difficulty waiting on a named range to be created in Excel. The
range is created from returned web service data. The web service returns data
into a matrix, the matrix is then set to an Excel range, and the range is
named "HFReturnRange".

The "activeCell.FormulaR1C1 = formula" is what starts the web service data
retrieval. The returned data then populates into a range underneath this
formula.

The problem is it takes a couple seconds for the web service data to be
returned and populated, and the subsequent code needs to reference the range
and the range.Name created by this data. So, due to the delay, I am in
essentially trying to reference a range name that does not yet exist.

I put together the loops below in an attempt to cycle until the range.Name
exists. I setup a foreach loop to iterate through the named ranges in the
worksheet BSGDataDump, and break out if it finds "HFReturnRange". I then
placed a goto statement to have it repeat the foreach loop until the named
range exists. I am missing something fundamental, however, because the loop
never ends, even after more than enough time has past for the web service
data to populate.

I appreciate any suggestions! I also tried to use the
System.Threading.Thread.Sleep() approach, but this just delayed everything,
and the result was the same.

Code:

activeCell.FormulaR1C1 = formula;

bool DoesHFReturnRangeExist = false;

Repeat:
foreach (Name rangeName in BSGDataDump.Names)
{
if ((String)rangeName.Name == "HFReturnRange")
{
DoesHFReturnRangeExist = true;
BSGDataDump.get_Range("A10",
System.Type.Missing).Value2 = "Range exists.";
break;
}
}

if (DoesHFReturnRangeExist == false)
{
goto Repeat;
}
else
{
goto Proceed;
}

Proceed: