Re: How to run an Excel Macro in Perl?

Replies are listed 'Best First'.
Re^2: How to run an Excel Macro in Perl? by padawan_linuxero (Scribe) on Jan 14, 2008 at 17:32 UTC
Hi Woody Tis is the macro that I am trying to work Sub Macro2() ' ' Macro2 Macro ' ' Rows("1:13").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbov +e Columns("C:D").Select Selection.Cut Destination:=Columns("P:Q") Columns("N:O").Select Range("O1").Activate Selection.Delete Shift:=xlToLeft Columns("K:K").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:D").Select Range("D1").Activate Selection.Delete Shift:=xlToLeft Range("A13").Select ActiveCell.FormulaR1C1 = "Patentes" Range("A13").Select ActiveCell.FormulaR1C1 = "Patente" Range("B13").Select ActiveCell.FormulaR1C1 = "Pedimentos" Range("C13").Select ActiveCell.FormulaR1C1 = "Clave Docum" Range("D13").Select ActiveCell.FormulaR1C1 = "Fecha Entrada" Range("E13").Select ActiveCell.FormulaR1C1 = "Fecha Pago" Range("F13").Select ActiveCell.FormulaR1C1 = "RFC Imp. Exp." Range("G13").Select ActiveCell.FormulaR1C1 = "CURP" Range("H13").Select ActiveCell.FormulaR1C1 = "Peso Brutio" Range("H13").Select Selection.ClearContents ActiveCell.FormulaR1C1 = "Peso Bruto" Range("H14:H683").Select ActiveWindow.SmallScroll Down:=-228 ActiveWindow.ScrollRow = 428 ActiveWindow.ScrollRow = 426 ActiveWindow.ScrollRow = 423 ActiveWindow.ScrollRow = 414 ActiveWindow.ScrollRow = 389 ActiveWindow.ScrollRow = 353 ActiveWindow.ScrollRow = 297 ActiveWindow.ScrollRow = 256 ActiveWindow.ScrollRow = 215 ActiveWindow.ScrollRow = 166 ActiveWindow.ScrollRow = 130 ActiveWindow.ScrollRow = 93 ActiveWindow.ScrollRow = 74 ActiveWindow.ScrollRow = 52 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 16 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 1 Columns("H:H").Select Selection.NumberFormat = "0.000" Columns("I:I").Select Selection.NumberFormat = "$#,##0.00;[Red]$#,##0.00" Range("I13").Select ActiveCell.FormulaR1C1 = "Contribuciones" Range("J13").Select ActiveCell.FormulaR1C1 = "Banco" Range("K13").Select ActiveCell.FormulaR1C1 = "Ped Orig" Range("L13").Select ActiveCell.FormulaR1C1 = "Ped Rectif" Range("F2").Select Cells.Replace What:="\N", Replacement:=" ", LookAt:=xlPart, Search +Order _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceForm +at:=False Range("E7").Select ActiveCell.FormulaR1C1 = _ "Relacion de Operaciones Correspondientes a la semana No." Range("E8").Select ActiveCell.FormulaR1C1 = "Comprendida del " Range("C10").Select ActiveCell.FormulaR1C1 = "Patente: " Range("C10").Select Selection.ClearContents Range("B10").Select ActiveCell.FormulaR1C1 = "Patente : " Range("C10").Select ActiveCell.FormulaR1C1 = "=R[4]C[-2]" Columns("A:L").Select Columns("A:L").EntireColumn.AutoFit ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 Range("E16").Select Columns("E:E").ColumnWidth = 13.71 Columns("E:E").ColumnWidth = 11.43 Range("B13:L1292").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Range("A13:A1292").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Range("B10:C10").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Range("H10").Select End Sub [download] How can I convert the macro to perl?	[reply] [d/l]
Re^3: How to run an Excel Macro in Perl? by whakka (Hermit) on Jan 14, 2008 at 19:30 UTC
I'm going to hazard a guess that WoodyWeaver won't be answering that request anytime soon (see here). Some resources I have found useful for working in Excel with Win32 are: excelPerl V0.12 released tek-tips Using Win32::OLE and Excel - Tips and Tricks Active State faq Good luck.	[reply]
Re^3: How to run an Excel Macro in Perl? by WoodyWeaver (Monk) on Jan 14, 2008 at 23:08 UTC
Its not that bad, whakka. I think he is trying, and I'll help him get started. The way to convert your macro into perl is to use the perl excel instance you've created, and convert the VBScript references to object oriented function calls. Staring at the object browser (I'm not on a box that has excel on it right now, but its something like Tools/Macro/VBEditor then View/ObjectBrowser) to understand what the calls are doing and what they mean is very helpful. Adding perl print statements mixed between the Excel statements is also helpful. You've set up `my $Sheet = $Book->Worksheets(1);` -- this is the first worksheet. You can now start manipulating it. For example, the first thing you do is to select a range of rows. If you look in the object browser, you will find that class "Worksheet" has property "Rows" as a range object. So, you can pick up that range as a perlish object as `my $range = $Sheet->Rows("1:13");` The macro then selects that range `$range->Select;` -- but that might not be what you want. If you look in the browser, class Range has a lot of properties you can invoke -- and since you are selecting only to insert, you can do that directly. The object browser describes `expression.Insert(Shift, CopyOrigin) expression Required. An expression that returns a Range object. Shift Optional Variant. Specifies which way to shift the cells. Can +be one of the following XlInsertShiftDirection constants: xlShiftToRi +ght or xlShiftDown. If this argument is omitted, Microsoft Excel dec +ides to do what it thinks you really want. CopyOrigin Optional Variant. The copy origin.` [download] Okay, not the greatest of doc, but given that xlDown is -4121 and that xlFormatFromLeftOrAbove is 0 (look them up in the object browser) your code becomes `$range->Insert(-4121,0);` or, since you didn't really need that range for anything else, `$Sheet->Rows("1:13")->Insert(-4121,0);` [download] And so on. Now you can put in a print statement that says you did that line successfully. Even better, you can introduce a `$Book->Save;` so that after the train jumps the tracks, you can at least see if the spreadsheet looked like you expected up to that point. By breaking out of the "load file, execute canned macro" you can gain a lot of leverage from perl. Sure, you can still have lines like $worksheet->Range("A3:N3")->{Value} = [ "Point of Contact", "Project", "PAL Code", "Goal", "Earned Value Towards Goal", "Overall Goal Progress", "Start Date", "Finish Date", "Days Active", "Last Completed Task", "Date Completed", "Dollars", "Staff Size", "Unplanned Tasks", ]; $worksheet->Range("A3:N3")->{WrapText} = 1; $worksheet->Rows(3)->{RowHeight} = 36; $worksheet->Range("A3:N3")->{Font}->{Bold} = 1; $xls->ActiveWindow->{SplitRow} = 3; $xls->ActiveWindow->{SplitColumn} = 3; $xls->ActiveWindow->{FreezePanes} = 1; [download] which is nice, because it saves some tedium, but you can also do things like `### build a whole bunch of data like below... my $defects = Dashboard::safeSQLfunction( "SELECT COUNT(*) FROM userProcessLists WHERE masterID = ? AND EVpo +ints > 0 AND ((start IS NULL) OR (finish IS NULL))", $ref->{'processID'}); # and store it $worksheet->Range("A$row:N$row")->{Value} = [ $poc, $project, $code, $process, $evp, $overallPercentage, $start, + $finish, $duration, $lastDone, $lastCompletionDate, $dollars, $staff, $defects, ]; } ## end while (my $ref = $sth->fetchrow_hashref)` [download] But ultimately, yeah, its hit the documentation to see how others have done it and what patterns to follow. --woody	[reply] [d/l] [select]