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.

You will need to post the rest of your code so that we can see what i
wrong in your cod

--
Message posted from http://www.ExcelForum.com

Have you included the line

FNames = Dir

within your loop? Otherwise, you'll keep openeing the same workbook
each time.

BTW your function name suggests you are using ADO. Are you aware that
you can use ADO to access the data in your workbooks (subject to them
being in 'database' format i.e. rows of columns) without having to
open them? For 150 workbooks, the performance difference will be
considerable if you do no need to open the workbooks.

--

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

Using ADO, you could use syntax like this to update your MS Access
database without opening the workbooks (assumes you are connected to
the MS Access database):

To append data to existing table

strSql = "INSERT INTO MyTable (ColA, ColB)" & _
" SELECT MyCol1, MyCol2" & _
" FROM [Excel 8.0;database=E:\Excel\test\" & _
FNames & ";].[Sheet1$]"

To create a new table based on Excel data:

strSql = "SELECT MyCol1, MyCol2" & _
" INTO MyNewtable" & _
" FROM [Excel 8.0;database=E:\Excel\test\" & _
FNames & ";].[Sheet1$]"

--


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

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.