Ruby and Windows Automation

References

• http://rubyonwindows.blogspot.com/
• http://rubyonwindows.blogspot.com/2007/03/automating-excel-with-ruby-workbook.html
• http://rubyonwindows.blogspot.com/2007/04/automating-excel-with-ruby-worksheet.html
• http://rubyonwindows.blogspot.com/2008/06/automating-excel-creating-charts.html
• http://rubyonwindows.blogspot.com/2008/06/automating-excel-chart-axis-and-legend.html
• http://rubyonwindows.blogspot.com/search/label/excel

Functions

Miscellaneous function in the API...

excel = WIN32OLE.new('Excel.Application')

excel.DisplayAlerts = false
excel.Visible       = true

book = excel.Workbooks.Add()

sheet = book.Worksheets(1)

range = sheet.Range('A1').Resize(data.size, data.first.size)
range.Value = data

sheet.Rows(1).AutoFilter()

book.Worksheets(3).Move book.Worksheets(1)

excel.activeworkbook.save

excel.sheets(idx).activate

no_sheets = excel.sheets.count

excel.workbooks.open("filename.xls")

sheet      = book.Worksheets(4)
sheet.Name = "XYZZY"

sheet.PrintOut

sheet.Delete

book.Worksheets(3).Copy(book.Worksheets(1))

sheet = book.ActiveSheet

iTunes

require 'win32ole'

app = WIN32OLE.new('iTunes.Application')

data = []

app.LibraryPlaylist.Tracks.each do |track|
  if not track.Podcast
    data << [track.Artist, track.Year, track.Album, track.Name]
  end
end

data.sort!

data.insert(0, ['ARTIST', 'YEAR', 'ALBUM', 'NAME'])

Add a Picture

require 'win32ole'

excel = WIN32OLE.connect('Excel.Application')
sheet = excel.ActiveSheet

range = sheet .Range('C3:F5')

pic = sheet.Shapes.AddPicture({ 
    'FileName' => 'C:\Temp\Image1.jpg', 
    'LinkToFile' => false, 
    'SaveWithDocument' => true, 
    'Left' => range.Left, 
    'Top' => range.Top, 
    'Width' => range.Width, 
    'Height' => range.Height 
})

MSDN Links

Constants

• Excel
• Word
• PowerPoint

Constants

From: http://ruby-doc.org/core/classes/WIN32OLE.html

module EXCEL_CONST
end
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, EXCEL_CONST)
puts EXCEL_CONST::XlTop # => -4160
puts EXCEL_CONST::CONSTANTS['_xlDialogChartSourceData'] # => 541

WIN32OLE.const_load(excel)
puts WIN32OLE::XlTop # => -4160

module MSO
end
WIN32OLE.const_load('Microsoft Office 9.0 Object Library', MSO)
puts MSO::MsoLineSingle # => 1

WIN32OLE.new('Excel.Application') # => Excel OLE Automation WIN32OLE object.
WIN32OLE.new('{00024500-0000-0000-C000-000000000046}') # => Excel OLE Automation WIN32OLE object.

Functional Methods

C:\QC_Reporting>irb --readline -r irb/completion
irb(main):002:0> require 'win32ole'
=> true
irb(main):003:0* excel = WIN32OLE.new('Excel.Application')
=> #<WIN32OLE:0x2b74da8>
irb(main):004:0> properties = excel.ole_func_methods
=> [QueryInterface, AddRef, Release, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames, Invoke, Calculate, DDEExecute, DDEInitiate, DDEPoke, DDERequest, DDETerminate, Evaluate, _Evaluate, ExecuteExcel4Macro, Intersect, Run, _Run2, SendKeys, Union, ActivateMicrosoftApp, AddChartAutoFormat, AddCustomList, CentimetersToPoints, CheckSpelling, ConvertFormula, Dummy1,Dummy2, Dummy3, Dummy4, Dummy5, Dummy6, Dummy7, Dummy8, Dummy9, Dummy10, Dummy11, DeleteChartAutoFormat, DeleteCustomList, DoubleClick, _FindFile, GetCustomListContents, GetCustomListNum, GetOpenFilename, GetSaveAsFilename, Goto, Help, InchesToPoints, InputBox, MacroOptions, MailLogoff, MailLogon, NextLetter, OnKey, OnRepeat, OnTime, OnUndo, Quit, RecordMacro, RegisterXLL, Repeat, ResetTipWizard, Save, SaveWorkspace, SetDefaultChart, Undo, Volatile, _Wait, _WSFunction, Wait,GetPhonetic, Dummy12, CalculateFull, FindFile, Dummy13, Dummy14, CalculateFullRebuild, CheckAbort, DisplayXMLSourcePane,
 Support, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames, Invoke]
irb(main):005:0>

Get Methods

irb(main):006:0> properties = excel.ole_get_methods
=> [Application, Creator, Parent, ActiveCell, ActiveChart, ActiveDialog, ActiveMenuBar, ActivePrinter, ActiveSheet, ActiveWindow, ActiveWorkbook, AddIns, Assistant, Cells, Charts, Columns, CommandBars, DDEAppReturnCode, DialogSheets, MenuBars, Modules, Names, Range, Rows, Selection, Sheets, ShortcutMenus, ThisWorkbook, Toolbars, Windows, Workbooks, WorksheetFunction, Worksheets, Excel4IntlMacroSheets, Excel4MacroSheets, AlertBeforeOverwriting, AltStartupPath, AskToUpdateLinks, EnableAnimations, AutoCorrect, Build, CalculateBeforeSave, Calculation, Caller, CanPlaySounds, CanRecordSounds, Caption, CellDragAndDrop, ClipboardFormats, DisplayClipboardWindow, ColorButtons, CommandUnderlines, ConstrainNumeric, CopyObjectsWithCells, Cursor, CustomListCount, CutCopyMode, DataEntryMode, _Default, DefaultFilePath, Dialogs, DisplayAlerts, DisplayFormulaBar, DisplayFullScreen, DisplayNoteIndicator, DisplayCommentIndicator, DisplayExcel4Menus, DisplayRecentFil
es, DisplayScrollBars, DisplayStatusBar, EditDirectlyInCell, EnableAutoComplete, EnableCancelKey, EnableSound, EnableTipWizard, FileConverters, FileSearch, FileFind, FixedDecimal, FixedDecimalPlaces, Height, IgnoreRemoteRequests, Interactive, International, Iteration, LargeButtons, Left, LibraryPath, MailSession, MailSystem, MathCoprocessorAvailable, MaxChange, MaxIterations, MemoryFree, MemoryTotal, MemoryUsed, MouseAvailable, MoveAfterReturn, MoveAfterReturnDirection, RecentFiles, Name, NetworkTemplatesPath, ODBCErrors, ODBCTimeout, OnCalculate, OnData, OnDoubleClick, OnEntry, OnSheetActivate, OnSheetDeactivate, OnWindow, OperatingSystem, OrganizationName, Path, PathSeparator, PreviousSelections, PivotTableSe
lection, PromptForSummaryInfo, RecordRelative, ReferenceStyle, RegisteredFunctions, RollZoom, ScreenUpdating, SheetsInNewWorkbook, ShowChartTipNames, ShowChartTipValues, StandardFont, StandardFontSize, StartupPath, StatusBar, TemplatesPath, ShowToolTips, Top, DefaultSaveFormat, TransitionMenuKey, TransitionMenuKeyAction, TransitionNavigKeys, UsableHeight, UsableWidth, UserControl, UserName, Value, VBE, Version, Visible, Width, WindowsForPens, WindowState, UILanguage, DefaultSheetDirection, CursorMovement, ControlCharacters, EnableEvents, DisplayInfoWindow, ExtendList, OLEDBErrors, COMAddIns, DefaultWebOptions, ProductCode, UserLibraryPath, AutoPercentEntry, LanguageSettings, Dummy101, AnswerWizard, CalculationVersion, ShowWindowsInTaskbar, FeatureInstall, Ready, FindFormat, ReplaceFormat, UsedObjects, CalculationState, CalculationInterruptKey, Watches, DisplayFunctionToolTips, AutomationSecurity, FileDialog, DisplayPasteOptions, DisplayInsertOptions, GenerateGetPivotData, AutoRecover, Hwnd, Hinstance, ErrorCheckingOptions, AutoFormatAsYouTypeReplaceHyperlinks, SmartTagRecognizers, NewWorkbook, SpellingOptions, Speech, MapPaperSize, ShowStartupDialog, DecimalSeparator, ThousandsSeparator, seSystemSeparators, ThisCell, RTD, DisplayDocumentActionTaskPane, ArbitraryXMLSupportAvailable]

Put Methods

irb(main):007:0> properties = excel.ole_put_methods
=> [ActivePrinter, AlertBeforeOverwriting, AltStartupPath, AskToUpdateLinks, EnableAnimations, CalculateBeforeSave, Calculation, Caption, CellDragAndDrop, DisplayClipboardWindow, ColorButtons, CommandUnderlines, ConstrainNumeric, CopyObjectsWithCells, Cursor, CutCopyMode, DataEntryMode, DefaultFilePath, DisplayAlerts, DisplayFormulaBar, DisplayFullScreen, DisplayNoteIndicator, DisplayCommentIndicator, DisplayExcel4Menus, DisplayRecentFiles, DisplayScrollBars, DisplayStatusBar, EditDirectlyInCell, EnableAutoComplete, EnableCancelKey, EnableSound, EnableTipWizard, FixedDecimal, FixedDecimalPlaces, Height, IgnoreRemoteRequests, Interactive, Iteration, LargeButtons, Left, MaxChange, MaxIterations, MoveAfterReturn, MoveAfterReturnDirection, ODBCTimeout, OnCalculate, OnData, OnDoubleClick, OnEntry, OnSheetActivate, OnSheetDeactivate, OnWindow, PivotTableSelection, PromptForSummaryInfo, ReferenceStyle, RollZoom, ScreenUpdating, SheetsInNewWorkbook, ShowChartTipNames, ShowChartTipValues, StandardFont, StandardFontSize, StatusBar, ShowToolTips, Top, DefaultSaveFormat, TransitionMenuKey, TransitionMenuKeyAction, TransitionNavigKeys, UserControl, UserName, Visible, Width, WindowState, UILanguage, DefaultSheetDirection, CursorMovement, ControlCharacters, EnableEvents, DisplayInfoWindow, ExtendList, AutoPercentEntry, ShowWindowsInTaskbar, FeatureInstall, FindFormat, ReplaceFormat, CalculationInterruptKey, DisplayFunctionToolTips, AutomationSecurity, DisplayPasteOptions, DisplayInsertOptions, GenerateGetPivotData, AutoFormatAsYouTypeReplaceHyperlinks, MapPaperSize, ShowStartupDialog, DecimalSeparator, ThousandsSeparator, UseSystemSeparators, DisplayDocumentActionTaskPane]

OLE Methods

irb(main):008:0>
irb(main):009:0* methods = excel.ole_methods
=> [QueryInterface, AddRef, Release, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames, Invoke, Application, Creator, Parent, ActiveCell, ActiveChart, ActiveDialog, ActiveMenuBar, ActivePrinter, ActivePrinter, ActiveSheet, ActiveWindow, ActiveWorkbook, AddIns, Assistant, Calculate, Cells, Charts, Columns, CommandBars, DDEAppReturnCode, DDEExecute, DDEInitiate, DDEPoke, DDERequest, DDETerminate, DialogSheets, Evaluate, _Evaluate, ExecuteExcel4Macro, Intersect, MenuBars, Modules, Names, Range, Rows, Run, _Run2, Selection, SendKeys, Sheets, ShortcutMenus, ThisWorkbook, Toolbars, Union, Windows, Workbooks, WorksheetFunction, Worksheets, Excel4IntlMacroSheets, Excel4MacroSheets, ActivateMicrosoftApp, AddChartAutoFormat, AddCustomList, AlertBeforeOverwriting, AlertBeforeOverwriting, AltStartupPath, AltStartupPath, AskToUpdateLinks, AskToUpdateLinks, EnableAnimations, EnableAnimations, AutoCorrect, Build, CalculateBeforeSave, CalculateBeforeSave, Calculation
, Calculation, Caller, CanPlaySounds, CanRecordSounds, Caption, Caption, CellDragAndDrop, CellDragAndDrop, CentimetersToPoints, CheckSpelling, ClipboardFormats, DisplayClipboardWindow, DisplayClipboardWindow, ColorButtons, ColorButtons, CommandUnderlines, CommandUnderlines, ConstrainNumeric, ConstrainNumeric, ConvertFormula, CopyObjectsWithCells, CopyObjectsWithCells, Cursor, Cursor, CustomListCount, CutCopyMode, CutCopyMode, DataEntryMode, DataEntryMode, Dummy1, Dummy2, Dummy3, Dummy4, Dummy5, Dummy6, Dummy7, Dummy8, Dummy9, Dummy10, Dummy11, _Default, DefaultFilePath, DefaultFilePath, DeleteChartAutoFormat, DeleteCustomList, Dialogs, DisplayAlerts, DisplayAlerts, DisplayFormulaBar, DisplayFormulaBar, DisplayFullScreen, DisplayFullScreen, DisplayNoteIndicator, DisplayNoteIndicator, DisplayCommentIndicator, DisplayCommentIndicator, DisplayExcel4Menus, DisplayExcel4Menus, DisplayRecentFiles, DisplayRecentFiles, DisplayScrollBars, DisplayScrollBars, DisplayStatusBar, DisplayStatusBar, DoubleClick, EditDirectlyInCell, EditDirectlyInCell, EnableAutoComplete, EnableAutoComplete, EnableCancelKey, EnableCancelKey, EnableSound, EnableSound, EnableTipWizard, EnableTipWizard, FileConverters, FileSearch, FileFind, _FindFile, FixedDecimal, FixedDecimal, FixedDecimalPlaces, FixedDecimalPlaces, GetCustomListContents, GetCustomListNum, GetOpenFilename, GetSaveAsFilename, Goto, Height, Height, Help, IgnoreRemoteRequests, IgnoreRemoteRequests, InchesToPoints, InputBox, Interactive, Interactive, International, Iteration, Iteration, LargeButtons, LargeButtons, Left, Left, LibraryPath, MacroOptions, MailLogoff, MailLogon, MailSession, MailSystem, MathCoprocessorAvailable, MaxChange, MaxChange, MaxIterations, MaxIterations, MemoryFree, MemoryTotal, MemoryUsed, MouseAvailable, MoveAfterRetur
n, MoveAfterReturn, MoveAfterReturnDirection, MoveAfterReturnDirection, RecentFiles, Name, NextLetter, NetworkTemplatesPath, ODBCErrors, ODBCTimeout, ODBCTimeout, OnCalculate, OnCalculate, OnData, OnData, OnDoubleClick, OnDoubleClick, OnEntry, OnEntry, OnKey, OnRepeat, OnSheetActivate, OnSheetActivate, OnSheetDeactivate, OnSheetDeactivate, OnTime, OnUndo, OnWindow, OnWindow, OperatingSystem, OrganizationName, Path, PathSeparator, PreviousSelections, PivotTableSelection, PivotTableSelection, PromptForSummaryInfo, PromptForSummaryInfo, Quit, RecordMacro, RecordRelative, ReferenceStyle, ReferenceStyle, RegisteredFunctions, RegisterXLL, Repeat, ResetTipWizard, RollZoom, RollZoom, Save, SaveWorkspace, ScreenUpdating, ScreenUpdating, SetDefaultChart, SheetsInNewWorkbook, SheetsInNewWorkbook, ShowChartTipNames, ShowChartTipNames, ShowChartTipValues, ShowChartTipValues, StandardFont, StandardFont, StandardFontSize, StandardFontSize, StartupPath, StatusBar, StatusBar, TemplatesPath, ShowToolTips, ShowToolTips, Top, Top, DefaultSaveFormat, DefaultSaveFormat, TransitionMenuKey, TransitionMenuKey, TransitionMenuKeyAction, TransitionMenuKeyAction, TransitionNavigKeys, TransitionNavigKeys, Undo, UsableHeight, UsableWidth, UserControl, UserControl, UserName, UserName, Value, VBE, Version, Visible, Visible, Volatile, _Wait, Width, Width, WindowsForPens, WindowState, WindowState, UILanguage, UILanguage, DefaultSheetDirection, DefaultSheetDirection, CursorMovement, CursorMovement, ControlCharacters, ControlCharacters, _WSFunction, EnableEvents, EnableEvents, DisplayInfoWindow, DisplayInfoWindow, Wait, ExtendList, ExtendList, OLEDBErrors, GetPhonetic, COMAddIns, DefaultWebOptions, ProductCode, UserLibraryPath, AutoPercentEntry, AutoPercentEntry, LanguageSettings, Dummy101, Dummy12, Answer
Wizard, CalculateFull, FindFile, CalculationVersion, ShowWindowsInTaskbar, ShowWindowsInTaskbar, FeatureInstall, FeatureInstall, Ready, Dummy13, FindFormat, FindFormat, ReplaceFormat, ReplaceFormat, UsedObjects, CalculationState, CalculationInterruptKey, CalculationInterruptKey, Watches, DisplayFunctionToolTips, DisplayFunctionToolTips, AutomationSecurity, AutomationSecurity, FileDialog, Dummy14, CalculateFullRebuild, DisplayPasteOptions, DisplayPasteOptions, DisplayInsertOptions, DisplayInsertOptions, GenerateGetPivotData, GenerateGetPivotData, AutoRecover, Hwnd, Hinstance, CheckAbort, ErrorCheckingOptions, AutoFormatAsYouTypeReplaceHyperlinks, AutoFormatAsYouTypeReplaceHyperlinks, SmartTagRecognizers, NewWorkbook, SpellingOptions, Speech, MapPaperSize, MapPaperSize, ShowStartupDialog, ShowStartupDialog, DecimalSeparator, DecimalSeparator, ThousandsSeparator, ThousandsSeparator, UseSystemSeparators, UseSystemSeparators, ThisCell, RTD, DisplayDocumentActionTaskPane, DisplayDocumentActionTaskPane, DisplayXMLSourcePane, ArbitraryXMLSupportAvailable, Support, GetTypeInfoCount, GetTypeInfo, GetIDsOfNames, Invoke]