ASF COM Prototyping - Extend Office Objects

1. Excel Range Enhancement 📊

▼

Add custom methods directly to Excel Range objects with method chaining support.

Simple Methods

// Add currency formatting
prototype.COM.Range currency() {
    this.NumberFormat = "$#,##0.00";
    return this;
}

// Add highlighting
prototype.COM.Range highlight() {
    this.Interior.Color = 65535;
    return this;
}

Test It:

With scriptEngine
    .AppAccess = True
    
    ' Define methods and use them in a single script
    pid = .Compile("prototype.COM.Range currency() { " & _
                   "this.NumberFormat = '$#,##0.00'; return this; }; " & _
                   "prototype.COM.Range highlight() { " & _
                   "this.Interior.Color = 65535; return this; }; " & _
                   "$1.Range('A1:A3').currency(); " & _
                   "$1.Range('B1:B3').currency().highlight();")
    .Run pid, ActiveSheet
End With

Key Features: Native feel • Method chaining • Easy to test

2. Word Document Methods 📝

▼

Add smart methods to Word Document objects for quick analysis and formatting.

Document Methods

// Get word count
prototype.COM.Document wordCount() {
    return this.Range.Words.Count - 1;
}

// Make title
prototype.COM.Document makeTitle() {
    this.Paragraphs(1).Range.Style = 'Title';
    return this;
}

Test It:

With scriptEngine
    .AppAccess = True
    
    ' Define methods and use them in a single script
    pid = .Compile("prototype.COM.Document wordCount() { " & _
                   "return this.Range.Words.Count - 1; }; " & _
                   "prototype.COM.Document makeTitle() { " & _
                   "this.Paragraphs(1).Range.Style = 'Title'; return this; }; " & _
                   "let words = $1.wordCount(); print(`Words: ${words}`); " & _
                   "$1.makeTitle();")
    .Run pid, ActiveDocument
End With

Smart Features: Document analysis • Auto-formatting • Chainable methods

3. PowerPoint Slide Magic 🎨

▼

Add automation methods to PowerPoint slides for instant theming and content.

Slide Methods

// Apply theme
prototype.COM.Slide theme() {
    this.Background.Fill.ForeColor.RGB = 16777215;
    if (this.Shapes.Count > 0) {
        this.Shapes(1).TextFrame.TextRange.Font.Size = 28;
    }
    return this;
}

// Add footer
prototype.COM.Slide footer(text) {
    this.Shapes.AddTextbox(1, 30, 500, 660, 30)
        .TextFrame.TextRange.Text = text;
    return this;
}

Test It:

With scriptEngine
    .AppAccess = True
    
    ' Define methods and use them in a single script
    pid = .Compile("prototype.COM.Slide theme() { " & _
                   "this.Background.Fill.ForeColor.RGB = 16777215; " & _
                   "if (this.Shapes.Count > 0) { this.Shapes(1).TextFrame.TextRange.Font.Size = 28; } " & _
                   "return this; }; " & _
                   "prototype.COM.Slide footer(text) { " & _
                   "this.Shapes.AddTextbox(1, 30, 500, 660, 30).TextFrame.TextRange.Text = text; " & _
                   "return this; }; " & _
                   "$1.Slides(1).theme(); " & _
                   "$1.Slides(1).theme().footer('My Company');")
    .Run pid, ActivePresentation
End With

Automation Power: Instant theming • Content generation • Method chaining

4. Excel Table Smarts 🗂️

▼

Convert Excel table rows to dictionaries for easy column-based data access.

Smart Tables

// Row to dictionary
prototype.COM.ListRow asDict() {
    let headers = this.parent.listcolumns;
    let values = this.range.value2;
    let dict = {};
    
    for (let i = 1; i <= headers.count; i += 1) {
        dict.set(headers.item(i).name, values[1][i]);
    }
    
    return dict;
}

Test It:

⚠️ Collection Methods Required: This example uses array methods on ListRows collection, requiring OverrideCollMethods = True

With scriptEngine
    .AppAccess = True
    .OverrideCollMethods = True  ' Required for array methods on collections
    
    ' Define method and use it in a single script
    pid = .Compile("prototype.COM.ListRow asDict() { " & _
                   "let headers = this.parent.listcolumns; " & _
                   "let values = this.range.value2; let dict = {}; " & _
                   "for (let i = 1; i <= headers.count; i += 1) { " & _
                   "dict.set(headers.item(i).name, values[1][i]); } return dict; }; " & _
                   "let row = $1.ListObjects('Table1').ListRows(1).asDict(); " & _
                   "let name = row.get('Name'); let email = row.get('Email'); " & _
                   "print('Name: ' + name + ', Email: ' + email);")
    .Run pid, ActiveSheet
End With

Smart Access: Column names not indexes • Dictionary conversion • Easy data access

5. Cross-App Integration ⚡

▼

Create simple workflows between Office applications with custom methods.

Simple Integration

// Excel to Word
prototype.COM.Range toWord() {
    let word = @(CreateObject("Word.Application"));
    word.Visible = true;
    let doc = word.Documents.Add();
    
    let data = this.value2;
    doc.Range().Text = data.map(fun(row) { 
        return row.join('\t'); 
    }).join('\n');
    
    return doc;
}

Test It:

With scriptEngine
    .AppAccess = True
    
    ' Define method and use it in a single script
    pid = .Compile("prototype.COM.Range toWord() { " & _
                   "let word = @(CreateObject('Word.Application')); word.Visible = true; " & _
                   "let doc = word.Documents.Add(); let data = this.value2; " & _
                   "doc.Range().Text = data.map(fun(row) { return row.join('\t'); }).join('\n'); " & _
                   "return doc; }; " & _
                   "let doc = $1.Range('A1:C3').toWord(); print('Created Word document');")
    .Run pid, ActiveSheet
End With

Integration Power: Cross-app workflows • Data sharing • One language for all Office

6. Collections + Array Methods 🔄

▼

Use JavaScript array methods directly on Office collections like Sheets, Slides, and Tables.

Array Power

// Process sheets
let names = $1.Sheets
    .map(fun(sheet) { return sheet.Name; })
    .filter(fun(name) { return !name.startsWith('_'); });

// Process slides
$1.Slides
    .filter(fun(slide) { return slide.Shapes.Count > 0; })
    .forEach(fun(slide) { 
        slide.theme(); 
    });

Test It:

⚠️ Collection Methods Required: This example uses array methods on Office collections, requiring OverrideCollMethods = True

With scriptEngine
    .AppAccess = True
    .OverrideCollMethods = True  ' Required for array methods on collections
    
    ' Process collections in a single script
    pid = .Compile("let names = $1.Sheets" & _
                   ".map(fun(sheet) { return sheet.Name; })" & _
                   ".filter(fun(name) { return !name.startsWith('_'); }); " & _
                   "print('Sheets: ' + names.join(', '));")
    .Run pid, ActiveWorkbook
    
    ' For PowerPoint (separate script for different object type)
    ' pid = .Compile("$1.Slides" & _
    '                ".filter(fun(slide) { return slide.Shapes.Count > 0; })" & _
    '                ".forEach(fun(slide) { slide.theme(); }); " & _
    '                "print('Themed slides with shapes');")
    ' .Run pid, ActivePresentation
End With

Collection Magic: .map(), .filter(), .forEach() on Office collections • Functional programming • Batch processing