XML File Parsing

XML File Parsing - Generic - VB code

Objective : Parse the XML to get all the nodes of any XML file .

First get the root node of the XML using oXml.documentElement.
Then check whether that root node has child nodes and do it repeatedly for all the nodes until its the last node.
The above can be achieved easily using recursion.

Belwo code can be used to parse any XML - To get all the nodes of XML

PS: The code is in VB but the same approach can be used for other languages and can be customized.

**************Code******************************

' Declare the variables
Dim oXml As DOMDocument
Dim oRoot As IXMLDOMNode
Dim Childnode As IXMLDOMNode

Sub ParseXml()

'Load the xml File
 Set oXml = CreateObject("Msxml2.DOMDocument")
 oXml.async = True
 xmlFile = ReadXMLfile(AnyFileLocation)
 oXml.loadXML xmlFile

'Set the root of the XML
Set oRoot = oXml.documentElement

'Call Recursive function and pass the root node of the XML
Call getChildNodes(oRoot)

End Sub

'Recursive function
Function getChildNodes(curNode As IXMLDOMNode)

'Çheck if the node has child nodes
If curNode.hasChildNodes = True Then
   For Each Childnode In curNode.childNodes
  
    'If yes then again call the recursive function

   Call getChildNodes(Childnode)
   Next
  
Else

   'If not then its doesnt have any more child nodes and we can print the node
   MsgBox curNode.nodeName

End Function

VB Program - To Create a Notepad(.txt) File and Save the data

VB Program - To Create a Notepad(.txt) File and Save the data

Steps:

1.For Creating a text file in a Vb program we use File System Object.
2.CreateObject ("scripting.filesystemobject") - This is  used to create the object of the file.
3.After creating the file object we open the file using opentextfile(location) function.
4.Once the file is opened then writeline is used to write text in the file that has been created above.
5.After writing in to the file ,the file object has to be closed using Close.

Here is a demo to create and then write something in a text file :

The below code will create the Text file(Ia2Tech.txt) and then write the data("Its all about Technology") in the file which will be saved at "D:\Ia2Tech.txt" and then will close it.

Program :

Sub Notepad()

Set objFS = CreateObject("scripting.filesystemobject")
Set txtFile= objFS.opentextfile("D:\Ia2Tech.txt", 8, True)
txtFile.writeline "Its all about Technology"
txtFile.Close

End Sub

Note : If the File is already there at a specified location then the data will be appended to the data already present in the file.

VB Program - To check if a number is Numeric or not

VB Program - To check if a number is Numeric or not

Program:

-Use of IsNumeric()

Sub checkNumeric()

Dim varNum

varNum = InputBox("Enter a Number")

If IsNumeric(varNum) = "True" Then
MsgBox varNum + " is a Numeric Value"
Else
MsgBox varNum + " is not a Numeric Value"
End If

End Sub

VB Program - To Reverse the n Digit Number entered by a user

VB Program - To Reverse the n Digit Number entered by a user

Program :

Here n= 5,this can be modified in the below code.

Sub ReverseNumber()

Dim varNum
Dim varRev

varNum = InputBox("Please Enter a number")

If Len(varNum) <> 5 Then
MsgBox varNum + " is not a 5 digit number"
Else
varRev = varRev * 10 + varNum Mod 10
varNum = varNum / 10
varNum = Left(varNum, 4)
varRev = varRev * 10 + varNum Mod 10
varNum = varNum / 10
varNum = Left(varNum, 3)
varRev = varRev * 10 + varNum Mod 10
varNum = varNum / 10
varNum = Left(varNum, 2)
varRev = varRev * 10 + varNum Mod 10
varNum = varNum / 10
varNum = Left(varNum, 1)
varRev = varRev * 10 + varNum Mod 10
MsgBox "Reverse Order of the entered number is " & varRev
End If

End Sub

VB Program - To Calculate the Simple Interest

VB Program - To Calculate the Simple Interest

Program :

Sub SimpleInterest()

Dim varPA
Dim varT
Dim varROI
Dim varSI

varPA = InputBox("Enter Principle Amount")
varT = InputBox("Enter Time(Yrs)")
varROI = InputBox("Enter Rate of Interest")
varSI = (varPA * varT * varROI) / 100

MsgBox "Simple Interest is " + CStr(varSI)
'Cstr()- Function converts the value to String

End Sub

VB Program - To find out whether the number is Even number or Odd number?

VB Program - To find out whether the  number is Even number or Odd number?

Program: 

Sub EvenOdd()

Dim varNum
varNum = InputBox("Enter a number")
If varNum Mod 2 = 0 Then
MsgBox varNum + "  is a even number"

Else
MsgBox varNum + "  is a odd number"

End If

VB Program - To find out whether the given year is a leap year or not?

VB Program - To find out whether the given year is a leap year or not?

Program : 

Sub LeapYear()

Dim varYear
varYear = InputBox("Enter the Year")
If varYear Mod 4 = 0 Then
MsgBox varYear + " is a Leap year"
Else
MsgBox varYear + " is not a Leap year"
End If


End Sub

VB Programming Basic Concepts - If-Else-If Condition

VB Programming Basic Concepts - If-Else-If Condition

 Below are the ways to write If statements in a VB Program :

1. Simple IF - 

If [condition] Then

Code 

......

End If 

2. IF Else -

If [condition] Then

Code

......

Else

Code

......

End If

3. Else If -

If [condition] Then

Code

......

Else If [condition] Then

Code

......

End If

VB Programming Basic Concepts - Declaring Variables

VB Programming Basic Concepts -  Declaring Variables

 There are mainly two types in VB:

 1. Object Data Type :

These variables are declared as objects. These can be assigned a value through the Set statement.

Ex : Dim varName As Type

If the type is declared as String then only string value can be assigned to it else it will throw a compile time error.

Type may be any VB object like Integer, Long, String, Date, Double, Currency, Boolean, Byte etc.

2. Variant Data Type :

These are the variables whose data types can be changed easily by assigning different values.

Ex : Dim varName

                 

varName=“IA2Tech”       // Here varName is a string

varName=111            // Here varName is an integer

VB Program - Formatting a Excel Report -Various Formatting Techniques

VB Program - Formatting a excel report -Various Formatting Techniques.

Program :

The below code has various paramters that can be used to format the excel repot(column width,wrap text,printing page setup) etc.
Kindly refer to comments mentioned in the code to understand better.

Sub Formatting()

   ' i is the no. of rows in the sheet
   Dim i As Integer
    i = 57
 
   ' we can modify the width as below
   'formatting starts for column A

   Columns("A:A").ColumnWidth = 5.43

    ' formatting the rows through the loop

    Range("A2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     End With
    Selection.Offset(1, 0).Select
   Loop

  
    Columns("B:B").ColumnWidth = 8.14
    Range("B2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

      
       Columns("C:C").ColumnWidth = 9.29
       Range("C2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
          End With
    Selection.Offset(1, 0).Select
   Loop
   
     
   
    Columns("D:D").ColumnWidth = 37.71
   Range("D2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
       .Orientation = 0
       .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Offset(1, 0).Select
   Loop
  

      Columns("E:E").ColumnWidth = 46.86
     Range("E2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
      .Orientation = 0
       .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Offset(1, 0).Select
   Loop
  

   Columns("F:F").ColumnWidth = 15
    Range("F2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  
  
  
   Columns("G:G").ColumnWidth = 11.29
    Range("G2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

     Columns("H:H").ColumnWidth = 29.71
     Range("H2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

  
   Columns("I:I").ColumnWidth = 10.43
    Range("I2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

    Range("J2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

   Columns("K:K").ColumnWidth = 7.57
    Range("K2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop
  

     Range("L2").Select
    Do Until Selection.Row = i
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
       .WrapText = True
     
    End With
    Selection.Offset(1, 0).Select
   Loop

  
  ' Changing the Page setup For priniting purposes

 
   With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
               
        .LeftMargin = Application.InchesToPoints(0.15748031496063)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.236220472440945)
        .BottomMargin = Application.InchesToPoints(0.15748031496063)
        .HeaderMargin = Application.InchesToPoints(0.15748031496063)
        .FooterMargin = Application.InchesToPoints(0.275590551181102)
        .Orientation = xlLandscape
        .Draft = False
        .Zoom = 68

    End With

    End Sub


Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

Macro - Definition

What is a Macro?

A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module which an be executed whenever there is a need to perform a particular task.  

Few points about Macros :- 

• Macros are written in VBA. 
• VBA (Visual basic for Applications) is a subset of VB Programming Language.
• It can automate all the repetitive work which is done manually in excel or any other application.
• It is useful when we have loads of data to play with in excel.
• It can even automate Web Pages,Web Services,Microsoft Outlook,HP Quality Center,Microsoft Word etc.
• It is a useful alternative of other complex automation tools like QTP,Selenium etc.

VB Program - To Change the Color of the cell based on the values.

VB Program - To Change the Color of the cell based on the values.
 
Program:

The below code change the color of the cell based on specific values as defined in the code.


Sub Color()

  For i = 1 To 100
    Cell1 = "A" + CStr(i)
    val1 = Range(Cell1).Value
    If val1 = "1" Then
            Range(Cell1).Select
            Selection.Interior.ColorIndex = 43
    ElseIf val1 = "no progress" Then
            Range(Cell1).Select
            Selection.Interior.ColorIndex = 15
    ElseIf val1 = "0" Then
            Range(Cell1).Select
            Selection.Interior.ColorIndex = 3
    Else
            Range(Cell1).Select
            Selection.Interior.ColorIndex = 35
    End If
  Next
     
End Sub

Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

VB Program - To Delete the entire row if a specific cell value in a column is blank.

VB Program - To Delete the row if a specific cell value in a column is blank.
Program:

The below code will delete the entire row if Value in Column G is blank.

Sub delRow()

 For i = 1 To 100
    Cell1 = "G" + CStr(i)
    val1 = Range(Cell1).Value
    If val1 = "" Then
          Range(Cell1).Select
          Selection.EntireRow.Delete
    End If
 Next

End Sub

Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

VB Program - To Delete contents of a column

VB Program - To Delete contents of a column

Program:


'Use of Do Until
'The below code will delete the contents of column A.

Sub Delcontent()

    i = 1
   Cell1 = "A" + CStr(i)
   val1 = Range(Cell1).Value
   Do Until val1 = ""
       Range(Cell1).Select
       Selection.ClearContents
       i = i + 1
      Cell1 = "A" + CStr(i)
      val1 = Range(Cell1).Value
   Loop




Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

VB Program - To Reverse a String

Visual Basic (VB) Program - To Reverse a String

- Use of StrReverse() Function

Program:

Sub ReverseString()

Dim s As String
s = InputBox("Enter a string")
s = StrReverse(s)
MsgBox s

End Sub

Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)

VB Program - Palindrome

Visual Basic (VB) program to check whether a string is Palindrome or not.

- Use of StrReverse() Function

Program :

Sub Palindrome()

Dim s As String
s = InputBox("Enter a string")
If s = StrReverse(s) Then
MsgBox s & (" is a palindrome")
Else
MsgBox s & (" is not a palindrome")
End If

End Sub

Note: Copy the code and run in Microsoft Visual Basic Editor.(Microsoft Excel)