VB script 入门

这里简单介绍下vb script 脚本的基本语法：

后面可以参考这个例子：

Sub 创建任务脚本_Click()
Dim Line As String
  Dim ColumnStr As String
  Dim Substr As String
  Dim Str, Val, n
  
  
  max_line = 20000
  
  Set fs = CreateObject("Scripting.FileSystemObject")
    sFilename = ThisWorkbook.Path + "\..\..\Sources\sql\output.sql"
  Set fhandle = fs.CreateTextFile(sFilename, True)
  
  i = 1
  For i = 1 To 200  '显示修改历史记录预设200行
    If (Cells(i, 1) = "修改历史") And UCase(Cells(i, 2)) = "BEGIN" Then
        j = i + 1
        fhandle.WriteLine ("-- 修改历史：")
        While Not (Cells(j, 1) = "修改历史" And UCase(Cells(j, 2)) = "END")
            fhandle.WriteLine ("-- " & Cells(j, 1) & " -" & Cells(j, 2) & " -" & Cells(j, 3) & " -" & Cells(j, 4))
            j = j + 1
        Wend
        Exit For
    End If
  Next

  
  '1.定位到第一条数据所在行数
  menu_start = 0
  For i = 1 To max_line
    If Cells(i, 2) = "交易市场" Then
      menu_start = i + 1
      Exit For
    End If
  Next
  
  If menu_start = 0 Then
    Exit Sub
  End If

 
  fhandle.WriteLine ("                                                       ")
  fhandle.WriteLine ("                                                       ")
    
  
    '3.生成任务脚本
    For i = menu_start To max_line
      If Cells(i, 2) = "" Then
         Exit For
      End If
        fhandle.WriteLine ("--" & Cells(i, 15) & " start")
        fhandle.WriteLine ("declare")
        fhandle.WriteLine ("    iCountNum number;")
        fhandle.WriteLine ("begin")
        fhandle.WriteLine ("    iCountNum := 0;")
        
        Line = "    select count(*) into iCountNum from TCONVERTSET where vc_module = '" & Cells(i, 7) & "' and vc_function = '" & Cells(i, 8) & "';"
        fhandle.WriteLine (Line)
        fhandle.WriteLine ("    if (iCountNum = 0) then")
       
        Line = "        insert into TCONVERTSET (L_SERIAL_NO, L_OPERATOR_NO, C_MARKET_NO, C_CONVERT_TYPE, L_THREAD_COUNT, L_BEGIN_TIME, L_END_TIME, VC_MODULE, VC_FUNCTION, C_CONVERT_STATUS, L_PRIORITY_LEVEL, VC_REMARKS, VC_INIT_FUNCTION, C_RUN_ONCE, C_VERSION )"
        fhandle.WriteLine (Line)
        Line = "        values ( SeqConvertSetNo.nextval, 1000,'" & Cells(i, 2) & "', '" & Cells(i, 3) & "', " & Cells(i, 4) & ", " & Cells(i, 5) & ", " & Cells(i, 6) & ", '" & Cells(i, 7) & "', '" & Cells(i, 8) & "', '" & Cells(i, 9) & "', " & Cells(i, 10) & ", '" & Cells(i, 11) & "', '" & Cells(i, 12) & "', '" & Cells(i, 13) & "', '" & Cells(i, 14) & "');"
        fhandle.WriteLine (Line)
        
        fhandle.WriteLine ("    end if; ")
        fhandle.WriteLine ("end; ")
        fhandle.WriteLine ("/ ")
        fhandle.WriteLine ("")
        
    ' End FOR
    Next
  
   fhandle.WriteLine ("commit; ")
   fhandle.Close

   MsgBox "任务脚本创建成功！" & Chr(10) & "文件名:" & sFilename
End Sub