Excel Macro to read data from text file

Sub readFile()
'Excel macro to read a text file

Dim myfso As Scripting.FileSystemObject
Dim stream As Scripting.TextStream

Set myfso = New Scripting.FileSystemObject

'open text file in read mode.
Set stream = myfso.OpenTextFile("c:\tempfile.txt", ForReading, True)

MsgBox stream.ReadAll

End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to create text file

Sub createFile()
'Please note that to perform file related functions in your project, you will have to add reference of microsoft script runtime

'Here is the excel macro code to create a text file
Dim myfso As Scripting.FileSystemObject
Dim stream As Scripting.TextStream
Set myfso = New Scripting.FileSystemObject
Set stream = myfso.CreateTextFile("c:\tempfile.txt", True)
'Create text file at given path
stream.Write ("This is file creation demo. if file exists, it will be overwritten")
'Write data into opened file's stream
stream.Close
Set stream = Nothing
Set myfso = Nothing

'This is how You can create a file in excel macro

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create and use dictionary object in excel macro

Sub createDictionary()
'Please note that to use dictionary in your project, you will have to add reference of microsoft script runtime

Dim d1 As Scripting.Dictionary
'Dictionary object allows you to store the elements in dynamic way using keys.
'Unlike arrays where values are stored using index, dictionary uses keys to store values

'To add key in dictionary, use below code
Set d1 = New Scripting.Dictionary
d1.Add "sachin", 41
d1.Add "dhoni", 32

'To get the value at particular key
MsgBox d1.Item("sachin")

'to remove key from dictionary
d1.Remove "dhoni"

'To get all keys from the dictionary, use below code
keyArray = d1.Keys


'to check if given key exists in the dictionary
If d1.Exists("sachin") Then
    MsgBox "Key sachin exists"
Else
    MsgBox "Key sachin does not exist"
End If

'To remove all keys from dictionary, use below code
d1.RemoveAll


'this is how you can use dictionary object in excel macro
'You can store the values from 2 columns as key value pair in dictionary
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create and use array in excel macro

Sub createArray()
'How to create and use array in excel macro
'Arrays are used to store large number of elements in sequence

'There are 2 types of Arrays in Excel Macro
'1.Fixed Size Array
'2.Dynamic Array

'**************************Fixed Size***********************************
Dim a(11)
a(0) = "sagar"
a(1) = 2
MsgBox a(1) & a(0)

'Or you can also create array like this
a = Array("sagar", "amol", "sachin", 33, 44, 55)
'**************************Dynamic Array***********************************
Dim b()

ReDim Preserve b(3)
'Above statement will resize the array to size 4
'Preserve statement will keep the existing values in the array intact
ReDim Preserve b(6)
'Again resize array to size of 7

'This is how we can array to store any number of values.
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

How to create modules in Excel Macro

Modules are used to store the related procedures and functions

1.Modules contain the functions that provide unique functionality
2.We can group similar functions and procedures in separate module
3.Class module contains properties and methods of that particular class
4.For example - we can create a student class which can be used to store/retrieve student properties like student name, student id, student address etc.


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the data type of cell value

Sub conversion()

'We can convert the given variable in any desired format
' For example we we have a string as 09-jan-2013, we can convert it into date as mentioned below

date1 = CDate("09-jan-2013")
MsgBox "Data type of date1 is " & TypeName(date1)

'If the input to cdate function is not a valid date, type mismatch error will occur
'Similarly you can convert the string into integer, float ect
MsgBox CInt("243.6 ")

'To convert the number into string, you can use cstr function
no = 2344
MsgBox CStr(no)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to split the cell value or string

Sub breakString()

'Excel macro to split the string with given delemiter or seperator

string1 = "sachin , suresh, Yuvraj, dhoni, Ishant, shikhar"

arrString = Split(string1, ",", 2)
'Split function will break the given string using seperator
'in above code we have split string1 using ","
'Last parameter indicates how many parts should be made of the given string
'Last parameter is optional and if ommitted string will be broken into maximum number of parts


For i = 0 To UBound(arrString)
    MsgBox arrString(i)
Next

'Output - "sachin" and "suresh, Yuvraj, dhoni, Ishant, shikhar"
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to extract the part of cell value or string

Sub extractString()
'Excel macro to get the part of string
'you can use below functions to extract the part of the string

MsgBox Left("Lalu Prasad", 4) 'get 4 characters from left side of string
'output - Lalu
'Left function will get the specified number of characters from left side of the string

MsgBox Right("Lalu Prasad", 6) 'get 6 characters from the right side of the string
'Right function will get the specified number of characters from right side of the string
'Output - Prasad

MsgBox Mid("Lalu Prasad", 6, 6) 'get 6 characters starting from index 6
'Mid function is used to get the fixed length portion of string starting from given position or index
'Output - Prasad
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to remove the spaces from the cell values

Sub removeSpaces()
'Excel macro to remove the leading and trailing spaces from the string or cell value.
'In Excel macro we can use trim functions to remove leading and trailing spaces from given string

MsgBox LTrim("  Kapil Sharma ") ' output "Kapil Sharma "
MsgBox RTrim("  Kapil Sharma ") ' output " Kapil Sharma"
MsgBox Trim("  Kapil Sharma ")  'output "Kapil Sharma"

' As displayed in above code we have 3 functions to remove the spaces from the string
'ltrim - removes the spaces from left side of the string
'rtrim - removes the spaces from right side of the string
'trim - removes the spaces from both left and right side of the string

'Please note that we can not remove the internal spaces from the string
'To remove the spaces from internal part from the string, you can use below macro code

MsgBox Replace(" Kapil Sharma ", " ", "")

'Output of above code will print "KapilSharma"
'Note that all spaces from the string are removed


End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to find the sub string

Sub findSubString()
'Excel macro to check if one string exists in another string

'We can use below macro code in excel to check if one string exists in another string
'if substring is found, InStr function will return the position where the match is found

x = InStr(1, "Salman Khan", "khan", vbTextCompare)
MsgBox x

'Above macro code will print 8 as "khan" appears in "salman khan" at 8th position
'Last parameter in the function instr is used to specify whether comparison is case-sensitive or not.
'vbtextcompare = Case insensitive comparison
'vbBinaryCompare  = case sensitive comparison

End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the string text to upper case

Sub uppercase()

'Excel macro to convert the string to upper case
'here is the macro code that can be used to convert the string to upper case
MsgBox UCase("priynka chopra")

'Above excel macro code will print PRIYANKA CHOPRA
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the text in lower case

Sub lowercase()

'Excel macro to convert the string to lower case
'here is the macro code that can be used to convert the string to lower case
MsgBox LCase("MS DHONI")

'Above excel macro code will print ms dhoni
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to convert the text in lower case

Sub lowercase()

'Excel macro to convert the string to lower case
'here is the macro code that can be used to convert the string to lower case
MsgBox LCase("MS DHONI")

'Above excel macro code will print ms dhoni
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel macro to get the month name of given date

Sub getMonthName()

'Print current month name
MsgBox MonthName(Month(Now))

'To print month name of any given date you can use below macro code
MsgBox MonthName(Month("09-jan-1988"))

End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel macro to get the day name of given date

Sub getDayName()
'Print today's day name
MsgBox WeekdayName(Weekday(Now))
'To print day name of any given date you can use below macro code
MsgBox WeekdayName(Weekday("09-jan-1988"))
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the date difference

Sub getDateDiff()
'Excel macro to get the date difference in term of day, week, year

'Suppose you want to find the total number of days between date1 = "09-jan-1987" and date2="12-jan-2012"
date1 = "09-jan-1987"
date2 = "12-jan-2012"
MsgBox "Total days between " & date1 & " and " & date2 & " is -> " & DateDiff("d", date1, date2)
MsgBox "Total weeks between " & date1 & " and " & date2 & " is -> " & DateDiff("ww", date1, date2)
MsgBox "Total years between " & date1 & " and " & date2 & " is -> " & DateDiff("yyyy", date1, date2)

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the stock data from yahoo finance

Below procedure can be used to get the stock information from yahoo finance.

Sub getYahooFinance()
'Below script will download the quotes for given symbol from yahoo finance
'more info at -> http://www.jarloo.com/yahoo_finance/

'qURL = "http://finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG+MSFT&f=nv"
'http://finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG+MSFT&f=nv
qURL = "http://ichart.finance.yahoo.com/table.csv?s=rcom.BO&ignore=.csv"

With Sheet1.QueryTables.Add(Connection:="URL;" & qURL, Destination:=Sheet1.Range("A1"))

      .BackgroundQuery = True
      .TablesOnlyFromHTML = False
      .Refresh BackgroundQuery:=False
      .SaveData = True
End With
   
   
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to process xml file

Sub processXML()

'online ref - http://msdn.microsoft.com/en-us/library/aa468547.aspx
Set xDoc = CreateObject("MSXML2.DOMDocument")
'to load the xml string
'xDoc.LoadXML ("<book><str>hello</str></book>")
'to load xml from file, use below syntax
xDoc.Load ("c:\abc.xml")
'Display all elements and their values
Call DisplayNode(xDoc.ChildNodes, 0)
MsgBox str1
'We can also use xpath to get the collection of nodes
xDoc.setProperty "SelectionLanguage", "XPath"
strPath = "//book"
Set nodelist = xDoc.DocumentElement.SelectNodes(strPath)
MsgBox "No of Nodes Found by using xpath is -> " & nodelist.Length & " Node"
Set xDoc = Nothing
End Sub


Public Sub DisplayNode(ByRef Nodes, ByVal Indent)
'Recursive function to traverse all the nodes inside xml file/ String
Dim xNode
Indent = Indent + 2
For Each xNode In Nodes
If xNode.NodeType = NODE_TEXT Then
str1 = str1 & " " & xNode.ParentNode.nodeName & ":" & xNode.NodeValue
End If
If xNode.HasChildNodes Then
DisplayNode xNode.ChildNodes, Indent
End If
Next
End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get stock finance data from Google NSE BSE

'Below program will get stock/finance information in xml format from google finance.
'You can give the stocks from NSE and BSE as well.

'Specify the url to connect to
URL = "http://www.google.com/ig/api?stock=rcom"
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Call objHTTP.Open("GET", URL, False)
objHTTP.Send

'The response comes in the form of xml stream.
MsgBox (objHTTP.ResponseText)


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to display a dialog box .

Sub messageBox()
'We can display message box / dialog box with yes/no buttons in excel macro using msgbox function

ret = MsgBox("Yes - No Buttons", vbYesNoCancel, "MessageBox Macro")
'Please note that second parameter in the function msgbox decided which buttons to display on the dialog 'box.

If ret = 6 Then
   MsgBox "You cliked on Yes"
ElseIf ret = 7 Then
   MsgBox "You cliked on No"
Else
  'ret = 2
  MsgBox "You cliked on Cancel"
End If

End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the current system time

Call getSystemTime()

Sub getSystemTime()

'below program will display current system time in excel macro

MsgBox Time

'To  display time stamp, you can use below code excel macro
MsgBox Now

'To extract the time part from the timestamp, you can use below code

MsgBox TimeValue(Now)
'Above code will print only the time part i.e current time
End Sub

What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get the year from given date

Sub getYear()
'To get the year of given date, you can use below function in excel macro.

MsgBox Year(Now)
'Above code will print current year

MsgBox Year("09-jan-1986")
'Above code will print 1986

End Sub




What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro. I will try to reply as soon as possible. I value your opinion on Excel Macros.

Excel Macro to get yesterday's date

Call getYesterday()


Sub getYesterday()
'We can use dateadd function to find yesterday's date in excel macro.

x = DateAdd("d", -1, Now)
MsgBox DateValue(x)
'In above function we have used dateadd function which takes 3 parameters.
'First parameter is used to specify the interval type like days, weeks, months, years etc.
'Second parameter is actual interval. It can be positive or negative
'Third parameter is any valid date.

'Similary We can find out the tommorrow's date using below line

x = DateAdd("d", 1, Now)
MsgBox DateValue(x)


End Sub


What do you think on above excel macro topic? Please provide your input, comments, questions on excel macro.  I will try to reply as soon as possible

Excel Macro to add leading zero to cell value

'Excel Macro to add leading zero to any cell.


Sub addLeadingZero()

'get the value from cell
x = Sheet1.Cells(1, 1)
'add leading zero

x = "'000" & x

'print value with zero
MsgBox x

'store value back in cell
Sheet1.Cells(1, 1) = x

End Sub

List of commonly used Excel functions

I am using Excel Macros since last few years and from that experience I have created the list of commonly used excel functions that we can use.

Formula	Value	Description
=ABS(-11)	11	gets Absolute value
=ADDRESS(1,1)	$B$1	gets the address of cell
=AGGREGATE(4,0,A1:A10)	33	Calculates aggregates
=BIN2DEC(101)	5	converst binary to decimal
=CELL("contents",A1)	33	gets the contents of given cell
=CHAR(65)	A	gets the character for ascii value
=CHOOSE(2,33,44)	44	selects nth value from the given set of values
=CONVERT(23,"m","ft")	75.45931759	Converts the number in different measurement units
=COUNT(A1,B3)	1	counts the number of cells having numbers in it
=COUNTA(A1:A7)	7	counts non empty cells
=COUNTIF(A1:A7,2)	0	counts the cells that meet the condition
=EXACT("sagar","sagar")	TRUE	checks if two string are similar or not
=FACT(5)	120	gets the factorial of the number
=FIND("ten","sachin tendulkar")	8	finds the position of given string in other string
=FIXED(19.434,2)	19.43	rounds the number
=FLOOR(45345.6756,1)	45345	gets the integer number
=CEILING(45345.676,1)	45346	gets the integer number
=NOW()	25-10-2013 17:19	gets current time
=DAY(now())	25	gets current day
=GCD(6,8)	2	find GCD
=HLOOKUP(2,A1:A10,3)	#N/A	Horizontal Lookup
=VLOOKUP(2,A1:A10,3)	#N/A	Vertical Lookup
=HOUR(NOW())	17	gets current hour
=HYPERLINK("http://www.google.com","goog")	goog	creates the hyperlink in the cell
=IF(12>10,"12 is big","12 not big")	12 is big	executes conditional statements
=INT(323.55)	323	gets the integer part of the number
=ISBLANK(a55)	TRUE	checks if the given cell value is blank
=ISEVEN(22)	TRUE	checks if the given value is even
=ISNONTEXT(a55)	TRUE	checks if the givenvalue is non-text
=ISNUMBER(22.44)	TRUE	checks if the given value is number
=ISODD(34)	FALSE	checks if the given value is odd
=ISTEXT("dsdsd")	TRUE	checks if the givenvalue is a text
=LARGE(A1:A44,1)	56	gets the nth largest value from the given set
=LCM(6,8)	24	gets the Least Common Multiple
=LEFT("sachin tendulkar",6)	sachin	gets the n characters from the left side of the string
=LEN("sachin")	6	gets the length of the given value
=LOWER("Sagar Salunke")	sagar salunke	converts the value into lower case
=MATCH(33,A1:A77,0)	1	searches the value in given range
=MAX(A1:A44)	56	finds the max value from given range
=Min(A1:A44)	3	finds the min value from given range
=MONTH(now())	10	gets the current month
=MOD(44,3)	2	gets the remainder of division
=MID("sachin tendulkar",7,33)	tendulkar	gets the n characters from the given string
=MINUTE(now())	19	gets current minutes
=NETWORKDAYS("20-jan-2013",NOW())	200	counts the weekdays from given dates
=OR(12>33,FALSE)	FALSE	checks the OR conditions
=POWER(2,2)	4	gets the power of given number
=PROPER("sagar salunke")	Sagar Salunke	Converst the first letter of word to upper case
=QUOTIENT(5,2)	2	gets the QUOTIENT
=RAND()	0.988171935	gets the random number betweem 0 and 1
=RANDBETWEEN(1,100)	35	gets the random number between 1 and 100
=REPLACE("sachin ten",8,3,"Anjali")	sachin Anjalien	replace the part of string by other string
=RIGHT("sagar salunke",8)	salunke	get n characters from the right side of the string
=ROUND(4545.67,1)	4545.7	rounds the number
=ROW(A1)	3	gets the row number of given cell
=SEARCH("ten","sachin tendulkar")	8	search one string in other
=SMALL(A1:A99,2)	5	gets the nth smallest number from the given set
=SQRT(4)	2	gets the square root of the number
=SUBSTITUTE("sagar salunke","sagar","ganesh")	ganesh salunke	replace the part of string by other string
=SUM(A1:A11)	268	gets the sum of given numbers
=SUMIF(A1:A10,33)	231	gets the sum of numbers if criteria is fulfilled
=TODAY()	25-10-2013	gets today's date
=TRIM("           sagar salunke         ")	sagar salunke	removes the spaces from left and right side
=TYPE(22)	1	gets the data type of the parameter
=TRUNC(3434.56767)	3434.56	truncates the number
=UPPER("sagar salunke")	SAGAR SALUNKE	converts the letters to upper case
=WEEKDAY(NOW())	6	gets the weekday from the given date
=YEAR(NOW())	2013	gets the year from the given date