Example of using "use File::Remote " to copy files from one Windows machine to another
Hi folks, I need to copy files from a remote Windows machine to the Windows machine were the Perl script is executing. I've installed and tried running a script that uses "File::Remote", but I'm getting errors. Here is the file so far.. *** use File::Remote qw(:replace); # PsExec_Usage_Line.txt # IP address of me8280a # 172.24.177.43 # read the file "PsExec_Usage_Line.txt on the D drive of me8280a ( ipAddress 172.24.177.43 ) open (REMOTE, "172.24.177.43:/d$/PsExec_Usage_Line.txt") or die $!; The error I'm getting is: D:\BusinessAnalyticsFiles\PerlScripts>perl File_Remote.pl The system cannot find the path specified. No such file or directory at File_Remote.pl line 25. The Remote file, PsExec_Usage_Line.txt, is on the base D drive of the remote machine. Thanks so much for any help. Glen Plantz Mitchell International ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Copying an Excel chart to PowerPoint
Hi Folks, I need to copy an Excel Chart to PowerPoint. I've found VBA code to do this, but I'm having trouble converting it to Perl. Could some kind soul please lend me a hand? I would be eternally grateful. Thanks in advance for any help. Glen Plantz Mitchell International Here is the VBA Code. Sub ChartToPresentation() ' Uses Early Binding to the PowerPoint Object Model ' Set a VBE reference to Microsoft PowerPoint Object Library Dim PPApp As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide ' Make sure a chart is selected If ActiveChart Is Nothing Then MsgBox "Please select a chart and try again.", vbExclamation, _ "No Chart Selected" Else ' Reference existing instance of PowerPoint Set PPApp = GetObject(, "Powerpoint.Application") ' Reference active presentation Set PPPres = PPApp.ActivePresentation PPApp.ActiveWindow.ViewType = ppViewSlide ' Reference active slide Set PPSlide = PPPres.Slides _ (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex) ' Copy chart as a picture ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _ Format:=xlPicture ' Paste chart PPSlide.Shapes.Paste.Select ' Align pasted chart PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True ' Clean up Set PPSlide = Nothing Set PPPres = Nothing Set PPApp = Nothing End If End Sub ---- -- Glen Plantz Software Engineer IV Business Analytics "The future belongs to data" ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Problem with error "Can't call method "Select" on an undefined value" in Win32::OLE Excel script
Hi Folks,
I'm having trouble with code that I've done hundreds of times before,
but which in this case is causing the error "Can't call method "Select"
on an undefined value".
Here is a snippet of code:
my $excel_app = Win32::OLE->new('Excel.Application')
or die "could not create excel app\n";
$excel_app->{'Visible'} = 1;
# " $Excel_CSAA_Test_Retrieve " is pointing to a file in
the current directory...
my $excel_Retrieve_Workbook =
$excel_app->Workbooks->Add( { Template => $Excel_CSAA_Test_Retrieve } )
or die "could not open $Excel_CSAA_Test_Retrieve\n";
my $Sheet1 = $excel_Retrieve_Workbook->Worksheets(1);
$Sheet1->Activate();
$excel_Retrieve_Workbook->ActiveSheet->Range("A4")->Select();
print "\nExiting for test\n";
exit;
# ***
I get the error " "Can't call method "Select" on an undefined value" on
the line:
$excel_Retrieve_Workbook->ActiveSheet->Range("A4")->Select();
# ****
I don't understand what I'm doing wrong. I've done things similar to
this many times.
Can anyone see any problems with the above code???
Thanks in advance for any help.
Glen Plantz
Mitchell International
San Diego, California
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
error when I try to execute the "SaveAs" function in Win32::OLE" when I try to save an Excel worksheet to a new format.
Title: error when I try to execute the "SaveAs" function in Win32::OLE" when I try to save an Excel worksheet to a new format.
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 );
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
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Returning to a Worksheet where a hyperlink was used - in Excel with Win32::OLE
Title: Returning to a Worksheet where a hyperlink was used - in Excel with Win32::OLE Hi Folks, We have a set of Excel Workbooks that are constructed by Perl Win32::OLE scripts. The Workbooks consist of reports in the form of Worksheets. Each Worksheet has a column of Hyperlink Objects that direct the user to one Sheet of a set of "definition pages" (a set of Worksheets at the end of each Workbook ) that gives details on how the values in that row are calculated. My issue is how to allow the user to "go back" to the page from where they came when they are on a definition page, by either clicking on a hyperlink on the definition page they are currently on, or with a simple Control-Sequence. I know that the user can get back to a Sheet from where they originally clicked on the hyperlink ( that brought them to the current definition page ) by executing: Control-G ( Control-G here brings up the "Go To" dialog box, listing a series of "References", one of which is highlighted, which is the reference for the sheet from where they came. Hitting the ENTER key will bring them back to the sheet from where they came). I can't construct a Hyperlink Object on these "definition" sheets, because a user could go to one specific definition sheet from any number of Worksheets. Does anyone know of a solution to allow a user to get back to a Sheet from where they clicked on a hyperlink, other than using the "Control-G " sequence ??? Thanks as always for any help. Glen Plantz ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Modifying the Address property of an Excel Hyperlink object with Win32::OLE
Title: Modifying the Address property of an Excel Hyperlink object with Win32::OLE Hi folks, I need to edit the Address property of Hyperlink objects in Excel worksheets. I did a google and found several examples of ADDING hyperlinks, but could someone please send me an example of editing an EXISTING hyperlink 'Address' property? Thanks in advance for any help. Glen Plantz ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
using unlink on Win32 results in "permission denied" error
Title: Message Hi Folks, I need to delete several files from a directory that were associated with an Excel process. I did an $excel_app->quit which got rid of that process, but when I then tried to do an "unlink" to get rid of the Excel files, I received the error "can't unlink. permission denied" error. Dave Roth posted an article on this problem in the "WindowsIT Pro" magazine, but a subscription is required to see his solution, that uses the Win32-AdminMisc module. Here is the snippet that the WindowsIT Pro magazine post This task isn't easy by any stretch of the imagination, but oddly enough it's an all too common one. A perfect example is when you want to rename or delete a file but you can't because a process has opened it. This problem is broken into two components: files that local processes have open and files that remote processes h . . . Could some kind soul please tell me how to deal with this problem? I'm sure this must be an FAQ, but I can't see a solution using Google. Thanks in advance for any help. Glen Plantz ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
creating ZIP file that is password protected
Title: Message Hi Folks, Do any of you know of a way or know of a module that can password-protect a ZIP file? I've just started using Archive::Zip, and it works well, but I don't see any way to password-protect the ZIP file that is created from Archive::Zip. I've looked thru the Archive::Zip documents, but I don't see any method to programmatically add a password to the ZIP file that is created. Thanks in advance for any help. Glen Plantz ___ Perl-Win32-Users mailing list Perl-Win32-Users@listserv.ActiveState.com To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Copy vs Add when adding sheets to a workbook past 255
Title: Message
Hi
Folks,
Last Thursday I posted a query about an error I was getting
when trying to create sheets in an Excel workbook past a sheet count of
255.
Justin Allegakoen was kind enough to
send me a script that works to add sheets past 255, using the "Add"
function.
Here is a snippet of the code that Justin sent
me;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 1;
$Excel->{SheetsInNewWorkbook} = 255;
my $Book = $Excel->Workbooks->Add();
foreach my $i(1 .. 255) {
print "Renaming Sheet$i\n";
my $Sheet = $Book->Worksheets($i);
$Sheet->{Name} = "Glen
$i";
}
$Book->SaveAs($File);
$Book->Close;
for(1 .. 10)
{
$SavedBook->Worksheets->Add();
}
This code works great, but why does the "Add " function
work here past a sheetcount of 255, when the "Copy" function that I mentioned in
my original post, throws an error when when the sheetcount > 255
???
I should have mentioned in my original post that I have
an existing Excel sheet that I need to open and use as a "Template" for all the
other sheets in this workbook that I'm creating.
Thanks in advance for any help here I'm
stumped.
Glen Plantz
Here is my original post...
#
*
I need some help
with getting around the 255 sheet limit for NEW workbooks in Excel. Here is a
snippet from the book "Excel: The Missing Manual"
Note: Although you're limited to 255
sheets in a new workbook, Excel doesn't limit how many
worksheets you can add after
you've created a workbook. The only factor that ultimately limits the number
of worksheets your workbook can hold is your computer's memory. However, most
modern day PCs can easily handle even the largest of
workbooks.
I've tried closing and and immediately reopening a Workbook, to no avail.
My system has 1 Gig of memory. No other apps are
running.
I keep a running count of the number worksheet's I've created in my Perl
script, and no matter whether I've closed and reopened, or what, as soon as
the count gets to 256 worksheets, the following error
occurs:
D:\Perl>perl CsaaRetrieveToTemplate.pl >
outputA3_for_lv.txtOLE exception from "Microsoft
Excel":
Unable to get the Copy property of the Worksheet
class
Win32::OLE(0.1701) error 0x800a03ec in
METHOD/PROPERTYGET "Copy" at CsaaRetrieveToTemplate.plline
976.
Here is a
snippet of the code ( the last line is throwing the above error
)
#
**
my
$templateCopy = "";
$templateSheet =
$Template_Workbook->Worksheets("$templateCopy");
$templateSheet->Activate();
my $shCount =
$Template_Workbook->Worksheets->{Count};
print "shCount = $shCount\n";
$templateSheet->Copy({After=>$Template_Workbook->Worksheets($shCount)})
or die Win32::OLE->LastError();
#
*******
I hope
someone has a solution... this is a "show stopper" for the work I'm
doing.
Thanks in
advance for any help...
Glen
Plantz
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
getting around the 255 sheet limit for NEW workbooks in Excel
Title: Message
Hi
Folks,
I need some help
with getting around the 255 sheet limit for NEW workbooks in Excel. Here is a
snippet from the book "Excel: The Missing Manual"
Note:
Although you're limited to 255 sheets in
a new workbook, Excel doesn't limit how many worksheets you
can add after you've created a
workbook. The only factor that ultimately limits the number of worksheets your
workbook can hold is your computer's memory. However, most modern day PCs can
easily handle even the largest of workbooks.
I've tried closing and and immediately reopening a Workbook, to no avail.
My system has 1 Gig of memory. No other apps are
running.
I keep a running count of the number worksheet's I've created in my Perl
script, and no matter whether I've closed and reopened, or what, as soon as
the count gets to 256 worksheets, the following error
occurs:
D:\Perl>perl CsaaRetrieveToTemplate.pl >
outputA3_for_lv.txtOLE exception from "Microsoft
Excel":
Unable to get the Copy property of the Worksheet
class
Win32::OLE(0.1701) error 0x800a03ec in
METHOD/PROPERTYGET "Copy" at CsaaRetrieveToTemplate.plline
976.
Here is a
snippet of the code ( the last line is throwing the above error
)
#
**
my
$templateCopy = "";
$templateSheet =
$Template_Workbook->Worksheets("$templateCopy");
$templateSheet->Activate();
my $shCount =
$Template_Workbook->Worksheets->{Count};
print "shCount = $shCount\n";
$templateSheet->Copy({After=>$Template_Workbook->Worksheets($shCount)})
or die Win32::OLE->LastError();
#
***
I hope
someone has a solution... this is a "show stopper" for the work I'm
doing.
Thanks in
advance for any help...
Glen
Plantz
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
converting from Row, Column to Cell Name, and back, in Excel
Title: Message Hi Folks, Could someone tell me how to convert from the "Row, Column" ( 1,1) reference to a Cell to using the "Cell Name" ( A1) ? I know this is an FAQ, but I can't seem to find an answer. Thanks for the help. Glen Plantz ___ 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
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
using Win32::OLE to copy or copy a range from one sheet to another
Title: Message
Hi
Folks,
I've been using Perl
for a number of years, and I'm now learning Win32::OLE for manipulating Excel
files. I'm try to copy or cut a range of cells from one Worksheet to
another. The "Select" portion works fine, but the "Paste" is not
working..
Thanks in advance
for any help.
Glen
Plantz
Mitchell
International
[EMAIL PROTECTED]
Here is my
code:
my $excel_app =
Win32::OLE->new('Excel.Application')or die "could not create excel
app\n";
$excel_app->{'Visible'} = 1;
$excel_app->{
'SheetsInNewWorkbook' } = 1;
my $excel_Workbook
= $excel_app->Workbooks->Open($Excel_CSAA_Test_Retrieve )or die
"could not open $Excel_CurrentMonth_Retrieve\n";
# this section
executes VBA functions in the Workbook opened above to retrieve data from an
Hyperion Essbase database... this works fine
$excel_app->RegisterXLL($Essbase_Addin_loc);
$excel_app->{ 'DisplayAlerts' } = 0;
my $excel_Workbook =
$excel_app->Workbooks->Open($Excel_CSAA_Test_Retrieve
) or die "could not open
$Excel_CurrentMonth_Retrieve\n"; my $m_sVBAFunctionName
= "'" . $Excel_CSAA_Test_Retrieve . "'!Connect";
my $retVal = $excel_app->Run($m_sVBAFunctionName, $Essbase_Server,
$Essbase_UserID, $Essbase_Password, $Essbase_Application,
$Essbase_Database); print "retVal =
$retVal\n"; $m_sVBAFunctionName = "'" .
$Excel_CSAA_Test_Retrieve . "'!Retrieve";
$retVal = $excel_app->Run($m_sVBAFunctionName);
# END - Data Retrieve
print
"retVal = $retVal\n";
my $Sheet1 =
$excel_Workbook->Worksheets(1);
$Sheet1->Range("A29:P39")->Select()$Sheet1->Range("A29:P39")->Copy();
my $Sheet =
$excel_Workbook->Worksheets->Add({After=>$excel_Workbook->Worksheets($excel_Workbook->Worksheets->{Count})})
or die Win32::OLE->LastError();
$Sheet->{Name} = "Glens"
$ works fine up to year,... the "Sheet1"
excel sheet above shows that the range "A29:P39" has been selected and
copied
#
>>>>>>>>>>>>>>>> Here is
where I'm running into trouble this Paste is not
working...
$Sheet->Range("A29")->Paste();
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
