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.