INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Contact US
Thanks. We have received your request and will respond promptly.
Log In
Come Join Us!
Are you a Computer / IT professional? Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
Win32::OLE Excel Cheat Sheet
Win32::OLE Excel Cheat Sheet by WinblowsME
Posted: 17 Aug 07 (Edited 17 Aug 07)
|
I figured this might be helpful to those who use Win32::OLE for Excel.
CODEuse OLE; use Win32::OLE::Const "Microsoft Excel";
###################################################################################################################################
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
#___ ADD NEW WORKBOOK
$workbook = $excel -> Workbooks -> Add; $sheet = $workbook -> Worksheets("Sheet1"); $sheet -> Activate;
#___ OPEN EXISTING WORKBOOK
$workbook = $excel -> Workbooks -> Open("$file_name"); $sheet = $workbook -> Worksheets(1) -> {Name}; $sheet = $workbook -> Worksheets($sheet); $sheet -> Activate;
#___ ACTIVATE EXISTING WORKBOOK
$excel -> Windows("Book1") -> Activate; $workbook = $excel -> Activewindow; $sheet = $workbook -> Activesheet;
#___ CLOSE WORKBOOK
$workbook -> Close;
#___ ADD NEW WORKSHEET
$workbook -> Worksheets -> Add({After => $workbook -> Worksheets($workbook -> Worksheets -> {Count})});
#___ CHANGE WORKSHEET NAME
$sheet -> {Name} = "Name of Worksheet";
#___ PRINT VALUE TO CELL
$sheet -> Range("A1") -> {Value} = 1234;
#___ SUM FORMULAS
$sheet -> Range("A3") -> {FormulaR1C1} = "=SUM(R[-2]C:R[-1]C)"; # Sum rows $sheet -> Range("C1") -> {FormulaR1C1} = "=SUM(RC[-2]:RC[-1])"; # Sum columns
#___ RETRIEVE VALUE FROM CELL
$data = $sheet -> Range("G7") -> {Value};
#___ FORMAT TEXT
$sheet -> Range("G7:H7") -> Font -> {Bold} = "True"; $sheet -> Range("G7:H7") -> Font -> {Italic} = "True"; $sheet -> Range("G7:H7") -> Font -> {Underline} = xlUnderlineStyleSingle; $sheet -> Range("G7:H7") -> Font -> {Size} = 8; $sheet -> Range("G7:H7") -> Font -> {Name} = "Arial"; $sheet -> Range("G7:H7") -> Font -> {ColorIndex} = 4;
$sheet -> Range("G7:H7") -> {NumberFormat} = "\@"; # Text $sheet -> Range("A1:H7") -> {NumberFormat} = "\$#,##0.00"; # Currency $sheet -> Range("G7:H7") -> {NumberFormat} = "\$#,##0.00_);[Red](\$#,##0.00)"; # Currency - red negatives $sheet -> Range("G7:H7") -> {NumberFormat} = "0.00_);[Red](0.00)"; # Numbers with decimals $sheet -> Range("G7:H7") -> {NumberFormat} = "#,##0"; # Numbers with commas $sheet -> Range("G7:H7") -> {NumberFormat} = "#,##0_);[Red](#,##0)"; # Numbers with commas - red negatives $sheet -> Range("G7:H7") -> {NumberFormat} = "0.00%"; # Percents $sheet -> Range("G7:H7") -> {NumberFormat} = "m/d/yyyy" # Dates
#___ ALIGN TEXT
$sheet -> Range("G7:H7") -> {HorizontalAlignment} = xlHAlignCenter; # Center text; $sheet -> Range("A1:A2") -> {Orientation} = 90; # Rotate text
#___ SET COLUMN WIDTH/ROW HEIGHT
$sheet -> Range('A:A') -> {ColumnWidth} = 9.14; $sheet -> Range("8:8") -> {RowHeight} = 30; $sheet -> Range("G:H") -> {Columns} -> Autofit;
#___ FIND LAST ROW/COLUMN WITH DATA
$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row}; $last_col = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByColumns}) -> {Column};
#___ ADD BORDERS
$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {LineStyle} = xlDouble; $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {Weight} = xlThick; $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {ColorIndex} = 1; $sheet -> Range("A3:I3") -> Borders(xlEdgeLeft) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlEdgeLeft) -> {Weight} = xlThin; $sheet -> Range("A3:I3") -> Borders(xlEdgeTop) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlEdgeTop) -> {Weight} = xlThin; $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlEdgeBottom) -> {Weight} = xlThin; $sheet -> Range("A3:I3") -> Borders(xlEdgeRight) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlEdgeRight) -> {Weight} = xlThin; $sheet -> Range("A3:I3") -> Borders(xlInsideVertical) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlInsideVertical) -> {Weight} = xlThin; $sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {LineStyle} = xlContinuous; $sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {Weight} = xlThin;
#___ PRINT SETUP
$sheet -> PageSetup -> {Orientation} = xlLandscape; $sheet -> PageSetup -> {Order} = xlOverThenDown; $sheet -> PageSetup -> {LeftMargin} = .25; $sheet -> PageSetup -> {RightMargin} = .25; $sheet -> PageSetup -> {BottomMargin} = .5; $sheet -> PageSetup -> {CenterFooter} = "Page &P of &N"; $sheet -> PageSetup -> {RightFooter} = "Page &P of &N"; $sheet -> PageSetup -> {LeftFooter} = "Left\nFooter"; $sheet -> PageSetup -> {Zoom} = 75; $sheet -> PageSetup -> FitToPagesWide = 1; $sheet -> PageSetup -> FitToPagesTall = 1;
#___ ADD PAGE BREAK
$excel -> ActiveWindow -> SelectedSheets -> HPageBreaks -> Add({Before => $sheet -> Range("3:3")});
#___ HIDE COLUMNS
$sheet -> Range("G:H") -> EntireColumn -> {Hidden} = "True";
#___ MERGE CELLS
$sheet -> Range("H10:J10") -> Merge;
#___ INSERT PICTURE
$sheet -> Pictures -> Insert("picture_name"); # Insert in upper-left corner $excel -> ActiveSheet -> Pictures -> Insert("picture_name"); # Insert in active cell
#___ GROUP ROWS
$sheet -> Range("7:8") -> Group;
#___ ACTIVATE CELL
$sheet -> Range("A2") -> Activate;
#___ FREEZE PANES
$excel -> ActiveWindow -> {FreezePanes} = "True";
#___ DELETE SHEET
$sheet -> Delete;
#___ SAVE AND QUIT
$excel -> {DisplayAlerts} = 0; # This turns off the "This file already exists" message. $workbook -> SaveAs ("C:\\file_name.xls"); $excel -> Quit; |
Back to Perl FAQ Index
Back to Perl Forum |
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close