// 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();
// 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();
// 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();
// 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対応 - ノートの余白みたいなもの
// 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();
' 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
' 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
' 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
' 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
' 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
〜 以上 〜