merrymonk has asked for the wisdom of the Perl Monks concerning the following question:
I am using Excel 2007 on a windows XP PC.
I want to do the following in Perl
1. Select a number of rows
2. Copy these rows
3. Use the 'Insert Copied Rows' to get a copy of the select rows in another position of the spreadsheet I have recorded an Excel VBA to do this and it is (I liked someone describing these as incantations.)
Can't call method "Insert" on an undefined value at insert_rows.pl line 16.
What change do I have to make to be able to copy the rows that I want?
2. Copy these rows
3. Use the 'Insert Copied Rows' to get a copy of the select rows in another position of the spreadsheet I have recorded an Excel VBA to do this and it is (I liked someone describing these as incantations.)
I do not think I need the ActiveWindow.SmallScroll Down:=30 because that is just a record of moving to the new insertion point.I have found the following about converting VBA to Perl but it does not seem to really cover this set of actions If you record a macro in Microsoft Office, this can often be translated directly into Perl. In Visual Basic for Applications (VBA) the syntax is like this: object.method(argument).property = value In Perl this becomes object->method(argument)->{property} = value; So for example this code from VBA: ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale becomes this in Perl: $Chart->Axes(xlCategory, xlPrimary)->{CategoryType} = xlCategoryScale; Below is the Perl code where I opened an existing spreadsheet and tried insert a number of copied rowsRows("1:9").Select Selection.Copy ActiveWindow.SmallScroll Down:=30 Range("A39").Select Selection.Insert Shift:=xlDown
The print for $selection gave OLE=HASH(0x183fab8) However, it gave a failure on the last line which saiduse strict "vars"; use OLE; use Win32::OLE::Const "Microsoft Excel"; use Win32::OLE::Variant; my($excel, $spsh_full, $workbook, $sheet, $selection); #___ DEFINE EXCEL $excel = CreateObject OLE "Excel.Application"; #___ MAKE EXCEL VISIBLE $excel -> {Visible} = 1; #___ OPEN EXISTING WORKBOOK $spsh_full = <full path to spreadsheet>; $workbook = $excel -> Workbooks -> Open($spsh_full); $sheet = $workbook -> Worksheets('Quotation'); $selection = $sheet->Rows("1:9"); print "$selection\n"; $sheet->Range("A39")->$selection->Insert;
Can't call method "Insert" on an undefined value at insert_rows.pl line 16.
What change do I have to make to be able to copy the rows that I want?
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Inserting copied rows at another position in Excel
by davies (Prior) on Mar 01, 2011 at 14:14 UTC | |
by merrymonk (Hermit) on Mar 01, 2011 at 16:06 UTC | |
by HelenCr (Monk) on Jan 12, 2013 at 04:24 UTC | |
by davies (Prior) on Jan 12, 2013 at 11:12 UTC | |
by HelenCr (Monk) on Jan 12, 2013 at 14:49 UTC | |
by davies (Prior) on Jan 12, 2013 at 16:23 UTC | |
| |
Re: Inserting copied rows at another position in Excel
by Anonymous Monk on Mar 01, 2011 at 11:20 UTC | |
by merrymonk (Hermit) on Mar 01, 2011 at 11:45 UTC |
Back to
Seekers of Perl Wisdom