How To open Password Protected Excel sheets

Function UnprotectXL(filePath,fileName,pwd,writeresPwd)
   Set objExcel=CreateObject(“Excel.Application”)
   objExcel.Visible=false
   testData=filePath&”\”&fileName
 
   Set oWorkbook=objExcel.Workbooks
 
   Set myWkbook=objExcel.Workbooks.open (testData,0,False,5,pwd,writeresPwd)
   objExcel.DisplayAlerts=False
   oWorkbook(fileName).Activate
   For Each w in objExcel.Workbooks
        w.SaveAs testData,,”",”"
      
   Next
 
   objExcel.Workbooks.Close
   objExcel.Quit
   Set oWorkbook=Nothing
   Set objExcel=Nothing
  
End Function
Function ProtectXL(filePath,fileName,pwd,writeresPwd)
     On Error Resume Next
     Set objExcel=CreateObject(“Excel.Application”)
     objExcel.Visible=False
     testData=filePath&”\”&fileName
     Set oWorkbook=objExcel.Workbooks
     Set outputWkbook=objExcel.Workbooks.open (testData,0,False)
     oWorkbook(testData).Activate
     objExcel.DisplayAlerts=False  
     outputWkbook.SaveAs testData,,pwd,writeresPwd
   
     outputWkbook.Close
     objExcel.Workbooks.Close
     objExcel.Quit
     Set outputWkbook=Nothing
     Set objExcel=Nothing
  
End Function

‘Call ProtectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)
‘Call UnprotectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)

Author: Mohan Kakarla

Merge 2 XML using Dotnet Factory

Dim objReader1,objReader2,objds1,objds2
Set objReader1 = DotNetFactory.CreateInstance(“System.Xml.XmlTextReader”, “System.Xml”,”C:\Documents and Settings\kmohankumar\Desktop\11.xml”)’ Creates the instance for the XMLWriter
Set objReader2 = DotNetFactory.CreateInstance(“System.Xml.XmlTextReader”, “System.Xml”,”C:\Documents and Settings\kmohankumar\Desktop\12.xml”)’
Set objds1 = DotNetFactory.CreateInstance(“System.Data.DataSet”, “System.Data”)
Set objds2 = DotNetFactory.CreateInstance(“System.Data.DataSet”, “System.Data”)
objds1.ReadXml(objReader1)
objds2.ReadXml(objReader2)
objds1.Merge(objds2)
objds1.WriteXml(“C:\Documents and Settings\kmohankumar\Desktop\13.xml”)
objReader1.close
objReader2.close
Set objReader1=Nothing
Set objReader2=Nothing
Set objds1=nothing
Set objds2=nothing
  

Author:Mohan Kakarla

Create XML using Dotnet Factory

 Dim objWriter,XmlWriter
   Set objWriter = DotNetFactory.CreateInstance(“System.Xml.XmlWriter”, “System.Xml”)’ Creates the instance for the XMLWriter
 set XmlWriter=objWriter.Create(“C:\Documents and Settings\kmohankumar\Desktop\1234.xml”)’ creates xml document
 
 XmlWriter.WriteStartElement(“Books”)’ start writing the element
 XmlWriter.WriteStartElement(“Author”)

XmlWriter.WriteAttributeString “Name”, “Mohan”‘ writes  attributes and its values to the element
 XmlWriter.WriteEndElement()’ ends writing the element
 XmlWriter.WriteStartElement(“Title”)

XmlWriter.WriteAttributeString “Title1″, “QTP”‘
 XmlWriter.WriteEndElement()
 XmlWriter.WriteFullEndElement()

XmlWriter.close()

Set objWriter =Nothing

Author:Mohan kakarla

Read XML using Dotnet Factory

Set objReader = DotNetFactory.CreateInstance(“System.Xml.XmlReader”, “System.Xml”)
set XmlReader=objReader.Create(“C:\Documents and Settings\kmohankumar\Desktop\3.xml”)

while (XmlReader.Read())

If  XmlReader.NodeType = “XmlDeclaration” or XmlReader.NodeType = “Element”Then
  
    Reporter.ReportEvent micPass, “NodeType:”&XmlReader.NodeType &”  Name :”& XmlReader.Name& ” is at Depth:”& XmlReader.Depth,”"

    If XmlReader.HasValue Then
        Reporter.ReportEvent micPass, ” Value: “& XmlReader.Value,”"
    End If

    Reporter.ReportEvent micPass, “Element:”& XmlReader.Name&”  has attribute(s)”&”:”& XmlReader.AttributeCount,”"

  If XmlReader.AttributeCount > 0 Then

        while  XmlReader.MoveToNextAttribute()
     fn = XmlReader.Name
     ns = XmlReader.NamespaceURI
      Reporter.ReportEvent micPass,” Attribute:”&fn &”  Value: “& ns&” Attribute:”& XmlReader.GetAttribute(fn, ns),”"
        wend
     End If
End If
wend
XmlReader.Close()

Author: Mohan Kakarla

Search for a particular value in Excel

Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
 Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet

 Set objSheet = appExcel.Sheets(“Sheet1″)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
    Set c = .Find (“nn”)’ data to find  
For each c in objSheet.UsedRange’ Loop through the used range
 If c=”nn” then’ compare with the expected data
            c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
            Set c = .FindNext(c)’ next search
       
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing

Author:Mohan Kakarla

Upload Attachments To QC

Function UpLoadAttachmentToQC(FilePath)

Set ObjCurrentTest = QCUtil.CurrentTest.Attachments
Set ObjAttch = ObjCurrentTest.AddItem(Null)
ObjAttch.FileName = FilePath
ObjAttch.Type = 1
ObjAttch.Post
ObjAttch.Refresh

End Function
FilePath=”C:\abc.vbs”
Call UpLoadAttachmentToQC(FilePath)

Author: Mohan Kakarla

Source: QC-OTA

Remove Attachment from QC Through QTP

‘The following function will Remove the specified attachment  from the current test of QC when  the file name provided
Function RemoveQCAttachements(NameOfTheFile)
var_count= QCUtil.CurrentTest.Attachments.NewList(“”).count
For i= 1 to var_count

 If   QCUtil.CurrentTest.Attachments.NewList(“”).Item(2).Name =NameOfTheFile Then
         AttachmentID = QCUtil.CurrentTest.Attachments.NewList(“”).Item(1).ID
   QCUtil.CurrentTest.Attachments.RemoveItem(AttachmentID)
 Else
     Reporter.ReportEvent micFail,”Please provide the valid filename with extention”,”Please provide the valid filename with extention”
 End If

Next
End Function

NameOfTheFile=”abc.vbs” ‘Provide the file name with extension
call RemoveQCAttachements(NameOfTheFile)

Author:Mohan Kakarla

Copy an excel sheet to another excel

Following is the code to copy the conntents of a sheet in one excel to another excel sheet

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\2.xls”)
objWorkbook1.Worksheets(“Sheet1″).UsedRange.Copy
objWorkbook2.Worksheets(“Sheet1″).Range(“A1″).PasteSpecial Paste =xlValues
objWorkbook1.save
objWorkbook2.save
objWorkbook1.close
objWorkbook2.close
set objExcel=nothing

 Author: Mohan Kakarla

Prompt Dialog For Password Entry In QTP with help of DotNetFactory

The following code popup a dialog with a edit box for a password entry when it run from the QTP

Set objForm = DotNetFactory.CreateInstance(“System.Windows.Forms.Form”, “System.Windows.Forms”)
Set objBtn1 = DotNetFactory.CreateInstance(“System.Windows.Forms.Button”, “System.Windows.Forms”)
Set objEdit1 = DotNetFactory.CreateInstance(“System.Windows.Forms.TextBox”, “System.Windows.Forms”)
x=80
y=40
Set p1 = DotNetFactory.CreateInstance(“System.Drawing.Point”,”System.Drawing”,x,y) ‘This will provide the locations(X,Y) for the controls
Set lbl= DotNetFactory.CreateInstance(“System.Windows.Forms.Label”,”System.Windows.Forms”)
lbl.Text=”Enter Password”
lbl.Location=p1
objForm.Controls.Add(lbl)
p1.Y=CInt(lbl.Height)+40
objEdit1.Location=p1
objForm.Controls.Add(lbl)

objEdit1.UseSystemPasswordChar=true’To set the password character From system

objForm.Controls.Add(objEdit1)
objBtn1.Text=”OK”

p1.Y=Cint(p1.Y)+CInt(objEdit1.Height)+20
objBtn1.Location=p1

objForm.CancelButton=objBtn1

objForm.Controls.Add(objBtn1)

objForm.StartPosition=CenterScreen
objForm.Text=”Mohan kakarla”
objForm.ShowDialog

msgbox “The Password You have Entered Is: “&objEdit1.Text

Author: Mohan Kumar Kakarla

OpenFileDialog In QTP with help of DotNetFactory

 Set fd = DotNetFactory.CreateInstance(“System.Windows.Forms.OpenFileDialog”, “System.Windows.Forms”)
fd.InitialDirectory=”c:\\”
fd.Filter=”txt files (*.txt)|*.txt |All files (*.*) |*.*”
fd.RestoreDirectory=true
fd.FilterIndex=2
fd.ShowDialog()
msgbox fd.FileName

Author: Mohan Kakarla