VBScript tutorial 6 | While Wend | Do While Loop | Working with Excel

Moving on to the next tutorial in the VBScript Tutorials for Beginners. This VBScript beginner tutorial explains the VBScript While Wend loop and the VBScript Do While loop. It also gives the VBScript code to create an Excel file. Please view the VBScript tutorial 6 or read on... A While Wend statement in VBScript is a conditional statement. It is useful when we don't know the number of times a code block should run. The While Wend loop runs a code block while a condition is true. Let us see VBScript While Wend example.

' VBScript code
Option Explicit
Dim i
' Initialize the counter variable, i
i = 1
Loop1

Sub Loop1
While i<= 5
    MsgBox "The current value of i is " & i, vbInformation, "While loop"
    ' Increment the value of i
    i = i + 1
Wend
End Sub

A Do While statement in VBScript is also a conditional statement. Like the While Wend statement, it is useful when we don't know the number of times a code block should run. The VBScript Do loop has two variations. The first variation of the VBScript Do While Loop checks if a condition is true. If the condition is true, it runs a code block. A VBScript Do While Loop statement example is in the Sub Loop1 below. The second variation of the VBScript Do Loop While runs a code block, and then checks if a condition is true. A VBScript Do Loop While statement example is in the Sub Loop2 below. Even if the condition is false, the Do Loop While statement runs the code block once (because it checks the condition after running the code block).

' VBScript code
Option Explicit
Dim i
Loop1
Loop2

Sub Loop1
i = 1
Do While i<=5
    MsgBox "The current value of i is " & i, vbInformation, "Do While Loop"
    i = i + 1
Loop
End Sub

Sub Loop2
i = 1
Do
    MsgBox "The current value of i is " & i, vbInformation, "Do Loop While"
    i = i + 1
Loop While i <= 5
End Sub

Next, let us see the VBScript code to create an Excel file. This VBScript uses the VBScript With statement that allows us to specify an object. The benefit is that we don't have to repeat that object's name on every line of code between the With statement and the End With statement.

 ' VBScript code
' This script is just an example.
' Important: Write your own script, test it on a non-production machine and use it at your own risk.
Option Explicit
Dim strFile
' Give a folder that exists on our computer.
strFile = "E:\Training\VBScript\Files\" & Date & ".xlsx"
FileDelete
WriteExcelReport

Sub WriteExcelReport
    Dim objExcelApp, objRange
    Set objExcelApp = CreateObject("Excel.Application")
    With objExcelApp
        .Application.Visible = True
        .Workbooks.Add
        .Cells(1, 1).Value = "Report generated on " & Date
        .Cells(1, 1).Font.ColorIndex = 30
        .Cells(2,1).Value = "Orders Placed"
        .Cells(3,1).Value = "Number of Customers"
        .Cells(3,2).Value = 10
        .Cells(3,2).Font.ColorIndex = 50
        .Cells(4,1).Value = "Total Order value"   
        Set objRange = .ActiveCell.EntireColumn
        With objRange
            .Font.Size = 12
            .AutoFit()
        End With
        .Cells(4,2).Value= 10000
        .Cells(4,2).Font.ColorIndex = 50       
        .ActiveWorkbook.SaveAs strFile
        .ActiveWorkbook.Close
        .Application.Quit
    End With
End Sub

Sub FileDelete
    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If objFSO.FileExists(strFile) Then objFSO.DeleteFile(strFile)
End Sub

Want to learn how to create an HTML file using VBScript? Please view my VBScript tutorial 6. Thank you.