Daniel J. Rychlik wrote at Sat, 25 May 2002 04:00:27 +0200:
> Hey Guys I need some help. I have been working on this script for a week now trying
>to out put my
> sql data into an excel macro. I have tried everything I know how to do and I need
>some direction.
> Please advise.
>
> Heres my code:
> #!perl
#!/usr/bin/perl -w
>
use strict;
use warnings;
> use DBI;
> use IO::File;
> use Win32::OLE;
> use Win32::OLE::Const 'Microsoft Excel';
>
> $dbh = DBI->connect('dbi:ODBC:agent');
>
> my $agnlst = 'agent_list.txt';
> my $ticsub = 'ticket_scrub.txt';
> my $out = IO::File->new(">$ticsub");
>
> print "Connecting to Sql Database\n ";
> sub agent_report {
>
> my $agent= shift;
>
> $sql = "SELECT tblScrubInfo.numTicketNumber, tblScrubInfo.txtAgentID,
>tblScrubInfo.datDate,
> tblScrubInfo.txtCustomer,
> tblScrubInfo.datDateIncidentWasNoted, tblScrubInfo.txtScrubingTech,
>tblScrubDetail.txtCategory,
> tblScrubDetail.Description, tblScrubDetail.ynWasFixed FROM tblScrubInfo,
>tblScrubDetail WHERE
> txtAgentID='$agent' AND tblScrubInfo.numTicketNumber =
>tblScrubDetail.numTicketNumber AND
> ynWasFixed= No ORDER BY txtCustomer ";
Avoid using code more than one times.
One solution (that's still a little bit ugly is):
my ($t1, $t2) = qw(tblScrubInfo tblScrubDetail);
my $sql = <<"SELECT_CMD";
SELECT
$t1.numTicketNumber, $t1.txtAgentID, $t1.datDate, $t1.txtCustomer,
$t1.datDateIncidentWasNoted,
$t1.txtScrubingTech, $t1.txtCategory,
$t2.txtCategory, $t2.Description, $t2.ynWasFixed
FROM
$t1, $t2
WHERE
txtAgentID='$agent' AND $t1.numTicketNumber = $t2.numTicketNumber AND ynWasFixed = No
ORDER BY txtCustumer
SELECT_CMD
Imagine always what you have to do, when the tables are renamed.
In your where clause, you use tblScrubDetail.numTicketNumber,
but I can't find the field in the select statement.
Is it right to write ynWasFixed = No.
Boolean values often have the label FALSE or
No is written in commata 'No'.
>
> $sth = $dbh->prepare($sql);
> $sth->execute ||
> die "Cannot open my $dbh ?";
>
> while (@row=$sth->fetchrow_array)
> {print $out "@row\n"}
>
> }
>
> print "Connection Successful - Fecthing Data \n";
>
> open (LIST, "$agnlst") or die "Cannot open the agent $agnlst: $!\n";
>
> while ( my $ca = <LIST> ){
> chomp ($ca);
> agent_report($ca);
> }
> close (LIST);
>
> my $Excel = Win32::OLE-> new ('Excel.Application', 'QUIT') or
> die ("Cannot create new object: ", Win32::OLE->LastError());
>
> $Excel-> Sheet (QueryTables->Add) (Connection);
That's the error perl is complaining about.
What do you want to achieve ?
Perl only siehs something like
foo(bar)(bar2);
> $Excel-> {TEXT} ('C:\Perl\bin\ticket_scrub.txt',
> (Destination->Range("A1"));
> $Excel-> {Name} = ticket_scrub;
> $Excel-> {FieldNames} = True;
> $Excel-> {RowNumbers} = False;
> $Excel-> {FillAdjacentFormulas} = False;
> $Excel-> {PreserveFormatting} = True;
> $Excel-> {RefreshOnFileOpen} = False;
> $Excel-> {RefreshStyle} = xlInsertDeleteCells;
> $Excel-> {SaveData} = True;
> $Excel-> {AdjustColumnWidth} = True;
> $Excel-> {RefreshPeriod} = 0;
> $Excel-> {TextFilePromptOnRefresh} = False;
> $Excel-> {TextFilePlatform} = xlWindows;
> $Excel-> {TextFileStartRow} = 1;
> $Excel-> {TextFileParseType} = xlDelimited;
> $Excel-> {TextFileTextQualifier} = xlTextQualifierNone; $Excel->
> {TextFileConsecutiveDelimiter} = False;
> $Excel-> {TextFileTabDelimiter} = False;
> $Excel-> {TextFileSemicolonDelimiter} = False;
> $Excel-> {TextFileCommaDelimiter} = False;
> $Excel-> {TextFileSpaceDelimiter} = True;
> $Excel-> {TextFileColumnDataTypes} = Array(1, 1, 1, 1, 1, 1, 1,
> 1, 1, 1,);
> $Excel-> {Refresh BackgroundQuery} = False;
> $Excel-> Workbook-> SaveAs = ('C:\Perl\bin\ticket_scrub.xls');
>
However, you should avoid double code again:
$Excel->....
$Excel->....
$Excel->....
One possibility is to exploit the for command:
for ($Excel) {
$_->{Name} = 'ticket_scrub';
$_->....
$_->....
}
>
> Heres the error message that I get when I run it. I have read the doucmentation and
>I think Im
> almost there but I need some direction.
>
> Documents and Settings\daniel\Desktop>perl agent.pl syntax error at agent.pl line
>43, near ") ("
> syntax error at agent.pl line 44, near ");"
> Execution of agent.pl aborted due to compilation errors.
Best Wishes,
Janek
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
