Import Excel Sheet

[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.