Re^2: Problems parsing Excel 2010 .xlsx file with Win32::OLE

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

$hasher[$r-2]{ $Sheet->Range($c.'1')->{Value} } = $Sheet->Range($c.$r)->{Value};

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).

Comment on Re^2: Problems parsing Excel 2010 .xlsx file with Win32::OLE Select or Download Code

Replies are listed 'Best First'.
Re^3: Problems parsing Excel 2010 .xlsx file with Win32::OLE by blakew (Monk) on Feb 22, 2012 at 21:44 UTC
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]	[reply] [d/l] [select]
Re^4: Problems parsing Excel 2010 .xlsx file with Win32::OLE by mwarrior (Initiate) on Feb 22, 2012 at 21:48 UTC
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.	[reply]