Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)
by Corion (Patriarch) on Jul 26, 2007 at 11:20 UTC
|
In addition to this very good introduction, here's my approach to automating things in Excel that I know how to do manually in Excel. These steps apply to any (Office) application that has a macro recorder:
- Start the Excel Macro Recorder
- Perform the desired action(s) manually, for example, delete a row or bolden text or create a pivot chart
- Stop the Excel Macro Recorder
- Inspect the generated VB code
- Check that the generated VB code does what you want
- Translate the generated VB code to Perl / Win32::OLE invocations
That's all there is to it and it sure beats poring over the documentation to find out the needed function names.
Similar lists exist at Re^3: Problems with Microsft's new Office 'XML' and Re: Convert word(.doc) file to html file, but as this appears to be a good starting page for people working with Win32::OLE, I figured to put it here as well.
| [reply] |
|
I am trying to do this is my program.
Open an Excel File
Search for a value in the excel file
When i find the value i want to know the location of that value.
For example if my excel file has 3 values
A1-->apple
A2-->Orange
A3-->Grapes
When i search the file for orange..it should say that orange found and return me the value A2.
I am not able to find the location.
Please help
| [reply] |
|
| [reply] [d/l] [select] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by tgo80 (Initiate) on Jun 21, 2002 at 20:00 UTC
|
Hey there,
I just discovered the monks! I havent been a Perl programmer for a long time... its kinda new.
Thank God for your "Finding the last Column and Row"! I couldn't have done anything without it! I tried to do it myself, or look on the Web, I couldn't find anything 'till I found this one!
But... unless this is asking to much...
HOW DOES IT WORK? I dont get it...
Why is it searching for a "*"?
Thanks!
Keep doing a great job!
To
| [reply] |
|
I'm searching for everything (or better, anything), starting at the end (either last row, or last column). The row/column properties return the first area in the specified range that matches, so in this case, it starts at the end of the sheet, either last row or last column,(because of the SearchDirection=>xlPrevious) looking for anything, finds it in the last row/column with data in it and returns that (as an int).
Hope that makes sense. C-.
| [reply] |
Last cell?
by Nkuvu (Priest) on Dec 06, 2002 at 21:58 UTC
|
What's the advantage to the method you propose for finding the last column and row as opposed to something more like
# Assuming all variables declared in a "my" statement above.
# Also assuming $worksheet is pointing to a WorkSheet object.
$last_cell = $worksheet->Range("A1")->EntireColumn->
SpecialCells(xlCellTypeLastCell)->{Address};
($last_col, $last_row) = $last_cell =~ /\$(\w+)\$(\d+)/;
?
(Note that the last cell returned by this method does not depend on the Range("A1") specified -- it still returns the last cell properly. For example, N23 (as text $N$23) if N23 were the last cell in the sheet.) | [reply] [d/l] |
|
Upon further investigation, I have found that this is not reliable if the columnar data is not of equal row size. It returns the last row of the right most column, which is incorrect in some cases.
The methods I used in the examples return the last row (over all) and the last column (over all) vs finding the location of the last cell in the last column
So, if the data on the worksheet is symmetric, your function works faster, however yours would not be reliable to, say, put edges around all the cells in a data set that has columns of different row sizes
C-.
---
Flex the Geek
| [reply] |
|
If the method you'd come up originally doesn't work fine with non "square" sheets then what would you recommend.
Furthermore, I can't seem to be able to make your original last row/column script work. The dos prompt tells me the following :
Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou
+rce\readExcel.pm line 30.
Bareword "xlByRows" not allowed while "strict subs" in use at D:\sourc
+e\readExcel.pm line 30.
Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou
+rce\readExcel.pm line 33.
Bareword "xlByColumns" not allowed while "strict subs" in use at D:\so
+urce\readExcel.pm line 33.
What should I do ?
edit (broquaint): changed <pre> to <code> tags | [reply] [d/l] |
|
Sorry about the previous post - I found out what wasn't working about the xlPrevious and I included the
use Win32::OLE::Const 'Microsoft Excel'; line.
Thanks again and great tutorial. How about PowerPoint someday ? | [reply] |
|
|
|
| [reply] |
|
| [reply] |
|
What is the best way to determine all of the valid fields and subfields for Perl/OLE/Excel information. It is only by studying examples did I learn that Cell->Interior->ColorIndex gets the cell background color, and everything I have learned is by Web examples. Is there a definitive document for all of the possible fields?
| [reply] |
|
|
|
Re: Using Win32::OLE and Excel - Tips and Tricks
by monarch (Priest) on Jul 26, 2005 at 02:46 UTC
|
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Office';
use Win32::OLE::Const 'Microsoft PowerPoint';
use strict;
..then setting warnings as per the Excel tutorial..$Win32::OLE::Warn = 3; # die on errors
Next, name the file that will be saved: my $filename = "c:\\temp\\testpower.ppt";
.. and then fire up the PowerPoint applicationprint( "Starting Powerpoint Object\n" );
my $power = Win32::OLE->GetActiveObject('Powerpoint.Application') ||
Win32::OLE->new('Powerpoint.Application', 'Quit');
Create a presentation (much like creating a workbook in Excel) print( "Creating a presentation\n" );
my $ppt = $power->Presentations->Add();
$ppt->SaveAs($filename);
(and save!).
Create a slide (like creating a worksheet in Excel) print( "Creating a slide\n" );
my $slide = $ppt->Slides->Add(1, ppLayoutBlank);
$ppt->SaveAs($filename);
Insert a picture into my slide my $pname = 'C:\WINNT\Web\Wallpaper\Fall Memories.jpg';
my $shape = $slide->Shapes->AddPicture( $pname, msoFalse,
msoTrue, 20, 1 );
# scale to 50% of original size
$shape->ScaleHeight( 0.5, msoTrue, msoScaleFromTopLeft );
$shape->ScaleWidth( 0.5, msoTrue, msoScaleFromTopLeft );
$ppt->SaveAs($filename);
Insert a table print( "Adding a 4 wide by 3 high table\n" );
my $table = $slide->Shapes->AddTable( 3, 4, 1, 100 );
my $columns = $table->Table->Columns->Count;
my $rows = $table->Table->Rows->Count;
for ( my $row = 0; $row < $rows; $row++ ) {
for ( my $col = 0; $col < $columns; $col++ ) {
my $cell = $table->Table->Rows($row+1)->Cells($col+1);
my $textframe = $cell->Shape->TextFrame;
$textframe->TextRange->{Text} = "$col,$row";
$textframe->TextRange->Font->{Bold} = msoFalse;
$textframe->TextRange->Font->{Name} = "Arial";
# set text size AFTER changing text
$textframe->TextRange->Font->{Size} = "12";
}
}
$ppt->SaveAs($filename);
There's a lot more that can be done, of course, and the best reference is at MSDN's Visual Basic Reference for PowerPoint. All the methods and properties used here are listed there. In addition, when calling a method, you often can leave parameters off the end and defaults will be used for you.
Another note to add.. if you're not running PowerPoint at the time this script is run, then it will happen in the background. The only way to know for sure if it worked is to open the file you created and visually verify everything was added. | [reply] [d/l] [select] |
|
Bareword "msoFalse" "msoTrue" "msoScaleFromTopLeft" not allowed while "strict subs" in use at PPT_ex_2.pl line 25.
How can I use the MSWin32 constants without a reference?
Thanks...
| [reply] |
|
I'm seeing the problem with MSWin32 constants and 'use strict'. Anyone know what the problem is ? Any workaround?
Thanks,
--
Charles DeRykus
| [reply] |
|
|
|
|
thank ou for sharing the way to handle poewrpoint through perl....i am a beginner in perl and i am trying to display an image on the slide but i am geting the same error as mentioned in the first comment...could you please elaborate on it ?
| [reply] |
|
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jun 25, 2002 at 17:06 UTC
|
Very good and useful article!!!
By the way, I'm using it to update values on Excel, but I'd like to know if it's possible to update a cell while the excel file is open. I tried the simple way :
my $oExcel = Win32::OLE->new('Excel.Application','Quit');
my $oBook = $oExcel->Workbooks->Open("$excelFile");
my $oSheet = $oBook->Worksheets(1);
...
$oBook->Save;
$oBook->Close;
But it doesn't seem to work...
Any idea????
Thanx | [reply] |
|
I'm not sure that I understand your question. ALL of the above code assumes that the worksheet is open (Either by creating a new one, or by opening an existing one).
Are you getting some kind of error, or are you not seeing what you expect? The code that you seem to have omitted would probably help me determine what the matter is, and don't forget to use <CODE> </CODE> tags, it makes for much easier reading.
C-.
---
Flex the Geek
| [reply] |
|
In fact, my problem is more about Excel rather than perl (I think).
I was wondering if there is an option so when my perl program update a value in a cell I can see it immediately in Excel (the file is always open on my desktop)
| [reply] |
|
|
The excel must be opened by the script. if the file is already opened the script will fail. In other words your script cannot modified an opened file if it was not opened by your script
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Discipulus (Canon) on Aug 20, 2003 at 08:08 UTC
|
thanks for this tutorial but I needed to your line
my $Sheet = $Book->Worksheets("Sheet1");
#in this way to make it run
my $Sheet = $Book->Worksheets(1);
or I get this error:
Win32::OLE(0.1601) error 0x8002000b: "Indice non valido"
in METHOD/PROPERTYGET "Worksheets" at C:\prova\OLEexcel.pl line 20
why ?? thanks 4theTuT Lor* | [reply] [d/l] |
|
Looks to me like you are using an italian?? version of Excel, where I would guess that "Sheet1" is named "Foglio1" or some such.
Accessing the sheet through index ie "1" in your example works, because you avoid the language differences
HTH
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by pinguxx (Initiate) on Nov 26, 2005 at 06:33 UTC
|
is there anyway to do freeze_panes with win32-ole, i see that spreadsheet::writeexcel does, can win32 do it? | [reply] |
|
$sheet->Cells( $y, $x )->Select();
$Excel->ActiveWindow->{FreezePanes} = 1;
| [reply] [d/l] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by kersht (Initiate) on Jan 15, 2003 at 19:16 UTC
|
Great "tutorial" on Excel with Win32::OLE! I am trying to extract data from one Excel file, and input that data into another Excel file. The problem comes when trying to have two open Excel files, and make the data transfer happen in the same block. I was thinking of creating a subroutine for each function -- one for extracting the data and sticking it in an array, and one for inputing the data from the array into the other spreadsheet ...
However, before I go through the effort, I thought I'd ask the Pro's!
Any good guidance or experience in this or a similar Excel problems?
Thanks ... | [reply] |
Using Perl from Excel (Re: Using Win32::OLE and Excel - Tips and Tricks)
by Corion (Patriarch) on Apr 11, 2011 at 07:36 UTC
|
If you are caught in the mirror universe and need to use Perl from within Excel, I know two ways of doing that. The first way is a very easy way, and I've supplied information to Excel that way. You simply write a HTTP server in Perl that serves a page that mostly consists of a <TABLE>. That table can be easily accessed and refreshed from within Excel. No special setup of Excel is needed. You just need to link the data in the sheet to the external HTTP address.
The second way would be through XLLoop, an Excel Add-In that allows you to write Excel function in other languages, and even serve the requests from a central server. I haven't used this, but it could be a way to avoid Visual Basic as glue.
| [reply] [d/l] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on May 09, 2002 at 20:35 UTC
|
I couldn't get your snippet above to get the outer edges of the rows and columns to work. I get the error
#[Thu May 9 16:39:02 2002] fndsxmit.pl: Win32::OLE(0.1501) error 0x80
+020005: "Ty
#pe mismatch"
#[Thu May 9 16:39:02 2002] fndsxmit.pl: in METHOD/PROPERTYGET "Fi
+nd" argumen
#t "SearchDirection" at fndsxmit.pl line 1350
What am I doing wrong?
| [reply] [d/l] |
|
hmmm. It makes me think that you didn't include the line:
use Win32::OLE::Const 'Microsoft Excel';
C-. | [reply] [d/l] |
|
ah, exactly right. many thanks. I'm continuously amazed and impressed by the Perl community.
Dooj
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Reverend Phil (Pilgrim) on Oct 30, 2002 at 19:39 UTC
|
++ wonderful tutorial. I've been using much of this functionality for some time, but I can slick a few snips from you to clean up a few lines here and there. And thanks for pointing out the DisplayAlerts property. Man, there's a great deal of hubbub in that object. =)
-=rev-= | [reply] |
|
I needed to open a spreadsheet with links to external files, which or may not be open. The following snippet did the trick:
my $Book = $Excel->Workbooks->Open("$EXCELFILE", TRUE);
| [reply] [d/l] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jun 07, 2002 at 22:20 UTC
|
thanks-great stuff mate
-dan | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Sep 30, 2002 at 12:51 UTC
|
Hi I've got a little problem with reading unicode characters from an Excel Sheet,
I thought use utf8; would do the trick, but my cells contain only question marks instead of the unicode characters from the perl point of view.
Text => ???
Value => ???
Value2 => ???
This is perl, v5.6.1 built for MSWin32-x86-multi-thread, from ActiveState.
I tried it with Spreadsheet::ParseExcel; before, same result. Feels like I'm forgetting something important :-\
| [reply] |
|
use Win32::OLE qw(CP_UTF8);
...
# Work in unicode!
$Win32::OLE::CP = CP_UTF8;
...
You can use Unicode::String to unpack() the string to look at each unicode char (which was what I had to do).
Cheers
---Lars | [reply] [d/l] |
|
Actually, using Unicode::String as a container for your data is not needed (in fact, it will croak on acctented chars and other punctuation). Just use the string as you 'normally' do, i.e. to look at each char:
for my $uchar (split(//, $text)) {
my $ord = ord($uchar);
...
}
While it seems natural to me now, it took me some time to locate that my troubles with unicode strings was *using* Unicode::String... :-)
---Lars
| [reply] [d/l] |
|
Do you have an example of a your Unicode string so that I can test? Odds are you are going to be playing with Variant (specifically VT_BSTR), but I don't want to steer you in the wrong direction.
C-.
---
Flex the Geek
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by poolboi (Acolyte) on Jan 28, 2008 at 08:50 UTC
|
hm..i got a perl program that needs to be appended to the last row using excel
how can i do this using win32::OLE? thanks
use Win32::OLE;
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Qui
+t;})
or die "Oops, cannot start Excel";
}
# get a new workbook
$book = $ex->Workbooks->Add;
$sheet = $book->Worksheets(1);
# write a 2 rows by 3 columns range
$y = 2;
$x = 1;
$sheet->Range("A$x:J$y")->{Value} = [['Date','Total (IN)','Suc
+c (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ
+(OUT)','MO(IN)','MO(OUT)'],
[$date, $total_in, $succ_in
+,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
$sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec']
+,
[$max_pk_msg,$pk_msg]];
foreach(@parameters)
{
$sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
$sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
}
# print "XyzzyPerl"
$array = $sheet->Range("A2:I1")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}
# save and exit
$book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\pe
+rl\\$save_file_name.xls") ;
undef $book;
undef $ex;
| [reply] [d/l] |
|
I was looking at this tutorial, http://www.ngbdigital.com/perl_ole_excel.html.
Is there a way that I can sort a column without knowing the lowest & highest range? Thanks.
| [reply] |
|
Is there any way to define x-axis, y-axis and Chart titles, if i make chart object like $chart = $sheet->ChartObjects->Add(100, 100, 400, 300);
| [reply] |
Win32::OLE Excel: script stops due to high excel load
by Ratazong (Monsignor) on Jul 02, 2012 at 10:58 UTC
|
Dear monks!
I think the following discovery might be useful for you:
I openend an Excel-workbook to extract some data, as instructed in the node above:
...
my $Book = $Excel->Workbooks->Open($excelfile);
my $Sheet = $Book->Worksheets("Sheet1");
...
This worked with some files, but with one I repeatedly got an error-message that the operation could not be
done due to some high load in Excel.
The resulting investigation showed that this special file contained very complex pivot-tables.
As a result, opening it seems not to be finished when I tried to access the worksheet. Strange.
Fortunately, this could be solved very easily with the following code:
...
my $Book = $Excel->Workbooks->Open($excelfile);
sleep(1); # give Excel time
+ to open the file
my $Sheet = $Book->Worksheets("Sheet1");
...
Hope this node helps you (and saves you some debugging-time) in case you encounter the same situation!
Rata
| [reply] [d/l] [select] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by kevinhat (Initiate) on Aug 11, 2006 at 23:25 UTC
|
In reference to the "Adding a Chart Object" section above, is there a way to pass an array to the "$Chart->Chart->SeriesCollection(1)->{Values}=" statement? | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Sep 12, 2007 at 21:09 UTC
|
After 2 weeks of losing hair and sleep, this one article shows enough detail for me to move past 'hanging open' excel files and several other issues. I'm writing a tool that has to scan several hundred XLS files in a swamp of folders and extract specific data. Thank you for writing this article, my program will behave so much better for it.
I noticed your request for handling square brackets and other strange animals in links. Just use the hex representation for them, as in www.badly%5Bformed_url%5D.as.only.MS.can.do | [reply] [d/l] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 14, 2003 at 18:23 UTC
|
Hi cacharbe,
I'm trying to fill up an excel sheet following a format wich is stored in an excel template (just in a regular excel file).
This tutorial is really help me, but there's still something hard to do : find out wich cells are merged in the template and merging the matching cells in the output file.
I have found the MergeCells property, a boolean that tells you if there are a merged cell in the parent object.
MergeArea returns a range of merged cells containing a given cell.
My problem is I cant find a way to extract the row & col indexes to reproduce the merge on the output file.
Any idea about, it.
mehdi aziz
mehdiaziz@lycos.com | [reply] |
|
I have a problem with this:
my $indents = $Sheet->Range( "A1:B2" )->{'IndentLevel'};
If i use 'Value' in stead of IndentLevel everything works fine.
Any ansers?
| [reply] [d/l] |
|
Hi aziz,
Sorry that I am asking you a question instead of an answer.Can u give me the syntax to merge cells in excel using perl(with OLEs)?
Thanks,
Sree
| [reply] |
|
I know how to read an Excel cell's hyperlink value.
What is the syntax to set or change a cell's hyperlink value?
-docuSwear
| [reply] |
|
|
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jul 13, 2008 at 11:23 UTC
|
Many thanks for this. It was very useful, and the trick about finding the last row and column was exactly what I was looking for at the time.
Graeme | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Dec 24, 2003 at 14:56 UTC
|
++article :-D ---> many thanks.
Being new to perl this article is the closest thing I have for the bible right now...
But... I have a few more question: I'm looking for a way to search for a certain value in my spreadsheet and getting it's location (inside my selected area).
Finding is the easy part:
$Sheet->Range("A1:B10")->Find("text to find")
but now what???
10x a 10^6,
grupy. | [reply] |
|
Hi,
If you look at the VB from a macro it is... Cells(r,c).Find(...).Activate which tells you that the $Sheet->Range("A1:B10")->Find() operation is returning something... that you can "->Activate" it means that it is a Range object you're getting back... so you can try
my $Range=$Sheet->Range("A1:B10")->Find("bob");
HTH - Mark
| [reply] [d/l] [select] |
|
Wow... Thanks a lot!!! it was a great help, even though it took me a while to figure out how to extract the info I needed from the return value (it returns a hash so I had to do something like casting just to see the possible values...)
cheers for the quick (and usefull) reply!
grupy.
| [reply] |
|
Re: Using Win32::OLE and Excel - Tips and Tricks
by davies (Monsignor) on Apr 23, 2005 at 21:04 UTC
|
Your reference to the Jan Dubois article gave me a 404 error. Am I just being stupid? I found this via google. Is it the same thing?
Thanks for the tutorial,
Regards,
John Davies | [reply] |
|
Yeppers, noted and changed.
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Aug 30, 2011 at 19:24 UTC
|
Thanks for the informative post. I'd like to add that, for Excel, if you want to save your output in PDF format, you need to use "57" in a SaveAs call:
$Book->SaveAs($pdf_file_name,57);
This will save the first sheet as PDF. Does anyone know how to tell Excel to save the entire workbook as PDF?
TIA,
Bill
| [reply] |
|
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by iamaids (Initiate) on Nov 24, 2005 at 17:58 UTC
|
Thanks for the article. The following code doesn't work on my machine:
my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop xlI
+nsideHorizontal xlInsideVertical);
$range = "b1:c56";
foreach my $edge (@edges){
with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)),
LineStyle =>xlContinuous,
Weight => xlThin ,
ColorIndex => 1);
}
I'm using ActivePerl 5.8.7. No error messages were encountered.
Thanks in advance. | [reply] [d/l] |
|
Perl not able to Recognise the Border Function
So please use Win32::OLE::Const 'Microsoft Excel';
It helps to resolve the issue
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on May 15, 2007 at 15:37 UTC
|
Thanks for this tutorial, I found how to finding the last Column and Row.
But I don't know how to delete one row, one column, or how to sort a sheet ? | [reply] |
|
Hi!The best advice I've receive was to record a macro in excel, then translate the VBA into your Perl.
Hope this will help you...
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 11, 2008 at 23:43 UTC
|
Thank you, this tutorial was very helpful. | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by ArmandoG (Sexton) on Jan 25, 2008 at 00:56 UTC
|
this tutorial has being my foundation on the project at work thank you, since that said, I am a newbie in Win32::OLE I got this problem I need to copy a cell and cant do it this is the code I am using:
$Sheet->Range('A17')->Select();
$Sheet->Selection->Copy('A17');
$Sheet->Range('B15')->Select();
$Sheet->ActiveSheet->Paste();
but that did not work what is wrong? I take this from the VBA in the macro.
Oh. yes I am using only WIN32::OLE
| [reply] [d/l] |
|
| [reply] [d/l] [select] |
|
This piece of code doesn't work because "Selection" is a method of the parent Excel application, not the sheet.
So if you had, in the beginning of your code:
my $XcelApp = Win32::OLE->new( 'Excel.Application' );
Then the second line in your post should go: $XcelApp->Selection->Copy('A17');Helen | [reply] [d/l] [select] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Feb 07, 2003 at 14:24 UTC
|
Well done. It helped me lot, especially at the begining.
There is one think I am missing here:
How to control the perl script from excel? In other words how to send commnads to perl script and change the sript run.
Something like "DDEpoke channel,..." in old DDE.
The OLE event is supposed to do it, but I was not able to find any code example how to use it. Do you have any or do you know where to find it?
Thanks
Tomas | [reply] |
|
is it possible to work with 2 workbooks at a time using perl?
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by sandycat05 (Initiate) on Nov 18, 2005 at 00:22 UTC
|
Hello, Your tutorial was wonderful! I found a lot of great info. I'm a new Perl programmer, and have been playing around with your code to try to fit my needs. Could you give me any advice on how to proceed with the following task? Instead of either opening a file or creating a new one, I'd like to do BOTH. I was thinking of creating a loop where I could basically say something like:
if $excelfile exists, then open $excelfile, otherwise, create a new workbook named $excelfile
However, being new to Perl, I haven't quite been able to find the correct syntax. Can you provide any clues as to how to do this, or is it even possible?
Thanks, and great work! | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jan 05, 2007 at 14:53 UTC
|
Hi,
Great tutorial! I'm looking to use text wrap for a column. Is there any chance someone could provide an example of how to do this with Win32::OLE and Excel?
Thanks!
NoviceMonk. | [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by Anonymous Monk on Jul 16, 2003 at 04:25 UTC
|
Is there any tutorial here or somewhere to create pie chart or any other chart using win32::OLE and excel?
thanks | [reply] |
|
Here's a little example that may help other novice monks:
my $Chart;
unless ($Book->Charts->Count) {
$Chart = $Book->Charts->Add({After => $Sheet});
# $Sheet is my data sheet
$Chart->{Name} = "Graphics";
$Book->Save();
} else {
$Chart = $Book->Charts("Graphics");
}
$Chart->SetSourceData($Sheet->Range('A1','C124'),xlColumns); # Range,
+xlRowCol
$Chart->{ChartType} = xlXYScatterLines; # xlChartType;
$Chart->Legend->{Position} = xlLegendPositionBottom; # xlLegendPositio
+n
$Chart->Activate();
# Axes
my $Xaxes = $Chart->Axes(xlCategory, xlPrimary);
$Xaxes->{HasTitle} = 1;
$Xaxes->{AxisTitle}->{Characters}->{Text} = "seconds";
my $Yaxes = $Chart->Axes(xlValue, xlPrimary);
$Yaxes->{HasTitle} = 1;
$Yaxes->{AxisTitle}->{Characters}->{Text} = "number";
| [reply] [d/l] |
|
What have you tried? Did it work? Did you take my advice up top and research the object model? Based on the Chart / Graph info I provided and a little reading, you should be able to figure this out. Even Google can be your friend. I read the second link (which gave me some ideas about ADSI and perl, btw) and found it interesting and helpful and David Wagner bubbled some of Jan DuBois' code up to top a while back here. Give these a read and give it a try.
Let us know how it goes.
C-.
---
Flex the Geek
| [reply] |
|
Hello Monk,
Just read your documentation on Excel, and found it extremely useful.
I have a problem and looking for some help regarding this.
This is my code below
#!/usr/bin/perl
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$MacroName = "LargeFileImport";
#Running a macro
$excelfile = "c:\\SndExMail\\test.xls";
my $Book = $Excel->Workbooks->Open($excelfile);
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();
$Excel->Run($MacroName);
I am running a macro, which will generate another excel file with certain values spanning across multiple sheets. Now after i execute the above code, a pop is displayed asking me to save sheet1 (this is generated after the macro is run). I can set displayalerts to 0 but At the same time i want to save the excel file generated by the macro with some name so that i ccan open it for manipulations later. Please let me know how to go about doing this. | [reply] [d/l] |
|
|
Thanks :) nice articles and it's very helpful.
| [reply] |
Re: Using Win32::OLE and Excel - Tips and Tricks
by jagdish.eashwar (Novice) on Dec 07, 2008 at 06:07 UTC
|
Is it possible to read an excel file without opening it? I have written a small script for reading several excel files in turn. Each of these excel files have a workbook_open macro which puts out a message. Because of this, running the script is very tedious. I have to keep pressing the enter key or click the ok button to get the msgbox out of the way whenever an excel file is opened by the script. | [reply] |
|
| [reply] |
|
It would completely depend on the format of the file, but in terms of OLE, no. This methodolgy uses the Excel application as the vehicle to read the files, and it requires the doc to be opened in the engine.
| [reply] |
Check whether the file is in use by an other user
by fish (Novice) on Jul 30, 2009 at 07:35 UTC
|
Hi,
at first I just want to thank you for the great tutorial, but I still have a problem.
I am working with a file which is accessible by many users via network. When I open the excel-file I want to check, whether the file is opened by an other user or not. I just want to work with it if it is not opened.
A simple die() if it is in use would be enough for me.
So, is it possible to check whether an other user has opened the file?
Great Regards,
- fish | [reply] |
|
IIRC Windows locks the file when opened. You may be able to check the file lock state, maybe something like this (I have no windows box to test):
open my $fh, "/some/file" or die "can't open!";
# try/catch file locking
eval { flock $fh, 'LOCK_EX' or die "can't lock!" };
if ( $@) {
warn "open file!" && exit;
}
| [reply] [d/l] |
|
Thanks for your response.
I tried your code and it's not bad but not exactly what I expected. Instead of showing an error-message if the file is opened the program waits until the file is getting closed. That is not bad, but kind of useless for my problem.
I am sure we are pretty near to a solution, so can somebody tell me how it works - please ;)
Best regards,
- fish
| [reply] |
|
|
Re: Using Win32::OLE and Excel - Tips and Tricks
by pank$ (Initiate) on Apr 16, 2013 at 13:17 UTC
|
how to use Win32:OLE on UNIX server..... i want to modify excel which contains macros ...that are deleted when i use ParseExcel::SaveParser
| [reply] |
|
| [reply] |
|
| [reply] |
|
|
|