Excel interview questions

Working with Excel Sheets - create, open, modify, save, close!!

Getting value from an existing excel sheet
Dim oExcel, oWB, oSheet, getVal

Set oExcel=CreateObject("Excel.Application")

Set oWB=oExcel.Workbooks.Open("C:\sri.xls")

Set oSheet=oWB.WorkSheets("Sheet1")

oExcel.Visible=TRUE

getVal=oSheet.Cells(1,1).Value

print getVal

oWB.Close

Set oExcel=Nothing



Inserting value to an existing excel sheet

Dim oExcel, oWB, oSheet, setVal

Set oExcel=CreateObject("Excel.Application")

Set oWB=oExcel.Workbooks.Open("C:\sri.xls")

Set oSheet=oWB.WorkSheets("Sheet1")

oExcel.Visible=TRUE

setVal="ValueInserted"

oSheet.Cells(4,1).Value=setVal

print getVal

oWB.Save

oWB.Close

Set oExcel=Nothing



Create a new excel sheet, insert values and save it..

Dim oExcel, oWB, oSheet, setVal

Set oExcel=CreateObject("Excel.Application")

Set oWB=oExcel.Workbooks.Add

Set oSheet=oWB.WorkSheets("Sheet1")

oExcel.Visible=TRUE

setVal="ValueInserted"

oSheet.Cells(4,1).Value=setVal

print getVal

oWB.SaveAs("C:\sri.xls")

oWB.Close

Set oExcel=Nothing

Working with Excel Sheets - Add new sheet

This code demonstrate how to add new sheet to existing excel file
and rename it at runtime

Dim oExcel, oWB,oSheet, getVal

Set oExcel=CreateObject("Excel.Application")

oExcel.visible=True

oExcel.DisplayAlerts= False   'this will not allow alerts to be displayed



Set oWB=oExcel.Workbooks.Open("C:\sri.xls")

Set oSheet1=oWB.Sheets.Add  'a new sheet is added with default name i.e.Sheet4 etc.

oSheet1.Name="QTPSchools" 'rename newly added sheet

oSheet1.cells(1,1).value="Welcome" 'enter value 

oWB.Save

oWB.Close

Set oExcel=Nothing

Using excel sheet as database table

Excel sheet can be used as a database for the parameterization purpose.
Following code demonstrate how to connect and consider excel sheet as database table.

This might be usefull while working with databases. You can export database table into excel (one time) and then work on excel as database.

Dim objCon, objRecordSet, strExlFile, colCount, row, i


Set objCon = CreateObject("ADODB.Connection")

Set objRecordSet =CreateObject("ADODB.Recordset")

strExlFile = "C:\abhikansh.xls"

objCon.Open "DRIVER={Microsoft Excel Driver(*.xls)};DBQ=" &strExlFile & ";Readonly=True"

strSQLStatement = "SELECT * FROM [Sheet2$]"

objRecordSet.Open strSQLStatement, objCon 'create recordset

colCount = objRecordSet.Fields.count    'No of columns in the table

While objRecordSet.EOF=false

    row=""

    For i=0 to colCount-1

        row=row&"    "& objRecordSet.fields(i)

    Next   

     Print row

    objRecordSet.moveNext

Wend

Set objRecordSet = Nothing

objCon.Close

Set objCon = Nothing
Screen-shots of sample excel and output are following 


-                                                                                




Following code demonstrate how to count number of rows inExcel without opening it - using vbscipt in QTP.

 Set objExcel=CreateObject("Excel.Application")

Set objWB=objExcel.WorkBooks.Open("C:\abc.xls")

Set objSheet=objWB.WorkSheets(1)

msgbox objSheet.usedrange.rows.count

Set objExcel=Nothing
1)Find the 'String' from excel sheet and save to another sheet.But we 
  don't know the string column number and row number?
Option explicit
Dim exo, wbo, wso1, wso2, nor, noc, i, j, x, y
'Open a Excel file
Set exo=CreateObject("excel.application")
exo.Visible=true
Set wbo=exo.Workbooks.Open("C:\sample.xls")
Set wso1=wbo.Worksheets("sheet1")
Set wso2=wbo.Worksheets("sheet2")
nor=wso1.usedrange.rows.count
noc=wso1.usedrange.columns.count
'Varifying excel sheet strings
For i=1 to nor step 1
 For j=1 to noc step 1
  x=wso1.cells(i,j)
  x=cstr(x)
  If strcomp(x,"Testing")=0 Then
   wso2.cells(1,1)=x
  End If
 Next
Next
'Close excel s/w and Destroy the objects
exo.Quit
Set wso1=Nothing
Set wso2=Nothing
Set wbo=Nothing
Set exo=Nothing