Program Page: セルの別の箇所への複写

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

関連の解説ページへ戻る

《このページの目次》

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

1.　JScript

△　vovXL08.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.Worksheets(1);
PutData(ws);
with (ws) {
    Range("A1:B2").Copy(Range("A5"));
    Range("B5").Cut(range("B8"));
    Range("A6").Cut(range("A9"));
}
wb.Worksheets(1).Range("A1:B2").Copy(wb.Worksheets(2).Range("A1"));
wb.SaveAs(bookPath, xlWorkbookNormal);
ExlApp.Quit();

function PutData(ws) {
    var ary = [["ice", "water"], ["rain", "snow"]];
    ws.UsedRange.Clear();
    for (var i=1; i<=ary.length; i++) {
        for (var j=1; j<=ary[i-1].length; j++) {
            ws.Cells(i,j).Value = ary[i-1][j-1];
        }
    }
}

目次に戻る

2.　VBScript

△　vovXL08.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.Worksheets(1)
Call PutData(WSobj)
With WSobj
    .Range("A1:B2").Copy .Range("A5")
    .Range("B5").Cut .range("B8")
    .Range("A6").Cut .range("A9")
End With
WBobj.Worksheets(1).Range("A1:B2").Copy WBobj.Worksheets(2).Range("A1")
WBobj.SaveAs BookPath, xlWorkbookNormal
EXLapp.quit

Sub PutData(WSobj)
    With WSobj
        .UsedRange.Clear
        .Range("A1:B1").Value = Array("ice", "water")
        .Range("A2:B2").Value = Array("rain", "snow")
    End With
End Sub

〜　以上　〜