Microsoft Office VBA ActiveX编程的JScript实现

本文要点：

1. 在JScript中使用VBArray
2. 在JScript中调用需要传入VB数组的方法
3. 在JScript中调用VBA的命名参数化的方法
   
4. 示例Word.Application的文件写入、图片插入、文件保存等等
5. 示例Excel.Application的单元格、多行、区域的复制等等

/************************* Common Functions **********************************/
/* Tips: an alert emulator for Wscript host */
function alert(msg){
    WScript.Echo(msg+"");
}

/* Tips: auto close popup in 3 seconds */
function popup(msg,delay){
    delay = delay || 3
    var sh = new ActiveXObject("Wscript.Shell");
    var tip = "Tips in %D seconds".replace("%D",delay);
    sh.popup(msg+"", delay, tip, 64);  
}

/* Tips: use safe VB Array for VBArray in JScript 8.0, this method can be use in multiple VB Array
 * http://stackoverflow.com/questions/848246/how-can-i-use-javascript-within-an-excel-macro
 * Return Keys as a SafeArray, then can be use: new VBArray(safeArray); */
function toVBArray(jsArray) {
    var dict = new ActiveXObject("Scripting.Dictionary");
    for (var i in jsArray) dict.Add(i, jsArray[i]);
    return dict.Keys();
}

/************************* Office Word Application ***************************/
var WdSaveOptions = { wdDoNotSaveChanges:0, wdPromptToSaveChanges:-2, wdSaveChanges:-1 }
var WdBuiltinStyle = {
    wdStyleNormal:-1,   // 正文。 
    wdStyleHeading1:-2, // 标题 1~10。 
    wdStyleHeading2:-3,
    wdStyleHeading3:-4,
    wdStyleHeading4:-5,
    wdStyleHeading5:-6,
    wdStyleHeading6:-7,
    wdStyleHeading7:-8,
    wdStyleHeading8:-9,
    wdStyleHeading9:-10,
    wdStyleNormalIndent:-29, // 正文缩进。 
    wdStyleBodyText:-67, // 正文文本。
    wdStyleHyperlink:-86, // 超链接。 
    wdStylePlainText:-91, // 纯文本。 
    wdStyleQuote:-181  // 引用。 
    // ... and more
};
var WdSaveFormat = {
    wdFormatDocument:0, // Microsoft Office Word 格式。 
    wdFormatDOSText:4, // Microsoft DOS 文本格式。 
    wdFormatDOSTextLineBreaks:5, // Microsoft DOS 文本格式，并且保留换行符。 
    wdFormatEncodedText:7, // 编码文本格式。 
    wdFormatFilteredHTML:10, // 筛选的 HTML 格式。 
    wdFormatHTML:8, // 标准 HTML 格式。 
    wdFormatRTF:6, // RTF 格式。 
    wdFormatTemplate:1, // Word 模板格式。 
    wdFormatDocument97:0, // Microsoft Word 97 文档格式。 
    wdFormatDocumentDefault:16 // Word 默认文档文件格式。对于 Microsoft Office Word 2007，这是 DOCX 格式。 
    //...
};

/* 1. Create Winword application and let it visible */
var Word = new ActiveXObject("Word.Application");
Word.Visible = true;
Word.DisplayAlerts = false;

/* 2. Prepare a new document to writing, NOTE: the method Add does follow parentheses */
var Doc = Word.Documents.Add();


/* 3. Writes a contents and set style */
var p = Doc.Paragraphs.Add();
p.Range.Text = "Comming";
p.Style = Doc.Styles(WdBuiltinStyle.wdStyleHeading1);

var p = Doc.Paragraphs.Add(); // create a new paragraph but the Range is the same
p.Range.Text += "Hot baby to this document";
p.Style = Doc.Styles(WdBuiltinStyle.wdStyleNormal);

var p = Doc.Paragraphs.Add(); 
p.Range.Text += "Hot baby to this document";

//p.Range.InsertFile("./some.txt.xls.doc");

popup("Insert Picture",6);
Doc.SaveAs("./T.DOC",WdSaveFormat.wdFormatDocument97);
var path = "./md1.png";
var p = Doc.Paragraphs.Add(); // add empty on avoid replace when Paste().
var p = Doc.Paragraphs.Add();
p.Range.Text = "PLACEHOLDER";
var iShape = Doc.InlineShapes.AddPicture(path); // Shapes or InlineShapes
iShape.Range.Cut();
//iShape.Range.Select();
//Word.Selection.Cut();
p.Range.Paste();


popup("Winword be ready to exit",6);

/* 4. Delete or Save you document */
Doc.Save();
//Doc.Close(WdSaveOptions.wdDoNotSaveChanges);
//Doc.SaveAs("./T.DOC");

/* 5. Writes a paragraph */
Word.Quit();



/*************************** Office Excel Application ************************/

/* 1. create Excel application, and setting options
 * Excel.Application can be found in reg HKCR\Excel.Application or EXCEL.EXE */
var Excel = new ActiveXObject("Excel.Application");
//var Sheet = new ActiveXObject("Excel.Sheet");
Excel.Visible = true;
Excel.DisplayAlerts = false;

/* 2. Prepare Workbook to working, NOTE: the method Add not follow parentheses */
var WorkBook = Excel.Workbooks.Add

/* Tips: Reorder sheets use named parameters method in VBA */
WorkBook.Sheets("Sheet1").Move(null, WorkBook.Sheets("Sheet3")); // Move after sheet2

/* Tips: Delete wasted sheets */
var a = toVBArray(["Sheet1","Sheet2","Sheet3"]); 
//WorkBook.Sheets(a).Delete();

/* 3. Optionally you can create a new Sheet */
var Sheet = Excel.Sheets.Add;
Sheet.Name = "New Sheet";
Sheet.Cells(1,1).Value = "This is column A, row 1";

//WorkBook.Sheets("Sheet1").Move( "after",WorkBook.Sheets("Sheet2") );

/* 4. Optionally save or delete your work */
//WorkBook.Close(false);
WorkBook.SaveAs("./T.XLS");
popup("T.XLS save to:\r\n"+Sheet.Application.ActiveWorkbook.Path);

/* 5. Keep away close application */
Excel.Quit();