Thank you for the various answers. I could not find what I really wanted in the references. Therefore I wrote a simple test script (below) to try and find out what I was looking for. This creates directories with increasing numbers of characters in their names. It then writes spreadsheets in these directories again with increasing name lengths. A summary is printed out at the end of what is found. It seems that the limit is an overall total of about 219 characters and that the path and file name have to be included in this total.
use strict;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($test_dir, $mkdir_res, $dir_name, $j, $k,$dir_length, $excel_name,
+ $full_excel_name, $full_excel_name_length);
my ($excel, $workbook, $sheet, $excel_name, $full_excel_name_length, $
+full_excel_name, $excelsave_res, $excel_res);
my (%config_data, $ext_length, %filedir_res, $test_dir_root);
# the next line needs to have the extension for the normal Excel sprea
+dsheet on the PC
# this will be either .xls or .xlsx
$config_data{cfg_quotation_spreadsheet_extension} = '.xlsx';
$ext_length = length($config_data{cfg_quotation_spreadsheet_extension}
+);
#==================================================================
#
# open spredsheet_for_excel_write
#
# this opens a new spredshert for use with the Excel wirting module
#
#==================================================================
sub open_spreadsheet_for_excel_write($$$$) {
my ($ref_excel, $ref_workbook, $ref_sheet, $ref_excel_res) = @_;
my ($ew_obj_message, $err_dialog, $err_ans, $ew_message);
$ew_obj_message = '';
eval {$$ref_excel = Win32::OLE->GetActiveObject( 'Excel.Application' )
+ };
if( $@ ) {
$ew_obj_message = "[open_spreadsheet_for_excel_write] Error: no E
+xcel installed - message <$@>\n";
print "$ew_obj_message\n\n";
} else {
print "[open_spreadsheet_for_excel_write] Excel installation test
+passed\n";
}
unless( defined $$ref_excel ) { # if not running, start it
$$ref_excel = Win32::OLE->new('Excel.Application', 'Quit') or ($$r
+ef_excel_res = 0, $ew_message = "Could not create excel object");
} else {
$$ref_excel_res = 1;
}
$$ref_workbook = $$ref_excel -> Workbooks -> Add;
$$ref_sheet = $$ref_workbook -> Worksheets("Sheet1");
$$ref_sheet -> {Name} = "Raw Costs";
$$ref_sheet -> Activate;
}
#=====================================================================
+==========
#
# sub save spreadsheet
#
#
#=====================================================================
+===========
sub save_spreadsheet($$$$$$) {
my ($ref_excel, $ref_workbook, $ref_sheet, $given_spsh_dir, $given_sps
+h_name, $ref_save_res) =@_;
my ($spsh_full, $given_spsh_dir_mod, $spsh_save, $ew_message, $excel_r
+es, $en_err, $en_f, $en_num, $close_res, $delete_spsh_return_code);
# save spreadsheet
$$ref_excel->{DisplayAlerts}=0;
$spsh_full = $given_spsh_dir . '/' . $given_spsh_name . '.xls';
$spsh_full =~ s/\:\\\\/\:\\/;
$given_spsh_dir_mod = $given_spsh_dir;
$given_spsh_dir_mod =~ s/\:\\\\/\:\\/;
$ew_message = "[save_spreadsheet]just before saving part file <$spsh_f
+ull>";
$spsh_save = $given_spsh_dir_mod . '/' . $given_spsh_name . $config_da
+ta{cfg_quotation_spreadsheet_extension};
$spsh_save =~ s/\:\\\\/\:\\/;
$ew_message = "\n[save_spreadsheet]just before saving spreadsheet file
+ <$spsh_save>";
$excel_res = 1;
$$ref_excel->Workbooks(1)->SaveAs($spsh_save) or ($excel_res = 0, $ew
+_message = "Could not save spreadsheet <$spsh_save>");
$$ref_save_res = $excel_res;
}
#=========main===================================================
open_spreadsheet_for_excel_write (\$excel, \$workbook, \$sheet, \$exce
+l_res);
print "excel <$excel> workbook <$workbook> sheet <$sheet>\n\n";
$test_dir = "c:\\\\filedir_test";
$mkdir_res = mkdir($test_dir);
$test_dir .= "\\";
$test_dir_root = $test_dir;
print "for root dir <$test_dir> made result <$mkdir_res>\n";
if($mkdir_res == 1) {
# create directories
for($j = 1; $j <= 300; $j ++) {
$test_dir .= 'A';
$dir_length = length($test_dir);
$mkdir_res = mkdir($test_dir);
# print "directory length <$dir_length> make sucess <$mkdir_res
+>\n";
if($mkdir_res == 1) {
$filedir_res{$j}{MaxDirLength} = $dir_length;
$excel_name = '';
for ($k = 1; $k <= 300; $k ++) {
$excel_name = 'A' . $excel_name;
$dir_length = length($excel_name) + $ext_length;
$full_excel_name = $test_dir . '/' . $excel_name;
$full_excel_name_length = length($full_excel_name) + $
+ext_length;
$excelsave_res = 1;
$excel->Workbooks(1)->SaveAs ($full_excel_name) or (
+$excelsave_res = 0);
save_spreadsheet(\$excel, \$workbook, \$sheet, $test_d
+ir, $excel_name, \$excelsave_res);
# print " excel name <$full_excel_name> length <$dir
+_length> full path length <$full_excel_name_length> result <$excelsav
+e_res>\n";
if($excelsave_res == 1) {
$filedir_res{$j}{ExcelNameLength} = $dir_length;
$filedir_res{$j}{FullPathLength} = $full_excel_nam
+e_length;
} else {
$k = 301;
}
}
} else {
$j = 301;
}
}
}
open (FILDIROP, ">" . $test_dir_root . "file directory length.txt");
print "MaxDirLength,ExcelNameLength,FullPathLength\n";
print FILDIROP"\n\nMaxDirLength,xcelNameLength,FullPathLength\n";
foreach $j (sort {$a <=> $b} keys %filedir_res) {
print "$filedir_res{$j}{MaxDirLength},$filedir_res{$j}{ExcelNameLe
+ngth},$filedir_res{$j}{FullPathLength}\n";
print FILDIROP "$filedir_res{$j}{MaxDirLength},$filedir_res{$j}{Ex
+celNameLength},$filedir_res{$j}{FullPathLength}\n";
}
close(FILDIROP);
|