How To open Password Protected Excel sheets using Vb Script

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\JayKrishna\Desktop”,”4.xls”,”test123″,”test123″)
‘Call UnprotectXL(“C:\Documents and Settings\JayKrishna\Desktop”,”4.xls”,”test123″,”test123″)

Comments

PDF Automation in QTP

The most challenging issue with PDFs is that it could be of any kind, not just a tabular data; it could have plain text, images or even forms to fill up. So this makes a tester’s life a bit difficult, never mind, we will definitely find an easy of do it… Although there are already some better approaches we have to deal with PDF documents but I found many of us are facing so many difficulties using this. There are lots of queries coming at QTP forums asking for an easy way of doing it with PDFs. keeping those in my mind I started c...

Convert JSON to XML using QTP/UFT/VBScript

Sample Code : Dim strPage,strJSON,objIE strPage = "C:\Jay\JLoader.html" Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True objIE.Navigate2 strPage While objIE.Busy : Wend strJSON = "{""FirstName"":""Jay"", ""LastName"":""Krishna""}" Set objWin = objIE.document.parentWindow objWin.execScript "var jsonStr2XML = function(strJSON) { return json2xml(JSON.parse(strJSON));};" Msgbox oWin.jsonStr2XML(strJSON) objIE.Quit In Detail: Converting The most popular data interchange format JSON(JavaScript Object Notation) to XML using QTP/UFT. Parsing JSON in UFT could be a challenge so we will use JavaScript in UFT to make it perfect. SO We need : Java Script API - To Convert JSON to XML JavaScript Files : ...

Download Test Resource From QC Using QTP

'########################################################################### '* Function Name: QCGetResource '* Designer: Jay '* Date 09-May-2012 '* This script will Download QC Test Resource to a local dir '########################################################################### Function QCGetResource(resourceName,saveTo) Set qcConn = QCUtil.QCConnection Set oResource = qcConn.QCResourceFactory Set oFilter = oResource.Filter oFilter.Filter("RSC_FILE_NAME") = resourceName Set oResourceList = oFilter.NewList If oResourceList.Count = 1 Then Set oFile = oResourceList.Item(1) oFile.FileName = resourceName oFile.DownloadResource saveTo, True End If Set qcConn = Nothing Set oResource = Nothi...

compare Two Text files using Vb Script

Public Function CompareFiles (FilePath1, FilePath2) Dim FS, File1, File2 Set FS = CreateObject(“Scripting.FileSystemObject”) If FS.GetFile(FilePath1).Size <> FS.GetFile(FilePath2).Size Then CompareFiles = True Exit Function End If Set File1 = FS.GetFile(FilePath1).OpenAsTextStream(1, 0) Set File2 = FS.GetFile(FilePath2).OpenAsTextStream(1, 0) CompareFiles = False Do While File1.AtEndOfStream = False Str1 = File1.Read(1000) Str2 = File2.Read(1000) CompareFiles = StrComp(Str1, Str2, 0) If CompareFiles <> 0 Then CompareFiles = True Exit Do End If Loop File1.Close() File2.Close() End Function Return value: The function returns 0 or False if the two files are identical, otherwise True. Example: File1 = “C:\countries\apple1.jpg” File2 = “C:\countries\apple3.jpg” If CompareFiles(File1, File2) = False Then MsgBox “Files are identical.” Else MsgBox “Files are different.” End If Source: Mercury Forum’s KB articles

CreateImageFromClipBoard using QTP

'-------------------------------------------------------------------------' Method : CreateImageFromClipBoard' Author : Jai Purpose : It gets the clipboard image and convert as a image file.' Parameters: FileName - String, contains the BMP file name' iIndex - Integer, contains the Worksheet index' Returns : String. The replaced file name it gives.' Caller : - Nil' Calls : - Nil' ------------------------------------------------------------------------- Sub CreateImageFromClipBoard(sFileName) Dim wshShell,ShellReturnCode, sCmdExec Set WshShell = WScript.CreateObject("WScript.Shell") sCmdExec = "D:\autostuff\i_view32.exe /silent /clippaste /convert="& sFileName ShellReturnCode = WshShell.Run(sCmdExec, 1, True) End Sub

Quick Learn Automation

Search This Blog