VBScript で Excel
Excel シートの内容を 取得する (ADO)
↓ こんな Excel ファイルを読む
A | B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|---|
1 | A1 | B1 | C1 | D1 | E1 | F1 | G1 | H1 | J1 | |
2 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | H2 | J2 | |
3 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | H3 | J3 | |
4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | H4 | J4 | |
5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | H5 | J5 | |
6 | J6 | |||||||||
7 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | H7 | I7 | J7 |
実行結果
Dim cn' as ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WScript.Arguments(0) & ";" & "Extended Properties=Excel 8.0;"
.CursorLocation = 3' adUseClient
.Open
End WithDim rs' As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
With rs
.Source = "SELECT * FROM [" + WScript.Arguments(1) + "$]"
.Activeconnection = cn
.CursorType = 0' adOpenForwardOnly
.LockType = 1' adLockReadOnly
.Open
End WithDo Until rs.EOF
Dim s: s = ""
For i = 0 To rs.Fields.Count - 1
s = s & rs.Fields(i).Value & ","
Next
WScript.Echo Left(s, Len(s) - 1)
rs.MoveNext
Looprs.Close
cn.Close
C:\>cscript c:\study\vbscript\chapter004\lesson008.vbs c:\study\Book1.xls Sheet1
//nologo
A2,B2,C2,D2,E2,F2,G2,H2,,J2
A3,B3,C3,D3,E3,F3,G3,H3,,J3
A4,B4,C4,D4,E4,F4,G4,H4,,J4
A5,B5,C5,D5,E5,F5,G5,H5,,J5
,,,,,,,,,J6
A7,B7,C7,D7,E7,F7,G7,H7,I7,J7