Re: Formatting a Date in a Cell in Excel

2005-12-19 Thread Dennis Kelly 
PS - you can also use [Format][cell][Number tab] and select custom. Then 
specify mmm  this will allow excel to treat it as a numeric 
quantity, and still have it be presented as you wish.


Glen Plantz wrote:


Hi Folks,
 
I'm having trouble formatting the Cell Contents of an Excel sheet with 
Win32::OLE.
 
I want to force the contents of a Cell that has a DATE value ( Dec 
2005 ) to be a string.

What happens is the Date value 'DEC 2005' in converted into 'Dec-05'.
 
Any help will be greatly appreciated
 
Thanks,


Glen Plantz
 
Here is my code;.

---
my ($currentMonth, $currentYear ) = split / /, 
$currentMonthYear;  # holds Dec 2005
 
# Now open the TEMPLATE FILE where this month value will be used...

# $CSAA_A2_RegionOffice

my $template_Workbook = $excel_app-Workbooks-Open($CSAA_A2_RegionOffice)
or die could not open $CSAA_A2_RegionOffice\n;
 
#now select first column heading where HOA will be used... and try 
putting the correct column heading in...

my $Sheet1 = $template_Workbook-Worksheets(1);
$Sheet1-Range(D2)-Select();

#now select correct array based on the '$currentMonth' value
my $mnth;
my $i = 0;
 
my $mnth = $HOA{$currentMonth}[$i];   # $mnth holds 'DEC'
 
#correct value... now try putting in the correct value...
 
my $monthYear = $mnth . '  ' . $currentYear;
 
print monthYear = $monthYear\n;
 
# my $dt = Variant(VT_DATE, $monthYear);

print $dt\n;
print $dt-Date(MMM ), \n;
 
my $formatedDate = $dt-Date(MMM ), \n;

print formatedDate = $formatedDate\n;
 
# this works, but the year has the format '05' see screen shot...

$Sheet1-Range(D2)-{'Value'} = $monthYear;
 
# this does not work

#$Sheet1-Range(D2)-{'Value'} = $formatedDate;
 
#this doesn't seem to effect the value entered...

#$Sheet1-Range(D2)-Format(MMM );
 
 




___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
 



--

R. Dennis Kelly



___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Re: Formatting a Date in a Cell in Excel

2005-12-19 Thread Dennis Kelly 
You can probably accomplish what you want by doing [Format][Cell][Number 
tab] and selecting text.
Excel likes to infer that cells are dates because then it can treat them 
as an ordered list, and you can do arithmetic like this day next week = 
today + 7. Under [Format][Cell][Number tab] the date selection has a 
number of good options for date presentation, but none seem to meet your 
needs.


Glen Plantz wrote:


Hi Folks,
 
I'm having trouble formatting the Cell Contents of an Excel sheet with 
Win32::OLE.
 
I want to force the contents of a Cell that has a DATE value ( Dec 
2005 ) to be a string.

What happens is the Date value 'DEC 2005' in converted into 'Dec-05'.
 
Any help will be greatly appreciated
 
Thanks,


Glen Plantz
 
Here is my code;.

---
my ($currentMonth, $currentYear ) = split / /, 
$currentMonthYear;  # holds Dec 2005
 
# Now open the TEMPLATE FILE where this month value will be used...

# $CSAA_A2_RegionOffice

my $template_Workbook = $excel_app-Workbooks-Open($CSAA_A2_RegionOffice)
or die could not open $CSAA_A2_RegionOffice\n;
 
#now select first column heading where HOA will be used... and try 
putting the correct column heading in...

my $Sheet1 = $template_Workbook-Worksheets(1);
$Sheet1-Range(D2)-Select();

#now select correct array based on the '$currentMonth' value
my $mnth;
my $i = 0;
 
my $mnth = $HOA{$currentMonth}[$i];   # $mnth holds 'DEC'
 
#correct value... now try putting in the correct value...
 
my $monthYear = $mnth . '  ' . $currentYear;
 
print monthYear = $monthYear\n;
 
# my $dt = Variant(VT_DATE, $monthYear);

print $dt\n;
print $dt-Date(MMM ), \n;
 
my $formatedDate = $dt-Date(MMM ), \n;

print formatedDate = $formatedDate\n;
 
# this works, but the year has the format '05' see screen shot...

$Sheet1-Range(D2)-{'Value'} = $monthYear;
 
# this does not work

#$Sheet1-Range(D2)-{'Value'} = $formatedDate;
 
#this doesn't seem to effect the value entered...

#$Sheet1-Range(D2)-Format(MMM );
 
 




___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
 



--

R. Dennis Kelly


___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Re: Formatting a Date in a Cell in Excel

2005-12-17 Thread Chris Wagner 
If u precede the data with an apostrophe then Excel will not try to
autoformat it.

At 03:35 PM 12/15/2005 -0800, Glen Plantz wrote:
I want to force the contents of a Cell that has a DATE value ( Dec
2005 ) to be a string.
What happens is the Date value 'DEC 2005' in converted into 'Dec-05'.

$formatteddate = 'DEC 2005;






--
REMEMBER THE WORLD TRADE CENTER ---= WTC 911 =--
...ne cede malis

0100

___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Formatting a Date in a Cell in Excel

2005-12-15 Thread Glen Plantz 
Title: Message



Hi 
Folks,

I'm having trouble 
formatting the Cell Contents of an Excel sheet with 
Win32::OLE.

I want to force the 
contents of a Cell that has a DATE value ( "Dec 2005" ) to be a 
string.
What happens is the 
Date value 'DEC 2005' in converted into 'Dec-05'.

Any help will be 
greatly appreciated

Thanks, Glen 
Plantz

Here is my 
code;.
---
my ($currentMonth, 
$currentYear ) = split / /, 
$currentMonthYear; 
# holds "Dec 2005"

# Now open the 
TEMPLATE FILE where this month value will be used...# 
$CSAA_A2_RegionOfficemy $template_Workbook = 
$excel_app-Workbooks-Open($CSAA_A2_RegionOffice)or die "could not 
open $CSAA_A2_RegionOffice\n";

#now select first 
column heading where HOA will be used... and try putting the correct column 
heading in...my $Sheet1 = 
$template_Workbook-Worksheets(1);$Sheet1-Range("D2")-Select();
#now select 
correct array based on the '$currentMonth' value my $mnth;my $i = 
0;

my $mnth = 
$HOA{$currentMonth}[$i]; # $mnth holds 'DEC'

#correct value... 
now try putting in the correct value...

my $monthYear = 
$mnth . ' ' . $currentYear;

print "monthYear = 
$monthYear\n";

# my $dt = 
Variant(VT_DATE, $monthYear);
print 
"$dt\n";print $dt-Date("MMM "), "\n";

my $formatedDate = 
$dt-Date("MMM "), "\n";print "formatedDate = 
$formatedDate\n";

# this works, but 
the year has the format '05' see screen 
shot...$Sheet1-Range("D2")-{'Value'} = 
$monthYear;

# this does not 
work#$Sheet1-Range("D2")-{'Value'} = 
$formatedDate;

#this doesn't seem 
to effect the value entered...#$Sheet1-Range("D2")-Format("MMM 
");


___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: Formatting a Date in a Cell in Excel

2005-12-15 Thread Jan Dubois 
Try something like this:

  $Sheet1-Range(D2)-{NumberFormat} = MMM ;

and please use plain text instead of HTML for mailing list traffic. :)

Cheers,
-Jan


___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs