Hello
Thanks for the suggestions. I was indeed overlooking the most obvious
thing. I had the workbook open but I had never referenced the worksheet
object.
This is what I had missed:
Set myworksheet = mybook.Worksheets(1) ' this gives me a worksheet object.

That statement corrects the problem with the additional 'myworksheet'
specified object.
See the example below:
..Fields("StartDate") = .Range("B14").Value ' this will
grab the current worksheet data
..Fields("StartDate") = myworksheet.Range("B14").Value ' this will grab the
data from the (looped to) workbook and the correct required worksheet.

Thanks for the suggestion about ADO. I did indeed use ADO to pull the data
from the spreadsheets. I think I needed to open each workbook as the
spreadsheet data is in a template but not in database form. This meant data
was usually in a given cell or group of cells but the cells were not in
database form and were not in named ranges. I put logic to loop thru the
variable rows of data and grab the correct required data. There will end up
being about 350 different spreadsheets that I will take the data from. I
put it into Access and then I am just using a couple of quick querys to
clean up the data. When the data is clean I am exporting to a datawarehouse
in SQL. I am using Access as an intermediate so that 'checkers' can
validate the data in a quick Access screen before it goes into SQL.

Thanks for your help and if you have any questions or want to see the rest
of the solution repost here and I will try to answer.
Thanks
Tom




"Tom" wrote in message
...
I am trying to loop thru a group of files and get data from each sheet. I
do the looping fine and open each file with out a problem thanks to some
example from the forum(THANKS!). I am having a problem when I read the data.
I get the data from the current sheet and not from the sheet in the
spreadsheet I have opened. The spreadsheet does open but I keep reading the
data from the current sheet and not the open spreadsheet. How should I
correct this?

Thanks for the ideas and support!
Tom

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim basebook As Workbook
Dim mybook As Workbook
Dim a As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim test As Variant

SaveDriveDir = CurDir
MyPath = "E:\Excel\test"
FNames = Dir("*.xls")

Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

With mybook.Worksheets(1)

...............rest of code looping etc....
...............This works just cant get to the correct sheet.