Option Explicit
Private fs
Private conn
Private excelApp
Private excelBook
Private excelSheet
Private Sub openSheet(iSheet, sqlFile)
If iSheet > excelBook.WorkSheets.Count Then
excelBook.Sheets.Add , excelBook.WorkSheets(iSheet - 1)
End If
Set excelSheet = excelBook.WorkSheets(iSheet)
excelSheet.Name = Replace(UCase(sqlFile.Name), ".SQL", "")
End Sub
Private Sub closeSheet()
With excelSheet.Cells.Font
.Name = "MeiryoKe_Console"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Name = "Consolas"
.Size = 10
End With
With excelSheet.Rows("1:1")
.Font.ColorIndex = 2
.Interior.ColorIndex = 55
End With
End Sub
Private Sub writeResult(rs, iSheet, sqlFile)
Call openSheet(iSheet, sqlFile)
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
closeSheet
End Sub
Private Function getSql(sqlFile)
WScript.Echo sqlFile.Name
Dim tsSql: Set tsSql = fs.OpenTextFile(sqlFile.Path)
Dim sqlText: sqlText = tsSql.ReadAll
tsSql.Close
Set tsSql = Nothing
getSql = sqlText
End Function
Private Function openRecordSet(sqlText)
Dim rs: Set rs = CreateObject("ADODB.Recordset")
With rs
.ActiveConnection = conn
.CursorType = 0
.LockType = 1
.Source = sqlText
.Open
End With
Set openRecordSet = rs
End Function
Private Sub openExcel()
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
Set excelBook = excelApp.Workbooks.Add
Dim iSheet
For iSheet = excelBook.WorkSheets.Count To 2 Step -1
excelBook.WorkSheets(iSheet).Delete
Next
End Sub
Private Sub closeExcel()
excelBook.SaveAs(WScript.Arguments(1))
excelApp.Quit
Set excelApp = Nothing
End Sub
Private Sub openDB()
Set conn = CreateObject("ADODB.Connection")
conn.Open "aaaaa","bbbbb","ccccc"
End Sub
Private Sub closeDB()
conn.Close
Set conn = Nothing
End Sub
Private Sub Main()
Set fs = CreateObject("Scripting.FileSystemObject")
openExcel
openDB
Dim sqlFolder: Set sqlFolder = fs.GetFolder(WScript.Arguments(0))
Dim sqlFile
Dim iSheet: iSheet = 0
For Each sqlFile In sqlFolder.Files
Dim sqlText: sqlText = getSql(sqlFile)
Dim rs: Set rs = openRecordSet(sqlText)
iSheet = iSheet + 1
Call writeResult(rs, iSheet, sqlFile)
rs.Close
Set rs = Nothing
Next
Set sqlFile = Nothing
Set sqlFolder = Nothing
closeDB
closeExcel
Set fs = Nothing
End Sub
Call Main()