+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Import Excel Sheet (/Thread-Import-Excel-Sheet)
Import Excel Sheet - mdrumi - 05-12-2011
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 FunctionRE: Import Excel Sheet - supputuri - 05-13-2011
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