Excel document manipulation related tasks are NOT likely to be faster in perl.
If you can attribute the 15-minute duration to managing a large quantity of data (Thousands of Excel rows), then you should consider moving the data into a database - just doing that would probably yield orders of magnitude improvement.
If you attribute the slowness to poor code, well, bad perl code can run slow too, so the focus should be on improving the code.
Having worked similar issues before, I have mostly encountered attempts to manipulate data that is better suited to a database.
"I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
-- Dr. Cox, Scrubs
| [reply] |
What features of Perl are you planning to use that aren't available in VBA? If there aren't any, stick to VBA. It's a ghastly language, but it's more readable to the beginner than Perl and the interface to Excel is slightly cleaner (see my reply to Extracting Chinese characters from Excel for a situation where VBA can but Perl can't). Remember that if you are trying to recruit someone to maintain the code, they will have to know Excel and $language. That group is much larger (or at least thinks it is) for $language = VBA than for $language = Perl.
But yes, there are plenty of situations where Perl has the edge. That's why I'm here! And there are at least tens of thousands of accountants (my field) who think that recording a macro is programming and end up producing screeds of inefficient code that is utter rubbish and totally unmaintainable. Look out for telltale signs such as Select and Activate. If you don't know why that's bad, read Excel’s Select and Activate considered harmful.
One inefficiency I can warn you about in either language: passing data into and out of Excel is hideously slow. If you can, read and write blocks with single statements by passing arrays (I don't think I've demonstrated this in any of my code here, but it's a common technique). Certainly minimise your transfers and don't use Excel cells as variables.
Regards,
John Davies
| [reply]
[d/l]
[select] |
G'day DyslexicHobo,
Welcome to the monastery.
I've coded those sorts of tasks in Perl so it's certainly possible to do; I can't make any claims about whether it will be faster.
For various tasks, you'll find there's different modules for *.xls and *.xlsx spreadsheets, e.g. Spreadsheet::ParseExcel (for the reading the older format) and Spreadsheet::XLSX (for the newer format).
There's a tutorial (with a lot of follow-up information) that may address many of your initial questions: Using Win32::OLE and Excel - Tips and Tricks.
| [reply]
[d/l]
[select] |
| [reply] |
Similiar to what NetWallah said:
One can write bad code in any language.
If you have perl do the same thing the same way, it will run crawl at the same speed.
More of importance is: which language is/will be easier for the person(s) to maintain these macros, especially if there are frequent change requests.
| [reply] |