VBS操作Excel数据

■数据（代码运行前）

・文件夹

・各个Sheet页

---

---

■代码「test.vbs」

call beginAuto()

function beginAuto()
    'msgbox "begin"
    
    '---Step1 Create Object
    Dim objExcel
    set objExcel = CreateObject("Excel.Application")
    objExcel.visible = true
    objExcel.DisplayAlerts = false
    
    '---Step2 Open Excel
    Set operateWorkBook = objExcel.WorkBooks.Open("C:\test\VBS\template.xlsx")
    WScript.Sleep 5000
    
    '---Step3 Get Sheet Object
    Set operateWorkSheet1 = operateWorkBook.WorkSheets("result")
    Set operateWorkSheet2 = operateWorkBook.WorkSheets("Data")
    
    '---Step4 Operate The Data
    Dim count
    count = 1
    For i=1 To 30
        If operateWorkSheet2.Range("B" & i).value = "testData" Then
            nowColumn = count + 2
            operateWorkSheet1.Range("C" & nowColumn).value = operateWorkSheet2.Range("B" & i+1).value
            operateWorkSheet1.Range("D" & nowColumn).value = operateWorkSheet2.Range("C" & i+1).value
            operateWorkSheet1.Range("E" & nowColumn).value = operateWorkSheet2.Range("D" & i+1).value
            count = count + 1
        End If
        
    Next
    
    '---Step5 Save And Close Excel
    operateWorkBook.saveAs "C:\test\VBS\result001.xlsx"
    operateWorkBook.save
    
    operateWorkBook.Close
    objExcel.Quit
    
    '---Step6 Release Object
    set operateWorkSheet1 = Nothing
    set operateWorkSheet2 = Nothing
    set operateWorkBook = Nothing
    set objExcel = Nothing
    
    msgbox "end"

end function

 

■结果（代码运行后）

・文件夹

・「result001.xlsx」各个Sheet页

---

■VBS中还可以调用VBA的代码

https://blog.youkuaiyun.com/sxzlc/article/details/105940745

---