Quick test professional

QTP Tips QTP codes QTP Faqs and more

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

Advertisements

November 27, 2008 Posted by | Excel Automation | 15 Comments

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

October 21, 2008 Posted by | Excel Automation, Uncategorized | , , | 18 Comments

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

June 9, 2008 Posted by | Excel Automation, Uncategorized | , , , | 25 Comments

Compare 2 Excel sheets cell by cell

This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color  in the first sheet.
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and

Settingsmohan.kakarlaDesktopDocs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

   For Each cell In objWorksheet1.UsedRange
       If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3’Highlights in red color if any changes in cells
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next

set objExcel=nothing
 Author: Mohan Kumar Kakarla

February 27, 2008 Posted by | Excel Automation | , , , , , , , , , | 87 Comments

Excel Sorting(Ascending , Descending) 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:Documents and Settingsmohan.kakarlaDesktopDocs1.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 Colum :

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:\Documents and Settings\mohan.kakarla\Desktop\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(“A1”)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Reference: MSDN

February 26, 2008 Posted by | Excel Automation | , , , , , , , , , , , , | 18 Comments

DELETE ROWS FROM XL SHEET

DELETE ROWS FROM

 XL SHEET

 Public Function BIP_xlsDeleteRowRange (sSrcPath, sDestPath, sStartRow, sEndRow) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False

‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Rows(sStartRow +”:”+ sEndRow).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function 

February 14, 2008 Posted by | Excel Automation | , , , , , , , | 14 Comments

DELETE COLUMNS FROM XL SHEET

DELETE COLUMNS FROM

 XL SHEET

Public Function BIP_xlsDeleteColumnRange (sSrcPath, sDestPath, sStartCol, sEndCol) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False
‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Columns(sStartCol + “:” + sEndCol).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function

February 14, 2008 Posted by | Excel Automation | , , , , , , , , | Leave a comment

ADODB CONNECTION TO READ DATA FROM EXCEL SHEET

Function GetContentFromDB (strFileName, strSQLStatement)

 

Dim objAdCon, objAdRs

 

Set objAdCon = CreateObject(“ADODB.Connection”)

 

objAdCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=”&strFileName & “;Readonly=True”

If Err <> 0 Then

      Reporter.ReportEvent micFail,”Create Connection”, “[Connection] Error has occured. Error : ” & Err

      Set obj_UDF_getRecordset = Nothing

   Exit Function

End If

Set objAdRs = CreateObject(“ADODB.Recordset”)

objAdRs.CursorLocation=3                        ‘ set the cursor to use adUseClient – disconnected recordset

objAdRs.Open strSQLStatement, objAdCon, 1, 3

 

MsgBox objAdRs.fields(4).name

While objAdRs.EOF=false

   For i=0 to objAdRs.Fields.count

Msgbox objAdRs.fields(i)

Next

objAdRs.moveNext

Wend

 

If Err<>0 Then

      Reporter.ReportEvent micFail,”Open Recordset”, “Error has occured.Error Code : ” & Err

      Set obj_UDF_getRecordset = Nothing

     Exit Function

End If

 

Set objAdRs.ActiveConnection = Nothing

 

objAdCon.Close

Set objAdCon = Nothing

 

 

 

End Function

 

Set rsAddin = GetContentsFromDB(“C:\Documents and Settings\mohank\Desktop\Login.xls”, “Select * from [Login$]”)

Author:Mohan Kakarla

February 14, 2008 Posted by | Excel Automation | , , , , , , , , | 34 Comments