Program Page: VOV(VBA, OLE, VBScript)による自動操作 トップページ

2016/08/13

関連の解説ページへ戻る


《このページの目次》


    

1. JScript

△ vov_xls.js

// Excelワークブックの作成
var fso, bookPath;
var ExlApp, wb, ws;
var xlWorkbookNormal = -4143;

fso = WScript.CreateObject("Scripting.FileSystemObject");
bookPath = fso.GetAbsolutePathName("test.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);
ws.Range("A1").Value = "Hello!";
wb.SaveAs(bookPath, xlWorkbookNormal);
wb.Close();
ExlApp.Quit();

目次に戻る


    

△ vov_doc.js

// Word文書の作成
var fso, docPath;
var wdApp, doc, para;
var WdFormatDocument = 0;

fso = WScript.CreateObject("Scripting.FileSystemObject");
docPath = fso.GetAbsolutePathName("test.doc");
if (fso.FileExists(docPath))  fso.DeleteFile(docPath);
wdApp = WScript.CreateObject("Word.Application") ;  // Wordの起動
wdApp.Visible = true ;  // Wordを見える状態に
doc = wdApp.Documents.Add();
para = doc.Content.paragraphs.Add();
para.Range.Text = "Hello!";
doc.SaveAs(docPath, WdFormatDocument);
doc.Close();
wdApp.Quit();

目次に戻る


    

△ vov_ppt.js

// PowerPointスライドの作成
var fso, pptPath;
var pptApp, presenObj, slideCount, newSlide, shapeObj;
var ppSaveAsPresentation = 1;
var ppLayoutTitleOnly = 11;

fso = WScript.CreateObject("Scripting.FileSystemObject");
pptPath = fso.GetAbsolutePathName("test.ppt");
if (fso.FileExists(pptPath))  fso.DeleteFile(pptPath);
pptApp = WScript.CreateObject("PowerPoint.Application") ;  // PowerPointの起動
pptApp.Visible = true ;  // PowerPointを見える状態に
presenObj = pptApp.Presentations.Add();
slideCount = presenObj.Slides.Count;  // 現在のスライド枚数
newSlide = presenObj.Slides.Add(slideCount+1, ppLayoutTitleOnly);
newSlide.Shapes.Title.TextFrame.TextRange.Text = "TestSlide";
shapeObj = newSlide.Shapes.AddTextbox(1, 100, 100, 200, 50);
shapeObj.textFrame.textRange.text = "Hello!";
presenObj.SaveAs(pptPath, ppSaveAsPresentation);
presenObj.Close();
pptApp.Quit();

目次に戻る


    

△ vov_mdb.js

// Accessデータベースファイル test.mdb を作成。Accessは使わない。
var DbName, TableName, TypeStr;
var fso, DbPath;
var CN, ConnStr, CAT, RS, sql, Items;

RunOn32bit();  // 32bitコマンドで当スクリプトを実行
DbName = "test.mdb";
TableName = "TestTable";
TypeStr = "氏名 varchar(20),身長 float";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName("test.mdb");
if (fso.FileExists(DbPath))  fso.DeleteFile(DbPath);

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + DbPath + ";";
CAT = WScript.CreateObject("ADOX.Catalog");
CN = CAT.Create(ConnStr) ;  // データベース作成

sql = "create table " + TableName + " (" + TypeStr + ");";
CN.Execute(sql) ;  // テーブル作成

Items = ["氏名", "身長"];
RS = WScript.CreateObject("ADODB.Recordset");
sql = "select * from " + TableName + ";";
RS.Open(sql,CN,0,2,1);
RS.AddNew(Items, ["鈴木", 172.3]);
RS.Update();
RS.AddNew(Items, ["高橋", 168.5]);
RS.Update();
RS.AddNew(Items, ["田中", 183.6]);
RS.Update();
RS.Close();
CN.Close();


function RunOn32bit() {
    var p_objWshShell, p_admArg, p_admArrayArguments;
    var p_admWscriptCscript, p_admCommand, i;
    p_objWshShell = WScript.CreateObject("Wscript.Shell");
    if (p_objWshShell.Environment("Process").Item(
      "PROCESSOR_ARCHITECTURE") != "x86") {
        p_admArrayArguments = "";
        if (WScript.Arguments.length > 0) {
            for (i = 0; i < WScript.Arguments.length; i++) {
              p_admArg = WScript.Arguments(i);
              p_admArrayArguments = p_admArrayArguments +
                  " \"" + p_admArg + "\"";
            }
        }
        if (WScript.FullName.toLowerCase().indexOf("wscript") > 0) {
            p_admWscriptCscript = "WScript.exe";
        } else {
            p_admWscriptCscript = "CScript.exe";
        }
        p_admCommand = "\"" + p_objWshShell.Environment("Process").Item(
            "windir") + "\\SysWOW64\\" + p_admWscriptCscript + "\" \"" +
            WScript.ScriptFullName + "\"" + p_admArrayArguments;
        p_objWshShell.Run(p_admCommand);
        WScript.Quit();
    }
}

* RunOn32bit は、下記のサイトから拝借しました。

WSH VBScript - ひとり64bit OS対応 - ノートの余白みたいなもの

目次に戻る


    

△ vov_mdb_xls.js

// test.mdb → test_mdb.xls の変換。ExcelのQueryTableを利用。
var fso, bookName, bookPath;
var ExlApp, wb, ws, qt;
var DbName, DbPath, sql, ConnStr;
var xlWorkbookNormal = -4143;

DbName = "test.mdb";
sql = "select * from TestTable;";

fso = WScript.CreateObject("Scripting.FileSystemObject");
DbPath = fso.GetAbsolutePathName(DbName);
if (fso.FileExists(DbPath) == false) {
    WScript.Echo(DbName + "がみつかりません.");
    WScript.Quit();
}
ConnStr = "ODBC;DSN=MS Access Database;DBQ=" + DbPath;

bookName = DbName.replace(/\./g, "_") + ".xls";
bookPath = fso.GetAbsolutePathName(bookName);
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);
qt = ws.QueryTables.Add(ConnStr, ws.Range("A1"), sql);
with (qt) {
    BackgroundQuery = false;  // バックグラウンド処理をしない
    Refresh();
    Delete();  // クエリテーブルを削除
}
wb.SaveAs(bookPath, xlWorkbookNormal);
ExlApp.Quit();

目次に戻る


    

2. VBScript

△ vov_xls.vbs

' Excelワークブックの作成
Option Explicit
Dim FSO, BookPath
Dim EXLapp, WBobj, WSobj
Const xlWorkbookNormal = -4143

Set FSO = CreateObject("Scripting.FileSystemObject")
BookPath = FSO.GetAbsolutePathName("test.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)
WSobj.Range("A1").Value = "Hello!"
WBobj.SaveAs BookPath, xlWorkbookNormal
WBobj.Close
EXLapp.Quit

目次に戻る


    

△ vov_doc.vbs

' Word文書の作成
Option Explicit
Dim FSO, DocPath
Dim WDapp, DOCobj, Para
Const WdFormatDocument = 0

Set FSO = CreateObject("Scripting.FileSystemObject")
DocPath = FSO.GetAbsolutePathName("test.doc")
If (FSO.FileExists(DocPath) = True) Then FSO.DeleteFile(DocPath)
Set WDapp = CreateObject("Word.Application")  ' Wordの起動
WDapp.Visible = True  ' Wordを見える状態に
Set DOCobj = WDapp.Documents.Add()
Set Para = DOCobj.Content.Paragraphs.Add
Para.Range.Text = "Hello!"
DOCobj.SaveAs DocPath, WdFormatDocument
DOCobj.Close
WDapp.Quit

目次に戻る


    

△ vov_ppt.vbs

' PowerPointスライドの作成
Option Explicit
Dim FSO, PptPath
Dim PPTapp, PresenObj, NewSlide, SlideCount, ShapeObj
Const ppSaveAsPresentation = 1
Const ppLayoutTitleOnly = 11

Set FSO = CreateObject("Scripting.FileSystemObject")
PptPath = FSO.GetAbsolutePathName("test.ppt")
If (FSO.FileExists(PptPath) = True) Then FSO.DeleteFile(PptPath)
Set PPTapp = CreateObject("PowerPoint.Application")  ' PowerPointの起動
PPTapp.Visible = True  ' PowerPointを見える状態に
Set PresenObj = PPTapp.Presentations.Add()
SlideCount = PresenObj.Slides.Count  ' 現在のスライド枚数
Set NewSlide = PresenObj.Slides.Add(SlideCount+1, ppLayoutTitleOnly)
NewSlide.Shapes.Title.TextFrame.TextRange.Text = "TestSlide"
Set ShapeObj = NewSlide.Shapes.AddTextbox(1, 100, 100, 200, 50)
ShapeObj.textFrame.textRange.text = "Hello!"
PresenObj.SaveAs PptPath, ppSaveAsPresentation
PresenObj.Close
PPTapp.quit

目次に戻る


    

△ vov_mdb.vbs

' Accessデータベースファイル test.mdb を作成。Accessは使わない。
Option Explicit
Dim DbName, TableName, TypeStr
Dim FSO, DbPath
Dim CN, ConnStr, CAT, RS, sql, Items

RunOn32bit  ' 32bitコマンドで当スクリプトを実行
DbName = "test.mdb"
TableName = "TestTable"
TypeStr = "氏名 varchar(20),身長 float"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName("test.mdb")
If (FSO.FileExists(DbPath) = True) Then FSO.DeleteFile(DbPath)

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DbPath & ";"
Set CAT = CreateObject("ADOX.Catalog")
Set CN = CAT.Create(ConnStr)  ' データベース作成

sql = "create table " & TableName & " (" & TypeStr & ");"
CN.Execute(sql)  ' テーブル作成

Items = Array("氏名", "身長")
Set RS = CreateObject("ADODB.Recordset")
sql = "select * from " & TableName & ";"
RS.Open sql,CN,0,2,1
RS.AddNew Items, Array("鈴木", 172.3)
RS.Update
RS.AddNew Items, Array("高橋", 168.5)
RS.Update
RS.AddNew Items, Array("田中", 183.6)
RS.Update
RS.Close
CN.Close

目次に戻る


    

△ vov_mdb_xls.vbs

' test.mdb → test_mdb.xls の変換。ExcelのQueryTableを利用。
Option Explicit
Dim FSO, BookName, BookPath
Dim EXLapp, WBobj, WSobj, QTobj
Dim DbName, DbPath, sql, ConnStr
Const xlWorkbookNormal = -4143

DbName = "test.mdb"
sql = "select * from TestTable;"

Set FSO = CreateObject("Scripting.FileSystemObject")
DbPath = FSO.GetAbsolutePathName(DbName)
If (FSO.FileExists(DbPath) = False) Then
    WScript.Echo DbName & "がみつかりません."
    WScript.Quit
End If
ConnStr = "ODBC;DSN=MS Access Database;DBQ=" & DbPath

BookName = Replace(DbName, ".", "_") & ".xls"
BookPath = FSO.GetAbsolutePathName(BookName)
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)
Set QTobj = WSobj.QueryTables.Add(ConnStr, WSobj.Range("A1"), sql)
With QTobj
    .BackgroundQuery = False  ' バックグラウンド処理をしない
    .Refresh
    .Delete  ' クエリテーブルを削除
End With
WBobj.SaveAs BookPath, xlWorkbookNormal
EXLapp.quit

〜 以上 〜


VOVシリーズ トップページ