I'm not quite clear on what you're trying to do, but you can certainly use Perl to automate Word and
Excel - check out the Win32::OLE module that comes with ActivePerl. I have done this sort of thing from Perl in ASP pages but beware, bugs in your code can result in winword.exe and excel.exe processes left running on the server.
You can also use OLE to work with Access, but the recommended approach is to use DBI.
| [reply] |
IMO your looking at the wrong tools. Just because a screwdriver looks like a chisel, doesnt mean it should be used for carving wood.
IM(Humble)O, If your going client server (ie web-based app) it would be better and more scalable to use a client server based RDBMS, such as Oracle, sqlserver, postgres (free), mysql (also free).
Once you have your data in there, (if you want to create reports) you could use something as simple as sql to generate html pages, or something fancy that will produce a pdf or even generate the MS doco's (Excel), for you (Have a look on the cpan).
I'm guessing you have a little knowledge of sql, and RDBMS's from your Access (tm) comments, so be not afraid, the knowledge you have is not wasted, and can be applied to a "more worthy" RDBMS. You may however have to pickup some additional skills such as how to install and configure a c/s rdbms (can be tricky, but not extremely difficult - there are loads of help and doco for out there), and perhaps some manual table creation skills...
Once you have those you can create a much more scalable framework for want you're after!
Good luck! | [reply] |
Thank you both for your advice. I shall look into the modules you have suggested. Allow me to explain my idea for this app.
I am an instructor for certification school. We have all our data tied up in Access databases. All our billing documents in VB enhanced Excel pages. And a whole lot evaluations form tied up in mail-merged and locked Word documents.
Instead of redoing everything (meaning shifting to a system based on a true RDMS, web scriting, and browser) I'd rather use all the MS office based forms within a web-app for our intranet. This way I can improve (make completing our daily tasks faster and easier) our current system while creating a more robust future system that will eventually replace the current system.
I have recently found that PHP can do this with it's poorly documented COM support. I was hoping that Perl had a better documented method.
| [reply] |
TBH i reckon you're wasting your time. Why spend the time automating something you're going to get rid of anyways?
Think, scalability, structural integrity of your system, maintainability, data integrity - then think, how easy is this to import into an RDBMS?
| [reply] |
Here is some sample code used to extract numbers from a range of cells in Excel. This should help with the general idea of how the Win32 Oblect Linking and Embedding module works.
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# Open Excel file
my $Book = $Excel->Workbooks->Open("C:\\Temp\\ExcelFile.xls");
# Select worksheet number 1
my $Sheet = $Book->Worksheets(1);
# Get the contents
my $array = $Sheet->Range("B5:AT5")->{'Value'};
$Book->Close;
foreach my $ref_array (@$array) {
foreach my $scalar (@$ref_array) {
print "$scalar\t";
}
print "\n";
}
| [reply]
[d/l] |