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

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 : http://goessner.net/download/prj/jsonxml/j

Read Outlook mail attachment and Body using Vb Script or QTP

Set olApp = CreateObject("Outlook.Application") Set olns = olApp.GetNameSpace("MAPI") Set ObjFolder = olns.GetDefaultFolder(6) j = 0 For each item1 in ObjFolder.Items iattachCnt = item1.Attachments.Count Print "Attachments Count: " & iattachCnt For i = 1 to iattachCnt Print "FileName : " & item1.Attachments(i).FileName Print "Display Name: " & item1.Attachments(i).DisplayName Print "Size: " & item1.Attachments(i).Size Next Print " Body : " & item1.body Print "--------------------------------------Mail Num - " & j & " -----------------------------------------------" j = j+1 Next

Excel Sorting By Rows and Columns

Excel Sorting By Row: Const xlAscending = 1 Const xlNo = 2 Const xlSortRows = 2 Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open(“C:\Jay\Docs1.xls”) Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Cells(1,1).activate Set objRange = objExcel.ActiveCell.EntireRow objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows set objExcel=nothing Excel Sorting By Column : Const xlAscending = 1′represents the sorting type 1 for Ascending 2 for Desc Const xlYes = 1 Set objExcel = CreateObject(“Excel.Application”)’Create the excel object objExcel.Visible = True’Make excel visible Set objWorkbook = _ objExcel.Workbooks.Open(“C:\Jay\Docs1.xls”)’Open the document Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 … Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank Set objRange2 = objExcel.Range

How to Read or Select Context Menu or Right Click Menu using QTP.

Select The Item in Right Click Menu or Context Menu: Window("sampleWindow").WinMenu("MenuObjType:=1).Select"File;New" Here MenuObjtype can be 1 r 2 r 3 .......n Check wether the Item is Exist or Not: If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("1","Exist") Then Msgbox"Exist" Else Msgbox"Does Not Exist" End If Or If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("File","Exist") Then Msgbox"Exist" Else Msgbox"Does Not Exist" End If Get the Items in Context Menu: For i = 1 to 10 Print Window("sampleWindow").WinMenu("MenuObjType:=" & i).GetItemProperty("1","Label") Then Next

How to Download a file using VbScript

Following is the code to download a file using Vbscript, without using QTP This code uses the HTMLDom and URLDownloadToFile method from urlmon API. Since VBScript does support calling Native API methods directly, here I am using Excel macro to declare a function for the urlmon API and running the macro by Excel API from VBscript Step1: Create a new excel and open the visual basic editor, Insert Module and paste the following code the Module, save the excel file Private Declare Function URLDownloadToFile Lib “urlmon” Alias _ “URLDownloadToFileA” ( _ ByVal pCaller As Long, ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserved As Long, _ ByVal lpfnCB As Long) As Long Sub FileSave(strUrl, Des) r = URLDownloadToFile(0, strUrl, Des, 0, a)

Quick Learn Automation

Search This Blog