Importing From excel

[Oded Kovach]

Hello there

I have probram that imports from excel

I'm use Docmd.TransfareSpreadsheet action to do this

In the default this action import only the first worksheet.

Is there a way to import other worksheets from the excel file

and is there a way to know how many worksheets are on excel file and what is
their name?

any help would be useful

 

[Dominic Olivastro]

I prefer using the Excel Object Model, like this

Dim x as Excel.Application
dim w as Excel.Workbook
dim s as Excel.Worksheet

set x = new excel.application
set w = x.Workbooks.open (<File.xls>, ...)

w.Worksheets.count --> is the number of worksheets in <File.xls>

for i = 1 to w.Worksheets.count
set s = w.Worksheeets (i)
s is now the object of the worksheet number "i"
s.Cells (row, col) is cell (row, col) in worksheet i, etc.
next i

--
Dominic Olivastro
CHI Research, Inc

web: http://www.ChiResearch.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

 

[Dominic Olivastro]

I should have mentioned that there are definite drawbacks with the Excel
Object Model. For one, make sure you call ...

x.close
set x = nothing

.... at the end, otherwise your PC may freeze. If you crash before you get
to this code, you should just reboot to avoid any other problems.

Dom

--
Dominic Olivastro
CHI Research, Inc

web: http://www.ChiResearch.com
fax: 1-856-546-9633
voice: 1-856-546-0600 (ext 224)
email: (e-mail address removed)

 

[Oded Kovach]

Thankes Dominic

The automation is quite better then the Docmd.Transferdatasheet

The problem that i stuck with that force me to use the Transfaredatasheet
was that it is faster for importing large number of data such as excel
sheets that one of them have at least 1000 rows.

The other thing is that the TransfareDatasheet always import the intire
sheet while i shout first find out my self the sheet size.

Is there better ways to deal with these two problems with excel automation?

hope that you can help me