When I set visible to 1 and pause after RefreshAll() the excel sheet has all of its data. I also tried making

my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};
[download]

constants, but then the code gets stuck with the same error on

However, in this second case, if I run it twice in a row, it works... the only difference I see between runs is that excel is still open from the first run because Perl didn't reach the 'close()' line, but this didn't fix the first case (without constants).

It's hard to tell exactly what's happening and I'm not familiar with web querying in Excel, but my guess is RefreshAll isn't totally blocking so your code continues executing even if it's not done. In the second run the data has completed the refresh and

Win32::OLE->GetActiveObject('Excel.Application')

grabs the background Excel process you started previously, which now has all of the data. Since you're quitting anyways I would remove this (just create a new instance) and try sleep-ing for a few minutes after RefreshAll. You may need a while(1) loop that checks on the status of the data query, or set the BackgroundQuery property to 0 on every object (if that's the root problem).

Installing a signal handler to close and exit would also prevent Excel staying open in the background:

$SIG{INT} = sub { cleanup(); die "Caught interrupt"; };

... (main code) ...

cleanup();

sub cleanup {
  $Book->Save();  
  $Book->Close();
  $Excel->Quit();
}
[download]

It may be related to a slow refresh like you're saying. For some reason the code appears to have started working again without me applying any changes, and the refresh speed is one of the only factors I know that is variable. If the problem arises again I'll try that. In any case, it somehow works now. Thank you for your help blakew, I really appreciate it.