How can I import all sheet from an Excel file to a able?

If you want to store test data files from external storage, such
Excel
Import
Sheet
Page
Datatable
And Judge
Datatable
Whether the specified
Sheet
If not, add
Sheet
. For example
Datatable
Object
Getsheet
,
Addsheet
And
Excel
Of
Com
Object programming:

 

Function importallsheets (byval filename)

Dim
Oexcel, obook

 

'Launch
Excel

Set
Oexcel = GetObject ("", "Excel. application ")

 

'Open
The file in Read Only Mode

Set
Obook = oexcel. workbooks. Open (filename, true)

 

'Enumerate
Through all the sheets present in the file

For
Each osheet in obook. worksheets

 

'Check
If a datatable with current name already exists

If
Not ifdatasheetexist (osheet. Name) then

'Able able
Cannot be imported if the sheet does not exist

Datatable. addsheet
Osheet. Name

End
If

 

'Import
The sheet

Datatable. importsheet
Filename, osheet. Name, osheet. Name

Next

 

Set
Obook = nothing

 

'Quit
Excel

Oexcel. Quit

Set
Oexcel = nothing

End Function

 

 

Function ifdatasheetexist (byval sheetname)

Ifdatasheetexist
= True

On
Error resume next

Dim
Otest

Set
Otest = datatable. getsheet (sheetname)

If
Err. number then ifdatasheetexist = false

On
Error goto 0

End Function