Convert number into Indian National Rupees word!

MS Excel is a banker’s delight software; they have every function and formula to carryout day to day task. But if you are in India and you are required to process a check copy with INR in words, the only way of doing it is typing it yourself.

There are articles about creating a UDF to do this using VBA but unfortunately most of them are about USD number system. Below is Excel Add-in that will provide you a function called =ConvertCurrencyToINR() to convert any number in INR words.


Download and Save the .xla file from link, then use Tool-> Add-in in Excel to install the add-in





Formula Syntax;

=ConvertCurrencyToINR(myNumber As Number, check_negetrive as Boolean)

ConvertCurrencyToINR() checks if the number is negative (only if you check_negetrive as True or 1) and then convert the number is INR words and Prefix Negative if it’s a negative value. You can disable the check_negetrive option by provide 0 or False input in formula.


How to use

In Cell A1 enter -15896.36

In cell A2, inter the formula =ConvertCurrencyToINR(A1,1)
In Cell B2 inter the Fomula =ConvertCurrencyToINR(A1, 0)

Excel-add in for "Collecting Data from Multiple Excel Workbooks into a Single Text File using VBA"

This is a Excel Add-in created with cods I had discussed in "Collecting Data from Multiple Excel Workbooks into a Single Text File using VBScript"

Prompting for a password before running a VBA code

The macro below will help you set a custom password for running a macro in workbook. Let’s say you have a run query button on your worksheet and you want to make sure before running the codes behind this query user needs to be authenticated.


Private Sub CommandButton1_Click()

Dim strPass As String ' This sting is password

Dim lCount As Long

For lCount = 1 To 3

strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")

If strPass = vbNullString Then 'Cancelled

sLast.Select

Exit Sub

ElseIf strPass <> "Password" Then 'InCorrect password

MsgBox "Password incorrect", vbCritical, "Failed"

Else 'Correct Password

Exit For

End If

Next lCount

If lCount = 4 Then 'They use up their 3 attempts

MsgBox "The password is wrong, workbook is going close", vbInformation

ActiveWorkbook.Close SaveChanges:=False

Exit Sub

Else

MsgBox "Correct Password", vbOKOnly 'put your codes from hear on

End If

End Sub

Extract the RGB colour of a shape using VBA.

There are times when we want to replicate a Shapes RBG value in some other shape or at least know the Red, Green and Blue components distinctly.

Hear is function that will help us extract the RBG value of shape is any MS Office Application (Word, Excel, and PowerPoint).

The function returns two values, one as Text string and the other one as Hexadecimal value depending on the Boolean type you specify.

Paste the codes as below in any VBA mobule:

Function RBGExtract(myShape As Shape, IsHex As Boolean) As Variant c = myShape.Fill.ForeColor.RGB If IsHex = False Then redComponent = c Mod 256 greenComponent = c \ 256 Mod 256 blueComponent = c \ 65536 Mod 256 RBGExtract = "RGB components: " & redComponent & _ ", " & greenComponent & ", " & blueComponent Else RBGExtract = c End If End Function

Now, its time to test the function that we have created, we will test it in PPT through following subroutine:

Sub Test() With ActivePresentation.Slides(1) MsgBox RBGExtract(.Shapes(1), True), vbInformation, 'To test the Hexadecimal value MsgBox RBGExtract(.Shapes(1), False), vbInformation, 'To test the Text value End With End Sub

Collecting Data from Multiple Excel Workbooks into a Single Text File using VBScript

Introduction: It’s often a very tedious ask to collect data through copy and pate from multiple MS Excel files. There are ways in VBA to automate the process, but unfortunately, some time ill spreadsheet practices like uses of merged cell may trigger cumbersome situations. Using Vbscript and DOS bat file we can automate this process in just few seconds.

Before we start with the cods, I am assuming that all the Excel files are in same folder as in my case it’s in “C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\Xl”
Now, we need to create one more folder for storing CSV files in example below the path is “C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV\”. You need to change both these paths per your requirement.

Step One: Open Notepad paste the codes as below; make sure you change the CSV path per your requirement.

cd C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV copy *.xls all.txt del *.xls

Step Two: Save this file in C drive as ConsolidateCSV.bat

Step Three: Open Notepad again paste codes as below; make sure you replace the CSV and Xl path with the folder which you have created to store the CSV file and Folder where your MS Excel files are located.

strPath = "C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\Xl" 'Path of Excel File, you may use standard or special folder names as per your need csvpath = "C:\Documents and Settings\ritwik.shukla\Desktop\Excel Training\CSV\" 'Path of CSV File Set objExcel = CreateObject("Excel.Application") 'Ceating a MS Excel Object instance Set WSHShell = CreateObject("Wscript.Shell") 'Ceating a Windows Shell object to run the bat file objExcel.Visible = False 'Better to keep Excel hidden as it might eat up some more memory objExcel.DisplayAlerts = False 'switch off the Display Alerts in Excel Set objFso = CreateObject("Scripting.FileSystemObject") Set objFolder = objFso.GetFolder (strPath) For Each objFile In objFolder.Files 'Looping through all the Excel files in folder If objFso.GetExtensionName (objFile.Path) = "xls" Then Set objWorkbook = objExcel.Workbooks.Open(objFile.Path) objWorkbook.SaveAs csvpath & objFile.name, 6, True o bjWorkbook.Close 'MsgBox objFile.name & " Saved", 64,"Example" 'You may uncheck this option if you want to notify the user after each file is saved End If Next objExcel.DisplayAlerts = True 'Switch on the Excel Display Alerts WSHShell.Run ("C:\ConsolidateCSV.bat") ' Run the ConsolidateCSV.bat file Set objExcel = Nothing Set WSHShell = Nothing 'Free the ojects MsgBox "All.txt is created!",64,"Example" 'Noify the user about all.txt file that has all the data

Step Four: Save the File with .vbs extension.

If you have performed every step appropriately, double clicking the .VBS file run a script to consolidate data from first worksheet of all workbooks into a text file with name all.txt.

Ps: In you have comments or a better way of doing it please post it.