Articles On Testing

Wecome to http://www.articlesontesting.com !!!

Articles On Testing

Wecome to http://www.articlesontesting.com !!!

Articles On Testing

Wecome to http://www.articlesontesting.com !!!

Articles On Testing

Wecome to http://www.articlesontesting.com !!!

Articles On Testing

Wecome to http://www.articlesontesting.com !!!

Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Excel Handling Through QTP


Excel handling using QTP is one of the most required utility that any QTP programmer need to be expert at . In the next few sections we shall be handling several excel handling codes that one can re-utilize as per his/her needs:

How to Create a new Excel Object with a default new Workbook ?
How to Close a open Excel Object ?
How to save a workbook according to the workbookIdentifier?
How to set the given 'value' in the cell ?
How to returns the cell's value according to its row column and sheet ?
How to get  returned an Excel Sheet according to the sheetIdentifier ?
How to insert a new worksheet into the active workbook ?
How to renames a worksheet's name ?
How to remove a worksheet from a workbook ?
How to create a new workbook in the excel application ?
How to open a previously saved Excel workbook and add it to the Application ?
How to set one of the workbooks in the application as Active workbook ?
How to close an open workbook ?
How to compare between two sheets ?



Dim ObjExcelApp 'As Excel.Application
Dim objExcelSheet 'As Excel.worksheet
Dim objExcelBook 'As Excel.workbook
Dim objFso 'As Scripting.FileSystemObject

' This function will return a new Excel Object with a default new Workbook

Function CreateExcel() 'As Excel.Application

   Dim objExcelSheet 'As Excel.worksheet
   Set ObjExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
   ObjExcelApp.Workbooks.Add
   ObjExcelApp.Visible = True
   Set CreateExcel = ObjExcelApp
End Function

'This function will close the given Excel Object

'objExcelApp - an Excel application object to be closed

Sub CloseExcel(ObjExcelApp)

   Set objExcelSheet = ObjExcelApp.ActiveSheet
   Set objExcelBook = ObjExcelApp.ActiveWorkbook
   Set objFso = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   objFso.CreateFolder "C:\Viplav"
   objFso.DeleteFile "C:\Viplav\ExcelExamples.xls"
   objExcelBook.SaveAs "C:\Viplav\ExcelExamples.xls"
   ObjExcelApp.Quit
   Set ObjExcelApp = Nothing
   Set objFso = Nothing
   Err = 0
   On Error GoTo 0
End Sub

'The SaveWorkbook method will save a workbook according to the workbookIdentifier

'The method will overwrite the previously saved file under the given path
'objExcelApp - a reference to the Excel Application
'workbookIdentifier - The name or number of the requested workbook
'path - the location to which the workbook should be saved
'Return "OK" on success and "Bad Workbook Identifier" on failure

Function SaveWorkbook(ObjExcelApp, workbookIdentifier, path) 'As String

   Dim workbook 'As Excel.workbook
   On Error Resume Next
   Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
   On Error GoTo 0
   If Not workbook Is Nothing Then
   If path = "" Or path = workbook.FullName Or path = workbook.Name Then
   workbook.Save
   Else
   Set objFso = CreateObject("Scripting.FileSystemObject")
   'if the path has no file extension then add the 'xls' extension
   If InStr(path, ".") = 0 Then
   path = path & ".xls"
   End If
   On Error Resume Next
   objFso.DeleteFile path
   Set objFso = Nothing
   Err = 0
   On Error GoTo 0
   workbook.SaveAs path
   End If
   SaveWorkbook = "OK"
   Else
   SaveWorkbook = "Bad Workbook Identifier"
   End If
End Function

'The SetCellValue method sets the given 'value' in the cell which is identified by

'its row column and parent Excel sheet
'objExcelSheet - the excel sheet that is the parent of the requested cell
'row - the cell's row in the objExcelSheet
'column - the cell's column in the objExcelSheet
'value - the value to be set in the cell

Sub SetCellValue(objExcelSheet, row, column, value)

   On Error Resume Next
   objExcelSheet.Cells(row, column) = value
   On Error GoTo 0
End Sub

'The GetCellValue returns the cell's value according to its row column and sheet

'objExcelSheet - the Excel Sheet in which the cell exists
'row - the cell's row
'column - the cell's column
'return 0 if the cell could not be found

Function GetCellValue(objExcelSheet, row, column)

   value = 0
   Err = 0
   On Error Resume Next
   tempValue = objExcelSheet.Cells(row, column)
    If Err = 0 Then
    value = tempValue
    Err = 0
   End If
    On Error GoTo 0
    GetCellValue = value
End Function

'The GetSheet method returns an Excel Sheet according to the sheetIdentifier

'ObjExcelApp - the Excel application which is the parent of the requested sheet
'sheetIdentifier - the name or the number of the requested Excel sheet
'return Nothing on failure

Function GetSheet(ObjExcelApp, sheetIdentifier) 'As Excel.worksheet

    On Error Resume Next
    Set GetSheet = ObjExcelApp.Worksheets.Item(sheetIdentifier)
    On Error GoTo 0
End Function

'The InsertNewWorksheet method inserts an new worksheet into the active workbook or

'the workbook identified by the workbookIdentifier, the new worksheet will get a default
'name if the sheetName parameter is empty, otherwise the sheet will have the sheetName
'as a name.
'Return - the new sheet as an Object
'ObjExcelApp - the excel application object into which the new worksheet should be added
'workbookIdentifier - an optional identifier of the worksheet into which the new worksheet should be added
'sheetName - the optional name of the new worksheet.

Function InsertNewWorksheet(ObjExcelApp, workbookIdentifier, sheetName) 'As Excel.worksheet

   Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    'In case that the workbookIdentifier is empty we will work on the active workbook
    If workbookIdentifier = "" Then
    Set workbook = ObjExcelApp.ActiveWorkbook
    Else
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    Set InsertNewWorksheet = Nothing
    Err = 0
    Exit Function
    End If
    On Error GoTo 0
    End If
    sheetCount = workbook.Sheets.Count
    workbook.Sheets.Add , sheetCount
    Set worksheet = workbook.Sheets(sheetCount + 1)
    'In case that the sheetName is not empty set the new sheet's name to sheetName
    If sheetName <> "" Then
    worksheet.Name = sheetName
    End If
    Set InsertNewWorksheet = worksheet
End Function

'The RenameWorksheet method renames a worksheet's name

'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
'sheetName - the new name for the worksheet

Function RenameWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier, sheetName) 'As String

    Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    RenameWorksheet = "Bad Workbook Identifier"
    Err = 0
    Exit Function
    End If
    Set worksheet = workbook.Sheets(worksheetIdentifier)
    If Err <> 0 Then
    RenameWorksheet = "Bad Worksheet Identifier"
    Err = 0
    Exit Function
    End If
    worksheet.Name = sheetName
    RenameWorksheet = "OK"
End Function

'The RemoveWorksheet method removes a worksheet from a workbook

'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
Function RemoveWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier) 'As String

   Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    RemoveWorksheet = "Bad Workbook Identifier"
    Exit Function
    End If
    Set worksheet = workbook.Sheets(worksheetIdentifier)
    If Err <> 0 Then
    RemoveWorksheet = "Bad Worksheet Identifier"
    Exit Function
    End If
    worksheet.Delete
    RemoveWorksheet = "OK"
End Function

'The CreateNewWorkbook method creates a new workbook in the excel application

'ObjExcelApp - the Excel application to which an new Excel workbook will be added
Function CreateNewWorkbook(ObjExcelApp)
    Set NewWorkbook = ObjExcelApp.Workbooks.Add()
    Set CreateNewWorkbook = NewWorkbook
End Function

'The OpenWorkbook method opens a previously saved Excel workbook and adds it to the Application

'objExcelApp - the Excel Application the workbook will be added to
'path - the path of the workbook that will be opened
'return Nothing on failure
Function OpenWorkbook(ObjExcelApp, path)

    On Error Resume Next
    Set NewWorkbook = ObjExcelApp.Workbooks.Open(path)
    Set OpenWorkbook = NewWorkbook
    On Error GoTo 0
End Function

'The ActivateWorkbook method sets one of the workbooks in the application as Active workbook

'ObjExcelApp - the workbook's parent excel Application
'workbookIdentifier - the name or the number of the workbook
Sub ActivateWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Activate
On Error GoTo 0
End Sub

'The CloseWorkbook method closes an open workbook

'ObjExcelApp - the parent Excel application of the workbook
'workbookIdentifier - the name or the number of the workbook
Sub CloseWorkbook(ObjExcelApp, workbookIdentifier)
    On Error Resume Next
    ObjExcelApp.Workbooks(workbookIdentifier).Close
    On Error GoTo 0
End Sub

'The CompareSheets method compares between two sheets.

'if there is a difference between the two sheets then the value in the second sheet
'will be changed to red and contain the string:
'"Compare conflict - Value was 'Value2', Expected value is 'value2'"
'sheet1, sheet2 - the excel sheets to be compared
'startColumn - the column to start comparing in the two sheets
'numberOfColumns - the number of columns to be compared
'startRow - the row to start comparing in the two sheets
'numberOfRows - the number of rows to be compared
Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean
   Dim returnVal 'As Boolean
   returnVal = True
    'In case that one of the sheets doesn't exists, don't continue the process
    If sheet1 Is Nothing Or sheet2 Is Nothing Then
    CompareSheets = False
    Exit Function
   End If
    'loop through the table and fill values into the two worksheets
   For r = startRow to (startRow + (numberOfRows - 1))
    For c = startColumn to (startColumn + (numberOfColumns - 1))
    Value1 = sheet1.Cells(r, c)
    Value2 = sheet2.Cells(r, c)
    'if 'trimed' equels True then used would like to ignore blank spaces
    If trimed Then
    Value1 = Trim(Value1)
    Value2 = Trim(Value2)
    End If
    'in case that the values of a cell are not equel in the two worksheets
    'create an indicator that the values are not equel and set return value
    'to False
    If Value1 <> Value2 Then
    Dim cell 'As Excel.Range
    sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."
    Set cell = sheet2.Cells(r, c)
    cell.Font.Color = vbRed
    returnVal = False
    End If
    Next
    Next
    CompareSheets = returnVal
End Function


For gaining more insights in the automation using QTP log on to below url :
Automation Testing Using QTP

Excel String Formulae

Category
Function 
String
Asc (VBA)
String
Char
String
Chr (VBA)
String
Clean
String
Code
String
Concatenate
String
Concatenate with &
String
CurDir (VBA)
String
Dollar
String
Exact
String
Find
String
Fixed
String
Format (VBA)
String
InStr (VBA)
String
LCase (VBA)
String
Left
String
Len
String
Lower
String
LTrim (VBA)
String
Mid
String
Proper
String
Replace
String
String
String
String
String
Space (VBA)
String
Str (VBA)
String
StrConv (VBA)
String
Substitute
String
T
String
Text
String
Trim
String
UCase (VBA)
String
Upper
String
Value

Asc (VBA)
In Excel, the Asc function returns the NUMBER code that represents the specific character. The syntax for the Asc function is: Asc( string ) string is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first. For example: Asc ("W") would return 87. Asc ("Wednesday") would return 87. Asc ("x") would return 120. VBA Code The Asc function can only be used in VBA code. For example: Dim LResult As Integer LResult = Asc ("W") In this example, the variable called LResult would now contain the value 87.




Char
In Excel, the Char function returns the character based on the NUMBER code. The syntax for the Char function is: Char( number_code ) number_code is the NUMBER used to retrieve the character.




Chr (VBA)
In Excel, the Chr function returns the character based on the NUMBER code. The syntax for the Chr function is: Chr( number_code ) number_code is the NUMBER used to retrieve the character. For example: Chr (87) would return "W" Chr (105) would return "i" VBA Code The Chr function can only be used in VBA code. For example: Dim LResult As String LResult = Chr(87) In this example, the variable called LResult would now contain the value "W".




Clean
In Excel, the Clean function removes all nonprintable characters from a string. The syntax for the Clean function is: Clean( text ) text is the value that has all nonprintable characters removed from




Code
In Excel, the Code function returns the NUMBER code that represents the specific character. The syntax for the Code function is: Code( text ) text is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.




Concatenate
In Excel, the Concatenate function allows you to join 2 or more strings together. The syntax for the Concatenate function is: Concatenate( text1, text2, ... text_n ) There can be up to 30 strings that are joined together




Concatenate with &
To concatenate multiple strings into a single string in Excel, you can use the "&" operator to separate the string values. string_1 & string_2 & string_n VBA Code The "&" operator can be used to concatenate strings in VBA code. For example: Dim LValue As String LValue = "Alpha" & "bet"




CurDir (VBA)
In Excel, the CurDir function returns the current path. The syntax for the CurDir function is: CurDir( drive ) drive is an optional parameter. If this parameter is omitted, the CurDir function assumes the current drive. For example: CurDir () would return "C:\Documents and Settings\user\My Documents" CurDir ("G") would return "G:\" VBA Code The CurDir function can only be used in VBA code. For example: Dim LResult As String LResult = CurDir () In this example, the variable called LResult would now contain the value "C:\Documents and Settings\user\My Documents




Dollar
In Excel, the Dollar function converts a number to text, using a currency format. The format used is $#,##0.00_);($#,##0.00). The syntax for the Dollar function is: Dollar( number, decimal_places ) number is the number to convert to text. decimal_places is the number of decimal places to display. The number will be rounded accordingly




Exact
In Excel, the Exact function compares two strings and returns TRUE if both values are the same. Otherwise, it will return FALSE. The syntax for the Exact function is: Exact( text1, text2 ) text1 and text2 are the values to compare. The Exact function is case-sensitive.




Find
In Excel, the Find function returns the location of a substring in a string. The search is case-sensitive. The syntax for the Find function is: Find( text1, text2, start_position ) text1 is the substring to search for in text2. text2 is the string to search. start_position is the position in text1 where the search will start. The first position is 1. If the Find function does not find a match, it will return a #VALUE! error




Fixed
In Excel, the Fixed function returns a text representation of a number rounded to a specified number of decimal places. The syntax for the Fixed function is: Fixed( number, decimal_places, no_commas ) number is the number to round. decimal_places is the number of decimal places to display in the result. no_commas - If this parameter is set to TRUE, the result will not display commas. If it is set to FALSE, it will display commas in the result.




Format (VBA)
In Excel, the Format function takes an expression and returns it as a formatted string. The syntax for the Format function is: Format ( expression, [ format ] ) expression is the value to format. format is optional. It is the format to apply to the expression. You can either define your own format or use one of the named formats that Excel has predefined such as: Format Explanation General Number Displays a number without thousand separators. Currency Displays thousand separators as well as two decimal places. Fixed Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place. Standard Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place. Percent Displays a percent value - that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place. Scientific Scientific notation. Yes/No Displays No if the number is 0. Displays Yes if the number is not 0. True/False Displays True if the number is 0. Displays False if the number is not 0. On/Off Displays Off if the number is 0. Displays On is the number is not 0. General Date Displays date based on your system settings Long Date Displays date based on your system's long date setting Medium Date Displays date based on your system's medium date setting Short Date Displays date based on your system's short date setting Long Time Displays time based on your system's long time setting Medium Time Displays time based on your system's medium time setting Short Time Displays time based on your system's short time setting For example: Format("210.6", "#,##0.00") would return '210.60' Format("210.6", "Standard") would return '210.60' Format("0.981", "Percent") would return '98.10%' Format("1267.5", "Currency") would return '$1,267.50' Format("Sep 3, 2003", "Short Date") would return '9/3/2003' VBA Code The Format function can only be used in VBA code. For example: Dim LValue As String LValue = Format("0.981", "Percent") In this example, the variable called LValue would now contain the value of '98.10%'.




InStr (VBA)
In Excel, the InStr function returns the position of the first occurrence of a string in another string. The syntax for the InStr function is: InStr( [start], string_being_searched, string2, [compare] ) start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1. string_being_searched is the string that will be searched. string2 is the string to search for. compare is optional. This is the type of comparison to perform. The valid choices are: vbBinaryCompare, vbTextCompare, and vbDatabaseCompare. For example: InStr(1, "Tech on the Net", "the") would return 9. InStr("Tech on the Net", "the") would return 9. InStr(10, "Tech on the Net", "t") would return 15. VBA Code The InStr function can only be used in VBA code. For example: Dim LPosition As Integer LPosition = InStr(10, "Tech on the Net", "t") In this example, the variable called LPosition would now contain the value 15.




LCase (VBA)
In Excel, the LCase function converts a string to lower-case. The syntax for the LCase function is: LCase( text ) Text is the string that you wish to convert to lower-case. For example: LCase("Tech on the Net") would return "tech on the net" LCase("124ABC") would return "124abc" VBA Code The LCase function can only be used in VBA code. For example: Dim LResult As String LResult = LCase("This is a TEST") In this example, the variable called LResult would now contain the value "this is a test".




Left
In Excel, the Left function allows you to extract a substring from a string, starting from the left-most character. The syntax for the Left function is: Left( text, number_of_characters ) text is the string that you wish to extract from. number_of_characters indicates the number of characters that you wish to extract starting from the left-most character.




Len
In Excel, the Len function returns the length of the specified string. The syntax for the Len function is: Len( text ) text is the string to return the length for.




Lower
In Excel, the Lower function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The syntax for the Lower function is: Lower( text ) text is the string to convert to lowercase




LTrim (VBA)
In Excel, the LTrim function removes leading spaces from a string. The syntax for the LTrim function is: LTrim( text ) text is the string that you wish to remove leading spaces from. For example: LTrim(" Tech on the Net") would return "Tech on the Net" LTrim(" Alphabet ") would return "Alphabet " VBA Code The LTrim function can only be used in VBA code. For example: Dim LResult As String LResult = LTrim(" Alphabet ") The variable LResult would now contain the value of "Alphabet ".




Mid
In Excel, the Mid function extracts a substring from a string (starting at any position). The syntax for the Mid function is: Mid( text, start_position, number_of_characters ) text is the string that you wish to extract from. start_position indicates the position in the string that you will begin extracting from. The first position in the string is 1. number_of_characters indicates the number of characters that you wish to extract.




Proper
In Excel, the Proper function sets the first character in each word to uppercase and the rest to lowercase. The syntax for the Proper function is: Proper( text ) text is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.




Replace
In Excel, the Replace function replaces a sequence of characters in a string with another set of characters. The syntax for the Replace function is: Replace( old_text, start, number_of_chars, new_text ) old_text is the original string value. start is the position in old_text to begin replacing characters. number_of_chars is the number of characters to replace in old_text. new_text is the replacement set of characters




Rept
In Excel, the Rept function returns a repeated text value a specified number of times. The syntax for the Rept function is: Rept( text, number ) text is the text value to repeat. number is the number of times to repeat the text value.




Right
In Excel, the Right function extracts a substring from a string starting from the right-most character. The syntax for the Right function is: Right( text, number_of_characters ) text is the string that you wish to extract from. number_of_characters indicates the number of characters that you wish to extract starting from the right-most character.




RTrim (VBA)
In Excel, the RTrim function removes trailing spaces from a string. The syntax for the RTrim function is: RTrim( text ) text is the string that you wish to remove trailing spaces from. For example: RTrim("Tech on the Net ") would return "Tech on the Net" RTrim(" Alphabet ") would return " Alphabet" VBA Code The RTrim function can only be used in VBA code. For example: Dim LResult As String LResult = RTrim(" Alphabet ") The variable LResult would now contain the value of " Alphabet".




Search
In Excel, the Search function returns the location of a substring in a string. The search is NOT case-sensitive. The syntax for the Search function is: Search( text1, text2, start_position ) text1 is the substring to search for in text2. text2 is the string to search. start_position is the position in text1 where the search will start. The first position is 1. If the Search function does not find a match, it will return a #VALUE! error.




Space (VBA)
In Excel, the Space function returns a string with a specified number of spaces. The syntax for the Space function is: Space( number ) number is the number of spaces to be returned. For example: Space(3) would return " " Space(7) would return " " VBA Code The Space function can only be used in VBA code. For example: Dim LResult As String LResult = Space(5) In this example, the variable called LResult would now contain the value " ".




Str (VBA)
In Excel, the Str function returns a string representation of a number. The syntax for the Str function is: Str( number ) number is the value to convert to a string. For example: Str(12) would return "12". Str(450) would return "450" VBA Code The Str function can only be used in VBA code. For example: Dim LResult As String LResult = Str(16) In this example, the variable called LResult would now contain the value "16".




StrConv (VBA)
In Excel, the StrConv function returns a string converted as specified. The syntax for the StrConv function is: StrConv ( text, conversion, LCID ) text is the string that you wish to convert. conversion is the type of conversion to perform. The following is a list of valid parameters for conversion. Parameter Value Description vbUpperCase 1 Converts the string to all uppercase. vbLowerCase 2 Converts the string to all lowercase. vbProperCase 3 Converts the first letter to every word to uppercase. All other characters are left as lowercase. This option is similar to the InitCap function in Oracle. vbUnicode 64 Converts the string to Unicode. vbFromUnicode 128 Converts the string from Unicode to the default code page of the system. LCID is optional. If this parameter is omitted, the StrConv function assumes the system LocaleID. For example: StrConv("tech on the net", 1) would return "TECH ON THE NET" StrConv("TECH ON THE NET", 2) would return "tech on the net" StrConv("TECH ON THE NET", 3) would return "Tech On The Net" VBA Code The StrConv function can only be used in VBA code. For example: Dim LResult As String LResult = StrConv("TECH ON THE NET", vbProperCase) In this example, the variable called LResult would now contain the value "Tech on the Net".




Substitute
In Excel, the Substitute function replaces a set of characters with another. The syntax for the Substitute function is: Substitute( text, old_text, new_text, nth_appearance ) text is the original string to use to perform the substitution. old_text is the existing characters to replace. new_text is the new characters to replace old_text with. nth_appearance is optional. It is the nth appearance of old_text that you wish to replace. I f this parameter is omitted, then every occurrence of old_text will be replaced with new_text.




T
In Excel, the T function returns the text referred to by a value. The syntax for the T function is: T( value ) value is the to extract the text from.

http://www.likeoffice.com/image/users/28057/ftp/my_files/excel/functions/T.gif?id=2220230




Text
In Excel, the Text function returns a value converted to text with a specified format. The syntax for the Text function is: Text( value, format ) value is the value to convert to text. format is the format to display the results in.




Trim
In Excel, the Trim function returns a text value with the leading and trailing spaces removed. The syntax for the Trim function is: Trim( text ) text is the text value to remove the leading and trailing spaces from.




UCase (VBA)
In Excel, the UCase function converts a string to all upper-case. The syntax for the UCase function is: UCase( text ) text is the string that you wish to convert to upper-case. For example: UCase("Tech on the Net") would return "TECH ON THE NET" UCase("Alphabet") would return "ALPHABET" VBA Code The UCase function can only be used in VBA code. For example: Dim LResult As String LResult = UCase("This is a TEST") In this example, the variable called LResult would now contain the value "THIS IS A TEST".




Upper
In Excel, the Upper function allows you to convert text to all uppercase. The syntax for the Upper function is: Upper( text ) text is the string that you wish to convert to uppercase.




Value
In Excel, the Value function converts a text value that represents a number to a number. The syntax for the Value function is: Value( text ) text is the text value to convert to a number. If text is not a number, the Value function will return #VALUE!.


Datatable to eXcel Workbook

Sub Page_Load(sender As Object, e As EventArgs)
    
        ' Create a DataSet from an XML file.  Modify this code to use
        ' any DataSet such as one returned from a database query.
        Dim xmlfile As String = Server.MapPath("files/spiceorder.xml")
        Dim dataset As New System.Data.DataSet()
        dataset.ReadXml(xmlfile)
        Dim datatable As System.Data.DataTable = dataset.Tables("OrderItems")
        
        ' Create a new workbook and worksheet.
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
        Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
        worksheet.Name = "Spice Order"
        
        ' Get the top left cell for the DataTable.
        Dim range As SpreadsheetGear.IRange = worksheet.Cells("A1")
        
        ' Copy the DataTable to the worksheet range.
        range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.None)
        
        ' Auto size all worksheet columns which contain data
        worksheet.UsedRange.Columns.AutoFit()
        
        ' Stream the Excel spreadsheet to the client in a format
        ' compatible with Excel 97/2000/XP/2003/2007.
        Response.Clear()
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97)
        Response.End()
        
    End Sub 'Page_Load

Excel Handling In VB.Net

Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) ' Create a new workbook. Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook() Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1") Dim cells As SpreadsheetGear.IRange = worksheet.Cells ' Set the worksheet name. worksheet.Name = "2005 Sales" ' Load column titles and center. cells("B1").Formula = "North" cells("C1").Formula = "South" cells("D1").Formula = "East" cells("E1").Formula = "West" cells("B1:E1").HorizontalAlignment = SpreadsheetGear.HAlign.Center ' Load row titles using multiple cell text reference and iteration. Dim quarter As Integer = 1 Dim cell As SpreadsheetGear.IRange For Each cell In cells("A2:A5") cell.Formula = "Q" & quarter quarter = quarter + 1 Next cell ' Load random data and format as $ using a multiple cell range. Dim body As SpreadsheetGear.IRange = cells(1, 1, 4, 4) body.Formula = "=RAND() * 10000" body.NumberFormat = "$#,##0_);($#,##0)" ' Stream the Excel spreadsheet to the client in a format ' compatible with Excel 97/2000/XP/2003/2007. Response.Clear() Response.ContentType = "application/vnd.ms-excel" Response.AddHeader("Content-Disposition", "attachment; filename=report.xls") workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97) Response.End() End Sub 'Page_Load