Program Page: セルのクリア

カテゴリー名：　［ExcelVBAとOLE　基本概念とセル操作］

関連の解説ページへ戻る

《このページの目次》

• 1.　JScript
  • △　vovXL06.js
• 2.　VBScript
  • △　vovXL06.vbs

1.　JScript

△　vovXL06.js

// セルのクリア
var fso, bookPath;
var ExlApp, wb, ws;
var xlWorkbookNormal = -4143;

fso = WScript.CreateObject("Scripting.FileSystemObject");
bookPath = fso.GetAbsolutePathName("Book1.xls");
if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
ExlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
ExlApp.Visible = true;  // Excelを見える状態に
wb = ExlApp.Workbooks.Add();  // Workbookの新規作成
ws = wb.ActiveSheet;
PutData(ws);
ws.Range("A1:C4").Copy(ws.Range("A6:C9"));
with (ws) {
    Range("A1:C1").ClearContents();
    Range("D1").Value = "ClearContents";
    Range("A2:C2").ClearFormats();
    Range("D2").Value = "ClearFormats";
    Range("A3").ClearComments();
    Range("D3").Value = "ClearComments";
    Range("A4:C4").Clear();
    Range("D4").Value = "Clear";
}
wb.SaveAs(bookPath, xlWorkbookNormal);
ExlApp.Quit();

function PutData(ws) {
    var ary, i;
    ws.UsedRange.Clear;
    ary = [189, 365, "=SUM(A1:B1)"];
    for (i=1; i<=ary.length; i++){
        ws.Cells(1,i).Value = ary[i-1];
        ws.Cells(1,i).Interior.ColorIndex = 15;
    }
    with (ws.Range("C1")) {
        AddComment("合計欄");
        Comment.Visible = true;
    }
    ary = [4, 5, 6];
    for (i=1; i<=ary.length; i++) {
        with(ws.Cells(2,i)) {
            Value = ary[i-1];
            NumberFormatLocal = "000";
            Font.Size = 20;
            Font.ColorIndex = 5;
        }
    }
    with (ws.Range("A3")) {
        Value = "=TODAY()";
        NumberFormatLocal = "m月d日";
        AddComment("今日の日付");
        Comment.Visible = true;
    }
    ary = [7, 8, 9];
    for (i=1; i<=ary.length; i++) {
        with (ws.Cells(4,i)) {
            Value = ary[i-1];
            Font.ColorIndex = 3;
            Interior.ColorIndex = 15;
        }
    }
}

目次に戻る

2.　VBScript

△　vovXL06.vbs

' セルのクリア
Option Explicit
Dim FSO, BookPath
Dim EXLapp, WBobj, WSobj
Const xlWorkbookNormal = -4143

Set FSO = CreateObject("Scripting.FileSystemObject")
BookPath = FSO.GetAbsolutePathName("Book1.xls")
If (FSO.FileExists(BookPath) = True) Then FSO.DeleteFile(BookPath)
Set EXLapp = CreateObject("Excel.Application")  ' Excelの起動
EXLapp.Visible = True  ' Excelを見える状態に
Set WBobj = EXLapp.Workbooks.Add()  ' Workbookの新規作成
Set WSobj = WBobj.ActiveSheet
PutData WSobj
WSobj.Range("A1:C4").Copy WSobj.Range("A6:C9")
With WSobj
    .Range("A1:C1").ClearContents
    .Range("D1").Value = "ClearContents"
    .Range("A2:C2").ClearFormats
    .Range("D2").Value = "ClearFormats"
    .Range("A3").ClearComments
    .Range("D3").Value = "ClearComments"
    .Range("A4:C4").Clear
    .Range("D4").Value = "Clear"
End With
WBobj.SaveAs BookPath, xlWorkbookNormal
EXLapp.quit

Sub PutData(WSobj)
    WSobj.UsedRange.Clear
    With WSobj.Range("A1:C1")
        .Value = Array(189, 365, "=SUM(A1:B1)")
        .Interior.ColorIndex = 15
    End With
    With WSobj.Range("C1")
        .AddComment "合計欄"
        .Comment.Visible = True
    End With
    With WSobj.Range("A2:C2")
        .Value = Array(4, 5, 6)
        .NumberFormatLocal = "000"
        .Font.Size = 20
        .Font.ColorIndex = 5
    End With
    With WSobj.Range("A3")
        .Value = "=TODAY()"
        .NumberFormatLocal = "m月d日"
        .AddComment "今日の日付"
        .Comment.Visible = True
    End With
    With WSobj.Range("A4:C4")
        .Value = Array(7, 8, 9)
        .Font.ColorIndex = 3
        .Interior.ColorIndex = 15
    End With
End Sub

〜　以上　〜