Enable and Disable of Excel Error Message

These alerts can be turned off by using this VBA-code:

Application.DisplayAlerts = False

After a routine you must turn on alert messages:

Application.DisplayAlerts = True

Delete entire empty rolls

Function delRolls ()

On Error Resume Next
worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Function

Write output file

Create a filesystem Object

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

 

 

Create a output file’s path

 

Set outFile = objFSO.CreateTextFile("i:\ASQ\import.bat", True)

 

 

Input the Value that you would like to input

 

outFile.WriteLine "Here you are writing it in the file"

Set an input Box

Input box is important to VBS as it can provide different input to achieve result.

 

 

myInputBox = Inputbox("Here will show up the detail of the description","This is Input Box")

 

 

 

 

 

 

Pause / Wait

Using sleep function to pause your program



Wscript.Echo "Time Start"

'here is the most important line
WScript.sleep 6000


Wscript.Echo "6 Second Gone"

Read Registry from Local or remote Machine

'to read HKEY Local Machine registry

const HKEY_LOCAL_MACHINE = &H80000002

'prompt for the machine name to input for scanning, the in front part is the prompt and behind one is for Title

strComputer = ""

Set oReg=GetObject( _

   "winmgmts:{impersonationLevel=impersonate}!\\" &_

    strComputer & "\root\default:StdRegProv")

'registry path

strKeyPath = "software\citi\agent"

'registry Value Name

strValueName = "redirect"

'value that it is going to read

oReg.getstringvalue _

   HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

'show with msgbox for the result

MsgBox (strComputer &" CITI agent Status is " & strValue)

To show "POP UP" to inform user

WScript.Echo("This is a pop up")

How many Cell is filled in Row

Having the ability to check the length of Row will give you the option to run script for the list of items in your EXCEL spreadsheet

Here we are checking the current Spreadsheet’s Row A length and declared “LastRow” for future reference

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

Wait command

Sometime we want the script to run a little bit slower than the time we executed them.

Here is the command to wait for 6 sec

Wscript.Echo "Time Start"

WScript.sleep 6000

Wscript.Echo "6 Second Gone"

Check file exist

Here is how to check for file exist while we copy to or from machine’s data

Example below show the checking of CMD.exe in Windows\system32 folder

'Set Object

Set fso = CreateObject("Scripting.FileSystemObject")

'Create Condition

If (fso.FileExists("C:\windows\system32\cmd.exe")) Then

'Alert User

WScript.Echo("File exists!")

WScript.Quit()

Else

'Alert User

WScript.Echo("File does not exist!")

End If

'Exit Script

WScript.Quit()

Check folder Existence

Checking of folder existence to minimise extra step in your script.

Below is an example to check for C:\Windows folder

set objDRV = CreateObject("Scripting.FileSystemObject")

If  objDRV.FolderExists("c:\windows") Then

(DO SOMETHING)

else

(DO ANOTHER THING)

End if 

Folder Creation

Here is how you create a folder

Just put in the folder destination on the 2^nd line and the name of the folder follow by “\”

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CreateFolder("c:\(FOLDER NAME)\")

Done

Run CMD in VBS

create a blank text file and put in the code below

Set objShell = CreateObject("WScript.Shell")

objShell.run("ping 127.0.0.1")

Save the file as RunCMD.vbs

Create Input Box

create a blank text file and put in the code below

myInputBox = Inputbox("Here will show up the detail of the description","This is Input Box")

Save the file as InputBox.vbs

Saying Hello

This is the most basic thing we can do with VBS.

Just create a blank text file and put in the code below

WScript.Echo("HELLO! This is VBS")

Save the file as Hello.vbs

Double click and you will get a message on your screen.