Tuesday, April 30, 2013

Some Useful Tips for QTP

  • Listed down are some of the useful QTP Tips.

  • Always start first then your application to automate.
  • Try to avoid the Text Checkpoints, Recovery Scenarios file and other similar functions that deal with screen resolution as this may fail your test in the long run.
  • Load only those Addin that are required for your application, loading extra AddIn slows down your application.
  • Turn off Run >> Results option while scripting the aplication as it will fasten the execution process.

Wednesday, April 24, 2013

VBA - Create Hyperlink Using VBA Coding on click of button.

 This Post will help you in creating hyperlink dynamically using VBA coding.

Requirement: My Client wants to create hyperlinks dynamically with a click of button with the source value present in column two "Link Source" and the name of link is present in Column 1 with heading "Link Name" and the hyperlink must be created in third column with heading "Final Hyper Link"

To Run the VBA code I have created a button called "Create Hyperlink" Clicking on it will create the hyperlinks in Final Hyper Link Column.

'Function Name: CreateHyperlink
'Author Name: Anshu Khandelwal
'Input : From Excel Work Book as link name and link source.
'This function can be run after calling this function on Button or Directly running it

Public Function CreateHyperlink()
Dim link_name As String
Dim link_value As String
Dim i As Integer

'Using Count to work on Dynamic Records.
 Count = Application.WorksheetFunction.CountA(Range("A:A"))

For i = 2 To Count
    link_name = Cells(i, 1).Value   'Picking up link_name value from Column 1
    link_source = Cells(i, 2).Value 'Picking up link_source value from Column
    Cells(i, 3).Select   'Select a Cell where you want Hyperlink to display
 'Here i am adding hyperlinks with ActiveSheet reference and Passing SubAddress in the form '
 of link_source and Texttodisplay using link_name variable which contains values from excel workbook.
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=link_source, _
Next i

End Function

Note: Anyone can directly use this code just copy and paste in your module and change value in your excel as per your need.

Monday, April 15, 2013

Important VBA(WorkSheet) Functions with Example

Listed down are some of the important VBA worksheet functions that we can use in Day to Day Automation.:

  • Command to Activate a Sheet:Sheets(Sheet_name).Activate
                     Public Function SheetActivateExample()
                      End Function

  • Activate a Sheet with the help of index property:Sheets(index).Activate            
                 Public Function SheetActivateExample()
                 End Function

  • Count the number of Sheets present in your excel workbook.In Excel the indexing start from 1 not by 0.To count till end of the Sheet function is Sheets.count
                Public Function Sheet_count()
                      Dim i as integer
                          For i = 1 to Sheets.count
                                  'It will loop you to the end of Sheets.
                         Next i
               End function

Sunday, April 7, 2013

Visual Basic For Application for Office 2007,2013

                                Running Office 2007 VBA code in office 2010 and 2013.
If you are trying to run VBA code built in 2007 or earlier version of office into 2010 and 2013  
office then you need to take note of the following points

If variable not declared in office 2007 VBA code then your code will not going to be run in office 2010 or 2013 code.