Re: error when I try to execute the SaveAs function in Win32::OLE when I try to save an Excel worksheet to a new format.
Quoting Glen Plantz [EMAIL PROTECTED]:
Hi Folks,
I'm getting an error when I try to execute the SaveAs function in
Win32::OLE when I try to save an Excel worksheet to a new format.
( I'm using the csv format here, but I _really_ want to save each
sheet in the workbook as a Tab delimited file, I could not find any
format strings that looked like they would save to a tab delimited file
)
Here is the text of the error;
Invalid number of parameters
in METHOD/PROPERTYGET SaveAs
I've searched for examples of executing this call, but I haven't found
anything that works.
Here is a snippet of code showing my execution:
#
***
my $count = $excel_Workbook-Worksheets()-{Count};
print count = $count\n;
my $sheetName;
my $newEmailFileName;
my $newFileFormat = 'xlCSVMDOS';
my $newFile;
foreach (1..$count) {
my $currSheet2 = $excel_Workbook-Worksheets($_);
$sheetName = $currSheet2-{Name};
print Sheet Name = $sheetName\n;
$newEmailFileName = $Drive . ':' . '\\' . $Directory
.'\\' .'AmFam' . $currentMonth . $year . '_' . $sheetName;
$newFile = $Drive . ':' . '\\' . $Directory .'\\'
.'AmFam' . $currentMonth . $year . '_' . $sheetName . .csv;
$currSheet2-SaveAs( $newEmailFileName, $newFileFormat,
undef, undef, undef, undef, undef, undef, undef, undef );
Try:
$currSheet2-SaveAs({Filename =$newEmailFileName,
FileFormat = xlCSV});
if( Win32::OLE-LastError() ) {
print $sheetName . ' Error saving: ' .
Win32::OLE-LastError() .
\n; }
}
#
*
Could some kind soul help me with this?
Thanks in advance for any tips.
Glen Plantz
Nathaniel Bartusiak
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: insert picture in Word using Perl OLE
Quoting just me [EMAIL PROTECTED]:
.
$msword-{Visible} = 1 ;
$msword-{DisplayAlerts} = 1 ;
my $doc1 = $msword-Documents-Add() ;
$doc1-Shapes-AddPicture(D:\logo.gif,0,1);
...
The picture doesn't show up in the word doc.
Is this the correct why to insert pictures into Word
using OLE? I saw another Class called InlineShapes.
Should i use that instead?
thanks
You have two options:
my $image_1 = $doc1-Shapes-AddPicture(D:\logo.gif',
False, True, 200, 200, 50,50); # location 200x200, size 50x50
# or
my $image_2 = $doc1-InlineShapes-AddPicture('D:\logo.gif', False, True);
Nathaniel Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Excel color to RGB
Hello
In Perl, I need to convert a color use by a cell in Excel to a RGB color.
$indexcolor=$Book-Worksheets(example)-Range(A1)-Characters-Font-ColorIndex;
How to convert the $indexcolor (which is a long) to ($r,$g,$b) ?
Someone know how to do it ?
Actually I think you should use Color. ColorIndex seems to return a
number 1-56. After you get a number with Color you will need to seperate
it. I found the folowing sub to generate the number from the rgb:
sub RGB { $_[0] + ($_[1] *256) + ($_[2] *256** 2) }
It is then easy to create a reverse of the RGB sub.
$indexcolor=$Sheet-Range(A5)-Characters-Font-Color;
($red,$green,$blue) = reverse_rgb($indexcolor);
sub reverse_rgb
{
...
}
--
Nathaniel Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Excel stuff.
Currently I'm using Spreadsheet::WriteExcel.
I am trying to work out how to do the following...
1. Merge cells.
2. Make all columns AutoFit, after the data has been entered.
3. Freeze panes.
I don't use Spreadsheet::WriteExcel but if you can use Win32::OLE then
this should help.
use Win32::OLE;
use Win32::OLE::Variant;
my $vtfalse = Variant(VT_BOOL, 0);
my $vttrue = Variant(VT_BOOL, 1);
my $vtpages = Variant(VT_I4, 1);
$Excel = Win32::OLE-GetActiveObject('Excel.Application') ||
Win32::OLE-new('Excel.Application');
$Excel-{'Visible'} = 1;#0 is hidden, 1 is visible
$Excel-{SheetsInNewWorkBook} = 1;
$Book = $Excel-Workbooks-Add();
$Sheet = $Book-Worksheets(1);
$Sheet-{Name} = 'My test worksheet';
$Sheet-Range('a1')-{Value} = 'A1';
$Sheet-Range('b2')-{Value} = 'B2';
my @columnheaders = qw(A:B);
foreach my $range(@columnheaders){
$Sheet-Columns($range)-AutoFit();
}
$mynextcol = 'b';
for (my $n=1;$n5;$n+=2){
my $range = $mynextcol++ . '1:' . $mynextcol++ . '1';
$Sheet-Range($range)-Merge();
$Sheet-Range($range)-{HorizontalAlignment} = xlHAlignCenter;
}
#$Sheet-Range('b3')-Select(); # cell
#$Sheet-Range('b:b')-Select(); # column
$Sheet-Range('3:3')-Select(); # row
$Excel-ActiveWindow-{FreezePanes} = $vttrue;
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
RE: Excel stuff.
Nathaniel,
I also use the OLE and I think you showed me how to freeze panes long
ago. Anyways, the problem that I ran into using that command was: If the
pane has already been frozen when you execute the same command, you get
fatal error. So, do you know of a way to wrap a test around the freeze?
Thanks for the info, I didn't know about that problem. If you really want
to test it then use an eval and check $@, its like a try-catch in Java.
Or if you just want to set the freeze, all you have to do is unfreeze the
sheet first.
use Win32::OLE::Variant;
my $vtfalse = Variant(VT_BOOL, 0);
my $vttrue = Variant(VT_BOOL, 1);
...
$Sheet-Range('3:3')-Select(); # row
$Excel-ActiveWindow-{FreezePanes} = $vttrue;
#unfreeze
$Excel-ActiveWindow-{FreezePanes} = $vtfalse;
$Sheet-Range('4:4')-Select(); # row
$Excel-ActiveWindow-{FreezePanes} = $vttrue;
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
RE: Excel stuff.
Hi Nathaniel,
Thanks for that, it's very helpful. I'm now trying to switch from
Spreadsheet::WriteExcel, to Win32::OLE.
The biggest problem I seem to have now is translating this command:
my $temp = [EMAIL PROTECTED];
$page}-write_row($row, 5, $temp);
Can you help?
I don't know if this does everything that write_row does but it will write
an array in some cells.
@row = ('x','y','z');
$Sheet-Range('d3:f3')-{Value} = [EMAIL PROTECTED];
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: How do I measure text length? (+ How to measure page-width)
PPS: Summary of answers to other questions:
FreezePanes (to make only a lower/to-right section scroll) works on a
single cell, not a row or column. Do cellRange-Select;
app-ActiveWindow0{FreezePanes} = 1;
FreezePanes can work on a row or column, if you select that range.
code
use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
my $vttrue = Variant(VT_BOOL, 1);
$Excel = Win32::OLE-GetActiveObject('Excel.Application') ||
Win32::OLE-new('Excel.Application');
$Excel-{'Visible'} = 0;#0 is hidden, 1 is visible
$Excel-{DisplayAlerts}=0;#0 is hide alerts
# Create New Workbook
$Excel-{SheetsInNewWorkBook} = 1;
$Book = $Excel-Workbooks-Add();
$Sheet = $Book-Worksheets(1);
$Sheet-Activate();
$Sheet-{Name} = 'My test worksheet';
$Sheet-Range('b3')-Select(); # cell
# or
$Sheet-Range('b:b')-Select(); # column
# or
$Sheet-Range('3:3')-Select(); # row
$Excel-ActiveWindow-{FreezePanes} = $vttrue;
$Book-SaveAs({Filename ='C:\all.xls',
FileFormat = xlWorkbookNormal});
$Book-Close(); # or $Book-Close(0);
undef $Sheet; # needed if sheet has been activated
$Excel-Quit();
undef $Excel;
/code
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Read entire row from excel into hash.
Guys,
I'm reading data from an excel spreadsheet, using win32-ole.
Rather than having lots of lines like this...
my $varA = $active_sheet-Cells($working_row,A)-{Value};
my $varB = $active_sheet-Cells($working_row,B)-{Value};
my $varC = $active_sheet-Cells($working_row,C)-{Value};
my $varD = $active_sheet-Cells($working_row,D)-{Value};
Is there a nice way of doing domething like this...
my @vars = $active_sheet-Cells($working_row)-{Values};
The thing is, I don't know how many columns there are likely to be in
the spreadsheet, so I just want to grab everything for now.
Actually, the perfect solution for me would be to end up with a hash,
where the keys are A, B, C...Z, AA, AB, etc, so I can get the value by
referencing the column as it looks in the spreadsheet.
Not very clear, I know, but hopefully you'll know what I mean.
I don't think there is a function to do what you want, but you could write
one. To begin with you need to know the number of columns:
my $LastRow = $active_sheet-UsedRange-Find({What=*,
SearchDirection=xlPrevious,
SearchOrder=xlByRows})-{Row};
my $LastCol = $active_sheet-UsedRange-Find({What=*,
SearchDirection=xlPrevious,
SearchOrder=xlByColumns})-{Column};
Then you would loop through the columns adding the vaue to a hash, which
could then be returned from the function.
$row{$currentcol} =
$active_sheet-Cells($working_row,$currentcol)-{Value};
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: win32::ole open, copypaste, create new, save document
Now Iam searching for the solution to do the following with win32::ole
-open .doc file- no problem
-make and copy a range of pages- problem
-create a new document - no problem
-paste into new document- problem
-close original - no problem
-save new document - problem
-close new document - no problem
Is it possible to copypaste like that using win32::ole?
Yes, you just select a range then copy. The same thing for paste. I
don't know how to define a range as only x pages though. This should get
you started.
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Word';
$Win32::OLE::Warn = 3; # Die on Errors.
$Word = Win32::OLE-GetActiveObject('Word.Application') ||
Win32::OLE-new('Word.Application');
$Word-{'Visible'} = 1;
$Word-{DisplayAlerts} = 0;
my $File = $Word-Documents-Open('C:\tmp.doc'); # open txt file
my $myRange = $Word-ActiveDocument-Content;
$myRange-Select();
$myRange-Copy();
sleep(5);
my $oDoc = $Word-Documents-Add();
my $myRange2 = $Word-ActiveDocument-Content;
$myRange2-Select();
$myRange2-Paste();
sleep(5);
$Word-ActiveDocument-SaveAs({FileName = 'c:\tmp2.doc',
FileFormat = wdFormatDocument});
$File-Close();
$oDoc-Close();
$Word-Quit();
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Excel Cell Value Alignment
Sorry forgot to change the label
I am tring to align the test in the cell but it no workie and it is
driving me nuts
$RangeValue = J1;
$Alignment = xlCenter;
$Working_Alignment = $Book-Worksheets($SHEET_NAME);
$Working_Alignment
-Range($RangeValue)-{HorizontalAlignment} = $Alignment;
Almost, try this:
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE-GetActiveObject('Excel.Application') ||
Win32::OLE-new('Excel.Application');
$Excel-{'Visible'} = 1;#0 is hidden, 1 is visible
$Excel-{DisplayAlerts}=0;#0 is hide alerts
# Open new Worksheet
$Excel-{SheetsInNewWorkBook} = 1;
my $Book = $Excel-Workbooks-Add();
my $Sheet = $Book-Worksheets(1);
$Sheet-Activate();
my $range = 'A1';
$Sheet-Range($range)-{Value} = 'abc';
$Sheet-Range($range)-Interior-{ColorIndex} = 27;
$Sheet-Range($range)-Font-{FontStyle}=Bold;
$Sheet-Range($range)-{HorizontalAlignment} = xlHAlignCenter;
$Sheet-Range($range)-Font-{Underline} = xlUnderlineStyleSingle;
# or
$Sheet-Range($range)-Font-{Underline} = 2;
sleep(5)
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Search and replace in msword .doc
Use 'or die Win32::OLE-LastError();' to see what the error is. In this
case it was 'Type mismatch' on the line with Ecexute. Then look in the
Visual Basic Object Browser (in Word, under the tools-maco menu is the
Visual Basic Editor, you use this to look at macros that you want to do in
perl, in the VBE under the View menu is the Object Browser). Here you can
see that 'Const wdReplaceAll = 2'. So, replace wdReplaceAll with 2.
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
Greetings to all,
i want to do some 'find and replace' on a .doc file.
Sample i found on this mailing list was:
-
use strict;
use Win32::OLE;
use Win32::OLE::Const;
my $wd = Win32::OLE::Const-Load(Microsoft Word 9.0 Object Library);
my($outputFile) = 'file.doc';
my($word) = Win32::OLE - new('Word.Application', 'Quit');
$word - {Visible} = 1; # Watch what happens
my($doc) = $word - Documents - Open(/perl2exe5.03/wind_spl/ml1.doc);
my $search = $doc-Content-Find;
my $replace = $search-Replacement;
$search-{Text} = searched;
$replace-{Text} = replace;
$search-Execute({Replace = wdReplaceAll});
$doc - Close();
$word - Quit();
-
But it does not work: replacement does not happen.
What is wrong in this code?
Thanks for any answer.
Philippe Paclet
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: inserting text into word with OLE
This may not be the best way but it works for me.
my $doc = $word - Documents - Open(c:/test.doc);
my $oRange = $doc-Range();
my $oRange-EndOf;
my $oFont = $oRange-{Font};
$oRange-{Text} = Hello;
$oFont-{Color} = $clBlue;
$oRange-{Text} .= \nWorld;
--
Nathaniel G. Bartusiak
TTMS, Keesler AFB
I have been trying to figure out a way to insert text at the end of a
Word document. I know you can insert with
$word-Selection-TypeText(text here);
but if I am correct I need to place the cursor at the end of the file in
order to insert there. I have tried looking for the control to place
the text cursor at the end of the file but have been unsuccessful, does
anyone know how to do this or is there a better way to insert text at
the end of a file?
Thanks in advance
Eric
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Dividing a Word Document into Columns
Instead of:
$Word-ActiveDocument-PageSetup-{TextColumns} =
$Word-SetCount({NumColumns=1)|| die can't create columns.
Win32::OLE-LastError();
Try this:
$Columns = $Word-ActiveDocument-PageSetup-{TextColumns};
$number = $Columns-{Count};
print --$number ---\n;
$Columns-Add()|| die can't create columns: . Win32::OLE-LastError();
$number = $Columns-{Count};
print --$number ---\n;
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Dividing a Word Document into Columns
Instead of:
$Word-ActiveDocument-PageSetup-{TextColumns} =
$Word-SetCount({NumColumns=1)|| die can't create columns.
Win32::OLE-LastError();
Try this:
$Columns = $Word-ActiveDocument-PageSetup-{TextColumns};
$number = $Columns-{Count};
print --$number ---\n;
$Columns-Add()|| die can't create columns: . Win32::OLE-LastError();
$number = $Columns-{Count};
print --$number ---\n;
Nathaniel G. Bartusiak
TTMS, Keesler AFB
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Win32::OLE MS Word
This will insert text at the beginning of a document.
use strict;
use warnings;
use Win32::OLE;
my $oWord = new Win32::OLE(Word.Application);
my $clBlue = 16711680;
my $clGreen = 32768;
my $oDoc = $oWord-Documents-Add();
$oWord-Selection-TypeParagraph;
$oWord-Selection-Font-{Size} = 14;
$oWord-Selection-Font-{Bold} = 998;
$oWord-Selection-Font-{Name} = Arial;
$oWord-Selection-Font-{Color} = $clGreen;
$oWord-Selection-TypeText(Detailed Reports);
$oWord-Selection-TypeParagraph;
$oWord-Selection-Font-{Size} = 10;
$oWord-Selection-Font-{Bold} = 998;
$oWord-Selection-Font-{Name} = Arial;
$oWord-Selection-Font-{Color} = $clBlue;
$oWord-Selection-TypeText(Detailed Reports2);
$oWord-Selection-TypeParagraph;
$oDoc-SaveAs('c:\my.doc');
$oWord-Quit();
Nathaniel G. Bartusiak
TTMS, Keesler AFB
Hi Toby
I ran your program on my machine (Win2K pro) with Office 2k.
Unfortunately both words in the word doc were written in blue and not
one in green and on in blue as the program suggests they should be.
I think the Range has be moved before the color is changed from green
to blue. BUT how?
thanks
Syl
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
___
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
