I am finding that MS-Excel does not shut down if I read my entire spreadsheet
of 4550 rows with Win32::OLE. But if I read 500 rows and then close Excel and
reopen it and read the next 500 rows (keeping track of where I left off) and
continue this way to the end, then Excel closes down fine. Not sure why so I
made a workaround as explained above. Must be a big memory overhead to read
more than 500 rows without shutting down Excel when using Win32::OLE. It
could be something to do with running in conjunction with Win32::GUI. Anybody
had similar problems with OLE alone, or OLE and GUI combined applications?
Thanks,
Eric
Garland, Texas USA
# This application transfers data from an Excel spreadsheet straight into an
Access database. Excel is shutdown
# after each read of 500 rows from the spreadsheet and restarted to read the
next 500 rows and continued
# till all rows are read. The program keeps track of last record read in
spreadsheet so knows where to begin
# reading of next 500 rows.
use Win32;
use Win32::GUI;
use Win32::ODBC;
use Win32::OLE;
use File::Copy;
$PWD=Win32::GetCwd(); # save application directory so ODBC knows where to look
for MSAccess FILEDSN
$FILEDSN="FILEDSN=$PWD\\MyAccessDB.dsn";
$M = new Win32::GUI::Menu(
"&File" => "File",
" > &Port" => "Port",
" > E&xit" => "Exit",
"&Help" => "Help",
" > &About - Excel port to Access" => "About",
);
$W = new Win32::GUI::Window(
-title => "Excel port to Access",
-left => 100,
-top => 100,
-width => 500,
-height => 200,
-menu => $M,
-style => ws_sysmenu,
-name => "Window",
);
$B = new Win32::GUI::Bitmap("$PWD\\Logo.bmp");
$BITMAP = $W->AddLabel(
-left => 0,
-top => 0,
-style => 14 | WS_VISIBLE,
-name => "Bitmap",
);
$BITMAP->SetImage($B);
$SFont = new Win32::GUI::Font(
-name => "Courier New",
-size => 8,
-weight => 700,
-height => -11,
);
$Status = $W->AddStatusBar(
-name => "Status",
-text => "Select menu option File->Port to begin.",
-font => $SFont,
);
$W->Show();
$W->BringWindowToTop();
#-------------------------------#
# Event Handler
#-------------------------------#
Win32::GUI::Dialog();
END {
$db->Close;
undef $db;
$sheet->Close;
undef $sheet;
$book->Close;
undef $book;
$Excel->Quit;
undef $Excel;
}
#------------------------------------#
# Event Subroutines
#------------------------------------#
sub Exit_Click {
return -1; # exits the Win32 GUI Event Handler Win32::GUI::Dialog();
}
sub About_Click {
$msg="";
$msg=$msg . "Program: Excel port to Access\n";
$msg=$msg . " Version: 1.0\n";
$msg=$msg . " Date: June 2002\n";
$msg=$msg . " By: Eric Hansen\n";
Win32::GUI::MessageBox($W,"$msg",
"About - Excel port to Access",64,);
}
sub Port_Click {
# Open File DialogBox showing files with
# *.xls extension starting in application directory
$file = "*.xls\0" . " " x 256;
$file = Win32::GUI::GetOpenFileName(
-owner => $W,
-directory => $PWD,
-title => "Select MS-Excel file to port to MS-Access",
-file => $file,
);
# if file is not valid, or contains no data, then return
if (! -s $file) {
Win32::GUI::MessageBox($W,"Microsoft Excel file notfound or is empty",
"Excel port to Access - Error & Abort",16,);
return -1;
}
$M->{'Exit'}->Enabled(0); #-- Disable menu option
$M->{'Port'}->Enabled(0); #-- Disable menu option
$Status->Text("Connecting. Please wait...");
$Status->Update();
$stat=copy("$PWD\\CopyOfMyAccessDB.mdb","$PWD\\MyAccessDB.mdb"); #--
File::Copy
if ($stat != 1) {
$msg="Cannot COPY empty database file:\n" .
"$PWD\\CopyOfMyAccessDB.mdb\n" .
"to file:\n" .
"$PWD\\MyAccessDB.mdb\n";
Win32::GUI::MessageBox($W,$msg,
"Excel port to Access - Error & Abort",16,);
return -1;
}
# connect to the MS-Access database
$db = new Win32::ODBC($FILEDSN);
if (! $db) {
$error=Win32::ODBC::Error();
Win32::GUI::MessageBox($W,
"Can't Establish Database Connection using file
DSN:\n$FILEDSN\n$error",
"Excel port to Access - Error & Abort",16,);
return -1;
}
$exitwhileloop="N";
$cnt=0;
$j=1;
# 500 times 130 = 65,000 which is max rows in an Excel spreadsheet
worksheet.
# We have to close Excel after each read of 500 records and restart Excel
# otherwise Excel won't shutdown. Some kind of memory problem develops.
while ($j <= 130) {
if ($j > 1) {
$sheet->Close;
undef $sheet;
$book->Close;
undef $book;
$Excel->Quit;
undef $Excel;
$Excel = Win32::OLE->new('Excel.Application');
$book = $Excel->Workbooks->Open($file);
$sheet = $book->Worksheets(1);
$start=($stop + 1);
$i=$start;
$stop=($start + 499);
} else {
$Excel = Win32::OLE->new('Excel.Application');
$book = $Excel->Workbooks->Open($file);
$sheet = $book->Worksheets(1);
$i=2;
$start=2;
$stop=500;
}
for ($i=$start;$i<=$stop;$i++) {
# build the sql insert statement
$sqltxt="INSERT INTO MyAccessTBL VALUES (";
$cell="A" . $i;
$market=$sheet->Range($cell)->{Value};
$market=~s/\'/\"/g;
$sqltxt=$sqltxt . "'" . $market . "'";
$cell="B" . $i;
$custnbr=$sheet->Range($cell)->{Value};
$custnbr=~s/\'/\"/g;
$sqltxt=$sqltxt . ",'" . $custnbr . "'";
$cell="C" . $i;
$custname=$sheet->Range($cell)->{Value};
$custname=~s/\'/\"/g;
$sqltxt=$sqltxt . ",'" . $custname . "'";
$cell="F" . $i;
$current=sprintf("%9.2f",($sheet->Range($cell)->{Value}));
$sqltxt=$sqltxt . "," . $balance;
if ($market eq "" || $market eq " ") {
# this check tells us when we are at the end of our data since we
know in this case
# that market field should not be blank. Otherwise we would
continue reading to the
# 65,000th row in the spreadsheet which is a waste of time.
$exitwhileloop="Y";
last;
}
#######################################################
# execute the insert statement and get return status
#######################################################
$ret=$db->Sql($sqltxt);
if ($ret) {
$error=$db->Error; # capture the sql error message
Win32::GUI::MessageBox($W,"$error\n$sqltxt",
"Excel port to Access - SQL Error($ret)",16,);
return -1;
}
$cnt++;
$Status->Text($cnt);
$Status->Update();
Win32::GUI::DoEvents(); # keep screen refreshed
} # end of for loop
if ($exitwhileloop eq "Y") {
last;
}
$j++;
} # end of while loop
Win32::GUI::MessageBox($W,"There were $cnt records ported to MS-Access.",
"Excel port to Access - Process Complete.",64,);
$Status->Text("Exiting Program - Please wait ...");
$Status->Update();
sleep 3;
return -1;
} # Port_Click event
# End Script
