VBS - auto-mate/CheatSheetWiki GitHub Wiki

VBS

Arguments or Parameters
Array
Common Classes
Create and write to File
Delete File
Echo
Excel
Executable EXE file
File Collection
File Exists Test
Includes
Path of Executing File
Read File All
Read Text Lines
Shell
Sleep Stop Sleep
Split
SQL Server 2012 Connection
Terminate Process
Write To File
Web Use In IE
Use External Script as Part of Current Script
UTF-8 Files

Arguments

wscript.arguments.count  
wscript.arguments(1)

Array

Version 1

SomeArray = Array("A", "B", "C")
SomeArrayLen = UBound(SomeArray)

Version 2

dim FnameAndBat(2,2)
FnameAndBat(0,0)="A"
FnameAndBat(0,1)="AA"

FnameAndBat(1,0)="B"
FnameAndBat(1,1)="BB"

wscript.echo  FnameAndBat(1,1)

Common Classes

See Excel below for usage. Click to see other common classes

Echo

wscript.echo "Hello World"

Excel

'# "Dim As" NOT Required

Dim xlap
Dim xlwb

Set xlap = CreateObject("Excel.Application")
Set xlwb = xlap.Workbooks.Add

xlwb.ActiveSheet.Range("A1") = 10
xlwb.ActiveSheet.Range("B1") = 10

result = xlwb.ActiveSheet.Range("A1").Value + xlwb.ActiveSheet.Range("B1").Value

MsgBox result, vbOKOnly

Set xlwb = Nothing
Set xlap = Nothing

Executable

To Create an exe file

  1. Find current vbc.exe
  2. Go To vbc.exe path
  3. Execute the command line below

swapping

\SomePathandInFile.vb for the vb script file

and

\SomePathandOutFile.exe for the exe file you want to create

vbc.exe /t:exe /debug+ /optionstrict+ /out:\\SomePathandOutFile.exe \\SomePathandInFile.vb

Sleep

e.g. 2 Seconds

wscript.sleep (2000)

Write To File

Filename="SomeTextFile.txt"
SomeVar="Some Data"

Set filesys = CreateObject("Scripting.FileSystemObject")
Set file = filesys.openTextFile(Filename, 8)
file.writeline (SomeVar & ",")
Set file = Nothing
Set filesys = Nothing

File Exists Test

FileName="SomePathAndFile"

Set fso = CreateObject("Scripting.FileSystemObject")

if not fso.FileExists(FileName) then 
  wscript.echo "# " & FileName & "does not exist!"
  set fso=nothing
  exit sub
end if

set fso=nothing

Read File All

Read Text Stream

FileName="SomePathAndFile"

Set fso = CreateObject("Scripting.FileSystemObject")  
Set fsoTS=fso.openTextFile(FileName)  

FileTxt=fsoTS.readAll()     
wscript.echo "# File Read to var FileTxt"  

Set fsoTS=nothing    
set fso=nothing

Delete File

Set fso = CreateObject("Scripting.FileSystemObject")  
fso.DeleteFile("C:\temp\temp.tmp")  
set fso=nothing

Shell

NB See Wait option 1 wait or 2 continue

Set objShell = WScript.CreateObject("WScript.Shell")
objShell.Run "cmd /c wmic process get name > ""C:\temp\someProc.tmp""",2,true '1=wait

Read Text Lines

Set fso = CreateObject("Scripting.FileSystemObject")    
Set f = fso.GetFile("C:\temp\temp.tmp")  
Set ts = F.OpenAsTextStream(1,-2)  

'## look at each line for "Test String"  
do while ts.atendofStream=false  
  checkStr=TS.ReadLine  
  if(instr(checkStr,"Test String")>0) then     
    TestStringFound=True   
    exit do  
  else 
    TestStringFound=false   
  end if  
loop  

ts.close  

set ts = nothing  
set f = nothing  
set fso = nothing

Terminate Process

objShell.Run "wmic process where name=""process name"" call terminate"

Split

Set fso = CreateObject("Scripting.FileSystemObject")
set file =fso.getfile(SourceFile)
Set fsoTS=fso.openTextFile(SourceFile)     
  
'#Read Input File
FileTxt=fsoTS.readAll()    


'Split file at line end
'======================
newout=split(FileTxt,vbnewline) 

for n=1 to ubound(newout)

  'Split line at comma ","
  '========================
  lineout=split(newout(n),",") 

  for p=1 to ubound(lineout)
    wscript.echo lineout(p)
  next

next



Set fsoTS = nothing
set file = nothing
Set fso = nothing

Create and write to File

Set fso = CreateObject("Scripting.FileSystemObject")  
Set OutFolder=fso.getfolder(OutFolderName)  
    OutFolder.CreateTextFile(OutFileName)  

Set OutFile=fso.getfile(OutFileFullName)  
Set fsoOUT=fso.openTextFile(OutFileFullName,8)   

  fsoOut.write "Text Or Var" & vbnewline  
  fsoOut.close  

set fsoOut = nothing  
Set OutFile = nothing  
Set OutFolder = nothing  
Set fso = nothing

File Collection

Set fso = CreateObject("Scripting.FileSystemObject")
set WrkFolder=fso.getfolder(FolderName)

For each foundFile in  wrkfolder.files        
    wscript.echo  foundFile.name 
next

Set WrkFolder = nothing  
Set fso = nothing

Use External Script as Part of Current Script

Execute StringVar

FileName="SomePathAndFile"

Set fso = CreateObject("Scripting.FileSystemObject")  
Set fsoTS=fso.openTextFile(FileName)  

ScriptText=fsoTS.readAll()     

'// Run the Script copied to "ScriptText" from "SomePathAndFile"
'// ============================================================
Execute ScriptText

Set fsoTS=nothing    
set fso=nothing

Sleep Stop

Set objShell = WScript.CreateObject("WScript.Shell")    

Do While True  
  objResult = objShell.sendkeys("{NUMLOCK}{NUMLOCK}")  
  Wscript.Sleep (6000)  
Loop

Web Use In IE

Ensure IE 9 is set as VBScript not supported in later versions

<!doctype html>  
<html>  
<head>   
<meta http-equiv="x-ua-compatible" content="IE=9">      
<script Language="VBScript">  

Sub myButton_OnClick()  

    '// # Filesystem manipulation' #  

    '// # On Error Goto NOT SUPPORTED #  
    On Error Resume Next  
    
    if 1=0 then '// # Unused but has details of how to use Scripting.FileSystemObject #  
        set fileSys = CreateObject("Scripting.FileSystemObject")  

        fileSys.CreateTextFile "C:\temp\tempScript.txt",true '// # true forces overwrite #  

        set file = fileSys.openTextFile("C:\temp\tempScript.txt", 8)  
        file.writeline ("Writing To Local File From WebPage")  
        set file=Nothing  
        set filesys=Nothing  

    end if  


    '// # SHOW DATA IN <P id="PID"> #  
    msgbox(me.document.getElementById("PID").innerHTML)  

    '// # SHOW DATA IN <INPUT name="textP" value="THIS TEXT"> #  
    msgbox(textP.value)  

    '// Using SAP .........  
    If Not IsObject(application) Then  
        Set SapGuiAuto  = GetObject("SAPGUI")  
        Set application = SapGuiAuto.GetScriptingEngine  
    End If  

    If Not IsObject(connection) Then  
        Set connection = application.Children(0)  
    End If  

    If Not IsObject(session) Then  
        Set session    = connection.Children(0)  
    End If    

    If Err.number <> 0 Then   
        MsgBox "Error " & Err.Number & ": " & Err.Description & " Detected"  
        Exit Sub  
    End If        

    '// Check Title is Menu Screen  
    If session.findById("wnd[0]/titl").text <> "MENU TEXT DETAILS" Then  
        MsgBox "SAP Must Be Logged in at the Menu Screen!"  
        Exit Sub       
    End If          

    If connection.Children.count <> 1 Then  
        MsgBox "Please Ensure Only ONE Session Is Open!" & vbNewLine & "SAP Must Be Logged in at the Menu Screen!"  
        Exit Sub        
    End If  

    session.findById("wnd[0]").maximize  

    '// # OPEN A T-CODE #  
    session.findById("wnd[0]/tbar[0]/okcd").text = "A SAP T_CODE"  
    session.findById("wnd[0]/tbar[0]/btn[0]").press  

    Exit Sub      

End Sub  

</script>  
</head>   
<Body>  
<p id="PID">SOME TEST TEXT</p>  
<input  name="textP" type="text" value="OK"></input>       
<button type="button" name="myButton">Run</button>  
</Body>  
</html>

UTF-8 Files

Example Make CSV file from UFF-8 SAP report text file. [Setting TristateTrue on FileSystem Object seems to give incorrect character count for UTF]

    HeaderSample = "|SAP Header"
    linesToSkip = 0 ' lines before forst data row
    dropChrAtParsePos = True
    delimOut = ","
    delimOutRep = " "
    Source = "C:\Users\A_N_Other\Documents\SAP\SAP GUI\someSAP_download.TXT"
        
    Set stream = CreateObject("ADODB.Stream")
    stream.Open
    stream.Type = 1
    stream.LoadFromFile Source
    stream.Type = 2
    stream.Charset = "utf-8"
    
    'GET FROM ARGUMENT
    fieldStart = Split(WScript.Arguments(0),",")        
    fieldLen   = Split(WScript.Arguments(1),",")

    'HARD CODED
    'fieldStart = Array(2, 63, 74, 85, 106, 117, 122, 173, 177, 188, 193, 214, 218, 231, 253)
    'fieldLen   = Array(60, 10, 10, 20, 10, 4, 50, 3, 10, 4, 20, 3, 12, 21, 9)

    Outline = ""

    For i = 1 To linesToSkip
        strChar = stream.ReadText(-2) ' NB -2 = Whole Row
    Next
                
    
    Do Until stream.EOS
        
        fieldData = stream.ReadText(-2) 'NB -2 =  Whole Row
        
        If Not (Mid(fieldData, 1, Len(HeaderSample)) = HeaderSample Or Mid(fieldData, 1, 10) = "|---------" Or Mid(fieldData, 1, 1) <> "|") Then
        
        For j = 0 To UBound(fieldStart)
            If Outline = "" Then
                Outline = Trim(Mid(fieldData, fieldStart(j), fieldLen(j)))
            Else
                Outline = Outline & "," & Trim(Mid(fieldData, fieldStart(j), fieldLen(j)))
            End If
        Next
        WScript.Echo Outline            
        Outline = ""
        
        End If
    Loop

    stream.Close

Includes

Pre load subs and functions as an include by creating a wsf file as follows e.g. Example.wsf

<job id="Example"> 
   <script language="VBScript" src="library01.vbs"/>
   <script language="VBScript" src="library02.vbs"/>
   <script language="VBScript" src="mainScript.vbs"/>
</job>

Run as

cscript Example.wsf

Path of Executing File

WScript.ScriptFullName

'Find Parent Folders with     

Set fso           = CreateObject( "Scripting.FileSystemObject" )
thisFolder        = FSO.GetParentFolderName( WScript.ScriptFullName  )
thisFolderParent1 = FSO.GetParentFolderName( thisFolder  )
thisFolderParent2 = FSO.GetParentFolderName( thisFolderParent1  )

SQL Server 2012 Connection

Set cn = CreateObject( "ADODB.Connection" )

cn.Open "Provider=SQLNCLI11;Server=<SERVER_NAME>;Database=<DATABASE_NAME>;Trusted_Connection=yes;"

Set rs= cn.Execute("select * from <SOME_TABLE>")

Do While Not(rs.EOF)
  WScript.Echo rs(1).Value
  rs.MoveNext
Loop

rs.Close
cn.Close
⚠️ **GitHub.com Fallback** ⚠️