VBScript で Excel

VBScript Excel COM 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

Excel シートの内容を 変更する (追加)

lesson009.vbs



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 With
' 追加

Dim sql

sql = ""

sql = sql & "INSERT INTO "

sql = sql & "    [" + WScript.Arguments(1) + "$] "

sql = sql & "( "

sql = sql & "     A1,   B1,   C1,   D1,   E1,   F1,   G1,   H1  "

sql = sql & ") "

sql = sql & "VALUES "

sql = sql & "( " 

sql = sql & "    'AX', 'BX', 'CX', 'DX', 'EX', 'FX', 'GX', 'HX' "

sql = sql & ")"

cn.Execute(sql)
Dim 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 With
Do 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

Loop
rs.Close

cn.Close
実行結果


C:\>cscript c:\study\vbscript\chapter004\lesson009.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

AX,BX,CX,DX,EX,FX,GX,HX,,

Excel シートの内容を 変更する (変更)

lesson010.vbs



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 With
' 変更

Dim sql

sql = ""

sql = sql & "UPDATE "

sql = sql & "    [" + WScript.Arguments(1) + "$] "

sql = sql & "SET "

sql = sql & "     A1 = 'AZ'"

sql = sql & ",    B1 = 'BZ'"

sql = sql & ",    C1 = 'CZ'"

sql = sql & ",    D1 = 'DZ'"

sql = sql & ",    E1 = 'EZ'"

sql = sql & ",    F1 = 'FZ'"

sql = sql & ",    G1 = 'GZ'"

sql = sql & ",    H1 = 'HZ'"

sql = sql & "WHERE "

sql = sql & "     A1 = 'A7'"

cn.Execute(sql)
Dim 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 With
Do 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

Loop
rs.Close

cn.Close
実行結果


C:\>cscript c:\study\vbscript\chapter004\lesson010.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

AZ,BZ,CZ,DZ,EZ,FZ,GZ,HZ,I7,J7

AX,BX,CX,DX,EX,FX,GX,HX,,

Excel シートの内容を 変更する (削除)

lesson011.vbs



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 With
' 削除

Dim sql

sql = ""

sql = sql & "DELETE FROM "

sql = sql & "    [" + WScript.Arguments(1) + "$] "

sql = sql & "WHERE "

sql = sql & "     A1 = 'A2'"

cn.Execute(sql)
Dim 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 With
Do 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

Loop
rs.Close

cn.Close
実行結果


C:\>cscript c:\study\vbscript\chapter004\lesson011.vbs c:\study\Book1.xls Sheet1

 //nologo

c:\study\vbscript\chapter004\lesson011.vbs(17, 1) Microsoft JET Database Engine:

 この ISAM では、リンク テーブル内のデータを削除することはできません。