SQLServer レコードの取得

ＳＱＬの結果を Ｅｘｃｅｌに出力する

読み込み用 SQL

lesson005.sql

select * from DimAccount

接続文字列

lesson004.con

DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;

Ｅｘｃｅｌ出力用スクリプト

lesson007.vbs

Option Explicit

Private fs
Set fs = CreateObject("Scripting.FileSystemObject")

'接続文字列 読み込み
Private tsCon
Set tsCon = fs.OpenTextFile(WScript.Arguments(0))

Private sCon
sCon = ""
If Not tsCon.AtEndOfStream Then
    sCon = tsCon.ReadLine
End If
tsCon.Close
Set tsCon = Nothing

'接続
Private con
Set con = CreateObject("ADODB.Connection")
With con
    .Provider         = "SQLOLEDB"
    .ConnectionString = sCon
    .Open
End With

'SQL 読み込み
Private tsSql
Set tsSql = fs.OpenTextFile(WScript.Arguments(1))

Private sSql
sSql = ""
Do Until tsSql.AtEndOfStream
    sSql = sSql & tsSql.ReadLine & vbNewLine
Loop
tsSql.Close
Set tsSql = Nothing
Set fs    = Nothing

'結果を Excel に保存
Dim excelApp
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible       = True
excelApp.DisplayAlerts = False '警告メッセージをOFF

'ブックを書き込み用で開く
Dim excelBook
Set excelBook = excelApp.Workbooks.Add

'シートを１枚だけにする
Dim iSheet
For iSheet = excelBook.WorkSheets.Count To 2 Step -1
    excelBook.WorkSheets(iSheet).Delete
Next
Dim excelSheet
Set excelSheet = excelBook.WorkSheets(1)

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = con
    .CursorType       = 0 'adOpenForwardOnly
    .LockType         = 1 'adLockReadOnly
    .Source           = sSql
    .Open
End With

If Not rs.EOF Then
    Dim iRow
    iRow = 1

    Dim iCol
    For iCol = 1 To rs.Fields.Count
        excelSheet.Cells(iRow, iCol).Value = rs.Fields(iCol - 1).Name & ""
    Next

    Do Until rs.EOF
        iRow = iRow + 1
        For iCol = 1 To rs.Fields.Count
            excelSheet.Cells(iRow, iCol).Value = rs.Fields(iCol - 1).Value & ""
        Next

        rs.MoveNext
    Loop
End If
rs.Close

'切断
con.Close
Set con = Nothing

'ブックを保存する
excelBook.SaveAs(WScript.Arguments(2))
excelApp.Quit 
Set excelApp = Nothing

実行形式

C:\>cscript c:\study\vbscript\chapter006\lesson007.vbs  c:\study\vbscript\chapte

r006\lesson004.con c:\study\vbscript\chapter006\lesson005.sql c:\study\vbscript\

chapter006\lesson007.xls //nologo

出力イメージ

lesson007.xls

AccountKey	ParentAccountKey	AccountCodeAlternateKey	ParentAccountCodeAlternateKey	AccountDescription	AccountType	Operator	CustomMembers	ValueType	CustomMemberOptions
1		1		Balance Sheet		~		Currency
2	1	10	1	Assets	Assets	+		Currency
3	2	110	10	Current Assets	Assets	+		Currency
4	3	1110	110	Cash	Assets	+		Currency
5	3	1120	110	Receivables	Assets	+		Currency
6	5	1130	1120	Trade Receivables	Assets	+		Currency
7	5	1140	1120	Other Receivables	Assets	+		Currency
8	3	1150	110	Allowance for Bad Debt	Assets	+		Currency
9	3	1160	110	Inventory	Assets	+		Currency
10	9	1162	1160	Raw Materials	Assets	+		Currency