Import Excel Sheet

[mdrumi]

Hi
I am trying to create a function to dynamically add sheet and import data in the runtime datatable in qtp.The function is adding all sheets but not retrieving data from excel .Codes are below.Can anyone resolve the problem ?

Code:

importdata("C:\Book1.xls")

Function importdata(filepath)
Set xlapp = createobject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open(filepath)
allsheets = xlapp.Worksheets.Count
For i = 1 to allsheets
    sheetname = xlapp.Worksheets(i).Name
    datatable.AddSheet sheetname
    datatable.ImportSheet filepath,sheetname,sheetname

Next
    xlbook.Close
    xlapp.Quit

Set xlapp = nothing
Set xlbook = nothing

End Function

[supputuri]

Hi,

Just by adding a line of code we can achieve your goal.

Code:

importdata("C:\Book1.xls")

Function importdata(filepath)
Set xlapp = createobject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open(filepath)
allsheets = xlapp.Worksheets.Count
For i = 1 to allsheets
sheetname = xlapp.Worksheets(i).Name
datatable.AddSheet sheetname
datatable.ImportSheet filepath,sheetname,sheetname

Next
xlbook.Close
xlapp.Quit

Set xlapp = nothing
Set xlbook = nothing
'need this line of code, the possible reason is as you are using the excel object alredy the file is in use so it could not load the data (may be I am not sure) but by using the below LOC you can import all the sheets.
DataTable.Import filepath
End Function

Let me know if you need any info.