Comparing text using Regular expression using vbscript

http://msdn.microsoft.com/en-us/library/ee236359%28v=vs.84%29.aspx

http://stackoverflow.com/questions/21800742/regular-expression-using-vbscript-for-starting-characters/21801104?noredirect=1#21801104

Symbols:
! =
^   = this is carat operator to identify beginning of sting. Ex: "^a" , Ex: "^(AET)". Only match the beginning of a string 'AET' here.
*   = Matches one or more times. Ex: ".*" ..matches any character any number of times.
.    = Matches any characters
()  = this is useful to combine operations
""  = matches any character between " ". Any characters b/w "", will check if that chars are present in string.
|  =This is OR operator
? =  Matches the previous element one or more time
[] = You can create a list of matching characters by enclosing one or more individual characters in brackets [ ]. Match any one character enclosed in the character set.
{} = Match exactly x occurrences of a regular expression.
\  = matches the above character in the string, to make them not do there operations. Ex: string= "AET*" , to match this "*" in string, pattern is ".*(\*)"
$ = Only match the ending of a string. "t$". Ex: "Tight"
\b = Matches any word boundary. "ly\b" matches "ly" in "possibly tomorrow."
\B = Matches any non-word boundary.
[^xyz]   - Matches any one character not in the set. Both opening and closing brackets are required.
Example "[^abc]" matches the "p" in "plain
[^a-z]  - Matches any character not in the specified range.
Example "[^m-z]" matches any character not in the range of "m" through "z".

Quantifier	Explicit quantifier	Meaning
*	{0,}	Matches the previous element zero or more times.
+	{1,}	Matches the previous element one or more times.
?	{0,1}	Matches the previous element zero or one time.

Method:
-Execute()
-Matches()


' Code for regular expression
Sub validatingtext()
Set RegularExpressionObject = CreateObject("vbscript.regexp")

src = "AET_P1s_ dfd"
RegularExpressionObject.IgnoreCase = True
RegularExpressionObject.Global = True
RegularExpressionObject.Pattern = "^(AET|BUS)_.*_.*"
'RegularExpressionObject.Execute (src)
Set Matches = RegularExpressionObject.Execute(src)
If (Matches.Count <> 0) Then
MsgBox ("True")
Else
MsgBox ("False")
End If

End Sub

Working on two or more Excel files using macro

Application.Workbooks("<Excel_Name1>").Worksheets(1).Range("A1").Value = "Hello"


http://www.excel-easy.com/vba/create-a-macro.html

Open a workbook(excel) file on your desktop using vbscript

Sub Button4_Click()
 file_Name = Application.GetOpenFilename() '-- to show the pop up window to find the file  and then it captures the filename of selected file when clicked ok.

' or file_Name= Application.GetOpenFilename(, , Title:="Select the Release module report") '-- to 'show the pop up window(with the text on it) to find the file and open it
set owb=Application.Workbooks.Open(file_Name) '-- it will really open the file. GetOpenFileanme - does not open the file
 MsgBox (file_Name) ' file_Name has full file path

End Sub

Row Count and Column Count from Excel

Finding the last Row or Coumn:
Solution 1:
Process 1: (2/9/2014)

lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
MsgBox (lastrow) 'Total number of rows(which has data) in Excel.
lastcolumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
MsgBox(lastcolumn) ' gives total number of columns(which has data) in Excel.


4/7/15:

lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row ' this gives the row number

' Lets suppose you applied filter, i found 2 records(row 2 and row 36) after applying filter, this code is giving me lastrow values as 36. so this formula cannot be used for counting rows.


5/7/15:
Solution 2:
'Get the Number of Rows used in the Excel sheetRowCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
msgbox "Number of rows used in the excel sheet "& RowCount

'Get the Number of Columns used in the Excel sheet
ColumnCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Columns.count
msgbox "Number of columns used in the excel sheet "&ColumnCount

source: http://mahimavbscript.blogspot.com/2010/09/working-with-excel-object.html

Note: UsedRange sometimes gives disguising results, need to find out why.

Solution 3:

Selection method plays a major role here.


Ex:
        Workbooks(Filename).Sheets(Director).Range("B25").Select 'Dont know why not select
     
        Workbooks(Filename).Sheets(Director).Range(Selection, Selection.End(xlDown)).Select
        Workbooks(Filename).Sheets(Director).Range(Selection, Selection.End(xlToRight)).Select

Creating Table from Excel Data

Sub Table()
Dim r, a, rfr, d, c, o, otr, Row, i
Rowscount = Sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Row

Columnscount = Sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
r = 0
a = 0
d = 0
rfr = 0
c = 0
o = 0
otr = otr + 1


For Row = 2 To Rowscount

'If ((Sheet1.Range("E" & Row).Value = "Assigned" Or "Ready For Retest" Or "Deferred" Or "Open")) Then

i = Sheet1.Range("E" & Row).Value

Select Case i

          Case "Resolved"

              r = r + 1

          Case "Open"
              o = o + 1
          Case "Assigned"
              a = a + 1
          Case "Ready For Retest"
              rfr = rfr + 1
          Case "Cancelled"
              c = c + 1
          Case "Deferred"
              d = d + 1
          Case Else
               otr = otr + 1

End Select

Next
MsgBox (a)
 Sheet2.Range("K17").Value = a
Sheet2.Range("k18").Value = d
Sheet2.Range("k19").Value = o
Sheet2.Range("k20").Value = rfr
Sheet2.Range("k21").Value = r
Sheet2.Range("k22").Value = otr
End Sub