Excel Automation - Part 1

Automation Using Excel Macro

Most of us use the Excel for one reason or the other. with Excel we can automate all the manual work of data comparison.

Here is an example of how such a automation can be done.

Requirement.
1. There are two systems with Data1 and Data2
2. Because of some reason Data1 Accounts do not match with Data2 Accounts
3. The data of both the systems are provided in a separate excel sheets
4. Only the Accounts match in both sheets and rest of the data needs to be validated

Follow the below steps to accomplish the above requirement

=>Open a New Excel sheet
=>Copy the data of First System in Sheet "Data1"
=>Copy the data of Second System in Sheet "Data2"
=> Use Alt+F11 to open MS VS
=>Insert New Module
=>Create a new Sub by name Compare

The data I am using has a sample format. And the data does not remain same always, however a little change in the code can satisfy the requirement. The sample data here has the below format

Fig 1: Data Sheets

Fig 2: Data format in Data1 Sheet

Fig 3: Data format in Data2 Sheet

Use the below code (Modify the code as required)

Sub Compare()
    Dim iRowData1, iColumnData1 As Integer
    Dim iRowData2, iColumnData2 As Integer
    Dim MatchFlg As Integer
    iRowData1 = 2
    iColumnData1 = 1
    Sheets("Data1").Select
    While (Cells(iRowData1, iColumnData1) <> "")
    AccountData1 = Cells(iRowData1, iColumnData1)
    SsnData1 = Cells(iRowData1, iColumnData1 + 1)
    Sheets("Data2").Select
    iRowData2 = 2
    iColumnData2 = 1
    MatchFlg = 0
        While (Cells(iRowData2, iColumnData2) <> "")
        AccountData2 = Cells(iRowData2, iColumnData2)
        SsnData2 = Cells(iRowData2, iColumnData2 + 2)
            If AccountData1 = AccountData2 Then
                If SsnData1 = SsnData2 Then
                MatchFlg = 1
                GoTo NextRow
                End If
            End If
        iRowData2 = iRowData2 + 1
        Wend
NextRow:
        Sheets("Data1").Select
        If MatchFlg = 0 Then
        Cells(iRowData1, iColumnData1 + 3) = "No Match Found"
        Else
        Cells(iRowData1, iColumnData1 + 3) = "Match Found"
        End If
        iRowData1 = iRowData1 + 1
    Wend
End Sub


After the Macro run the Data1 Sheet would be having the following data

This code really helped me a lot. For me it saved around three days work and I had to compare around 35K records. The Macro would run for 5 min. 

For more Stay Tuned.....

Batch File Automation - Part 4

Archive Files Using Automation - by Passing Arguments

In the previous post we had a scenario where we had archived files form one folder to Archive folder but the structure of the folder was fixed as we had hard coded the values in the script. Now let us see how we can write a code that can work on any folder structure. 

The best part of any VB Script is it can accept arguments from command line. As the script can accept the arguments from command line we can have a bat file that can call the VB.

The VB script below would accept four parameters and if four parameters not passed it would throw a message as "Not enough Arguments". The four arguments w.r.t to the position of the arguments is 

Arg(0):{DriveName, E:}, 

Arg(1):{PathName, Test_E}, 

Arg(2):{ArchivePath, E:\Test_E\Archive\}, 

Arg(3):{Prefix, Test_E}

These arguments are for the requirement if the source is E:\Test_D and Archive path is E:\Test_D\Archive and Files with prefix as Test_E*.txt would be archived.

strComputer = "."

If WScript.Arguments.Count = 4 Then

Arg_1 = cstr(WScript.Arguments.Item(0))

Arg_2 = cstr(WScript.Arguments.Item(1))

Arg_3 = cstr(WScript.Arguments.Item(2))

Arg_4 = cstr(WScript.Arguments.Item(3))

strName = strDrive & "\" & "Test\"

Else

 Wscript.Echo "Not enough Argumets"

 Wscript.Quit

End If

strDrive = Arg_1

StrPath = Arg_2

desPath = Arg_3

PrefixfName = Arg_4 & "%"

Set objWMIService = GetObject("winmgmts:\\" & strComputer)

Set objF = CreateObject("Scripting.FileSystemObject")

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive = '" & _

strDrive & "' AND Path = '\\" & strPath & "\\' AND FileName Like '"& prefixfName &"'  AND Extension = 'txt'")

For Each objFile In colFiles

objF.CopyFile objFile.Name, (desPath & objFile.Filename & "_" & _

Left(objFile.CreationDate, Len(objFile.CreationDate) - 11) & "." & objFile.Extension)

Next

The above script is saved as ArchiveAny.vbs

The requirement as below

 There are two folders in two different drives 

E:\Test_E and E:\Test_E\Archive\ and files have prefix as Test_E

E:\Test_D and D:\Test_D\Archive\ and files have prefix as Test_D

The bat file has following lines

@echo off

Cscript ArchiveAny.vbs "e:" Test_E "e:\Test_E\Archive\" "E_Test" 

Cscript ArchiveAny.vbs "d:" Test_D "d:\Test_D\Archive\" "D_Test" 

This is really a good way to automate the process of automation if scheduled. We shall discuss on how such a process can be scheduled. 

Stay Tuned....

Batch File Automation Part - 3

Archive Files Using Automation

Let me discuss on a new requirement that was in my project. The requirement is as follows.

1. A particular directory would have a number of text files with different names but they all have a similar prefixes.

2. The number of files are too many which would make manual work tedious and time consuming.

3. The files must be archived to the Archive file and they should be having a time stamp appended in the file name.

4. The appended time stamp should not be the archived date but the file created date. So that this helps in checking the date  which the file was created.

5. Once the files are archived they would be deleted form the source directory.

The requirement is very simple and a VB script would be doing the entire process with few lines in it.

Consider the folder structure E:\Test\ and E:\Test\Archive.

E:\Test\ folder would have 10 files namely Text_1.txt, Text_2.txt, Text_3.txt, Text_4.txt, Text_5.txt, Text_5.txt, Text_7.txt, Text_8.txt, Text_9.txt, Text_10.txt.

All these 10 files in folder E:\Test\ to be archived to E:\Test\Archive. 

Here is a sample VB Script which would do satisfy the above requirement.

strComputer = "." ' Comuter Name

strDrive = "e:" ' Drive Letter

strPath = "Test" 'Directory Name

desName = strDrive & "\" & strPath & "\Archive\" 'Complete path of Target Directory

Set objWMIService = GetObject("winmgmts:\\" & strComputer)

Set objF = CreateObject("Scripting.FileSystemObject")

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive = '" & _

strDrive & "' AND Path = '\\" & strPath & "\\' AND FileName Like 'Test%' AND Extension = 'txt'") ' this would fetch a cursor like in PLSQL with all the matches

For Each objFile In colFiles

objF.CopyFile objFile.Name, (desName & objFile.Filename & "_" & _

Left(objFile.CreationDate, Len(objFile.CreationDate) - 11) & "." & objFile.Extension)  'Move each file and rename it to archived directory

Next

Save the above code snippet in a file by name Archive.vbs and then call the vbs file from bat file. I would call my bat file Archive.bat. This bat file would have following line

@echo off
Start Archive.vbs

The bat file would call the vbs and archive all the files as required.

How ever the above code would be specific only to a specific structure. We wuld be discusing how this can be generalized to any folder structure.

Stay Tuned....... 

Batch File Automation Part-2

As discussed in the previous thread. A Batch file along with VB can used to automate a process.

Let us consider a requirement where you are suppose to place a file in a directory. The directory might have a file already with same name as the one being moved. This is very easy when we do it manually as the OS would through a error saying "file with same name already exists and do you want to replace it"  and we can easily click on yes and continue. But when we automate we should be able to move the file with out any discrepancies.

Let us see how such a automation can be done.

Steps
1. Create a Batch file that would create a New file by name "Move_Me.txt" at C:\Test
2. The batch file would then try to move the file to C:\Test\Moved using a VBS

Fig : Showing the files used in the example 

The details of the files

CreateFile.bat
This file has the following lines to


@Echo off
Echo "I will be Moved" > C:\Test\Move_Me.txt
Echo "File Move_Me.txt has been created. File would be moved now...."
pause
Start MoveFile.vbs


The above piece of code would first create a File Move_Me.txt with test as "I will be Moved".
Then the code would display message on the screen saying "File Move_Me.txt has been created. File would be moved now...." and pause would wait for user input.
Start command would kick start the MoveFile.vbs which would move the Move_Me.txt to the destination folder.

MoveFile.vbs
VB script is as below

FilePath = "C:\Test\"

Filename = "Move_Me.txt"

NewPath = "C:\Test\Moved\"

Set ScriptObj = CreateObject("Scripting.FileSystemObject")

If ScriptObj.FileExists(FilePath & Filename) Then

    If ScriptObj.FileExists(NewPath & Filename) Then

        ScriptObj.DeleteFile (NewPath & Filename)

    End If

ScriptObj.MoveFile (FilePath & Filename), (NewPath & Filename)

    End If

Set ScriptObj = Nothing

The code is hard coded with the values of file path and file name. Not a good programming practice. But for beginner this should be fine. How ever we will discuss on how parameters can be passed with a different example.

Try this out and let us discuss if any better way of doing this.

Stay tunned for upcoming topics.........