Extract data from closed workbooks

Hi!

I have a folder containing about 300 Excel workbooks. These contain the same Sheet names. ('Sequence Data' and 'Sequence Analysis')

Each Sheet is divided into several sectors, i.e. rows, with different headings.
I enclosed two examples.
I must collect all data, written in these different sectors of the sheets, and write them in a workbook.

My idea is to loop through the Folder, and open each workbook.
Then look into their sheets, export available data written in sectors.

Taking the worksheet 'Sequence Data' as an example:
It contains the data sectors 'Primary Sequences', 'Derived Sequences', and 'Electronic Sequences' as you can see in the enclosed files to this thread.
- The source data in 'Primary Sequences' is written in Range(B6:M9)
- 'Derived Sequences' - Range(B11:M14)
- 'Electronic Sequences' - Range(B16:M20)

Now I want to collect all data written in these sectors, into three new target worksheets.

My problem is to process many workbooks, and write their output into a corresponding target sheet,.

Perhaps there is a way to combine the two code examples, from this Link:
http://www.exceltip.com/st/Read_info...Excel/473.html

The first and the second approach (loop and manual selection of source ranges).


Aem...Merry Christmas By the Way!!

Cheers
Juergen

Make a bit of code to loop through all the files (xls only)
Create the new target workbook (hold a reference to it)
Create a destination worksheet (hold a ref to it)
Loop through the files
- open the file
- grab data and write to specific columns in dest worksheet
- move pointer to output row on dest sheet
Once all files are processed, the data should be in the sheet. Note : you'll need to account for the 65000 rows (if you are importing more than that) into the code.

You should be able to use an ADO or DAO Connection to each workbook. You open a connection instead of opening the Workbook itself. I don't know if there is any performance difference. I have examples of opening connections to Access from Excel if you are interested.

Hi,
If you all could provide some example VBA code, that would be very helpful.
I am still learning VBA via the Method 'record Macro - look at generated code - test code -', which takes sometimes very long.

Cheers
Jürgen

I haven't played with this for a very long time, and this process is quite tricky (almost no documentation!). Here are the Access > to > Excel examples: (Excel '97)

Code:

---

Sub ADOfromAccesstoExcel()
'
' BE SURE TO: add a reference to MS ActiveX Data Objects x.x Object Library
'

Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim mySQL As String
Dim TargetRange As Range

DBFullName = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
TableName = "Products"
FieldName = "Category"
mySQL = "SELECT * FROM Products WHERE Category = 'Beverages'"
Dim cn As ADOB.Connection
Dim rs As ADOB.Recordset
Dim longRowIndx As Long

Set TargetRange = TargetRange.Cells(1, 1)
Set cn = New ADOB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; DataSource=" & DBFullName & ";"
Set rs = New ADOB.Recordset
With rs
  .CursorType = adOpenStatic
  .LockType = adLockOptimistic
  .Open TableName, cn, , , adCmdTable  ' This is ALL records
 
  ' Here is Filtered Records:
  ' .Open "SELECT * FROM & TableName & " WHERE & FieldName & _
  '      'MyCriteria'", cn, , , adCmdTxt
 
  If Not .BOF Then .MoveFirst
  longRowIndx = 0
  While Not .EOF
    TargetRange.Offset(longRowIndx, 0).Formula = Fields(FieldName)
    longRowIndx = longRowIndx + 1
    .MoveNext
  Wend
End With
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub
Sub DAOfromAccesstoExcel()
' 030311.0949    Arthur Du Rea
' BE SURE TO: add a reference to MS DAO 3.51 Object Library
'
' Pull a filtered Record Set from a closed Access Data Base
'
Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim CatTypeName As String
Dim dateStr As String
Dim myFilter As String
Dim TargetRange As Range
DBFullName = _
"G:\Sales & Marketing\Non-Perishables\Grocery Merchandise\MerchSysMgr\AccessZpix\ZPIX_Archive.mdb"
dateStr = _
  InputBox("Input W/E Date for Ad - m/d/yyyy", "ENTER DATE AS m/d/yyyy", "1/4/2003")

myFilter = _
  "SELECT * FROM Weekly_Ad_Archive_2003 WHERE Date_Item_End = #" _
  & dateStr & "#"

' MsgBox (myFilter)

Dim db As Database
Dim rs As Recordset
Dim longRowIndx As Long
Dim intColIndx As Integer

' Clear the Active Worksheet
Cells.Select
Selection.Clear
Set TargetRange = ActiveSheet.Range(Cells(1, 1), Cells(1, 1))

' Actual Data Access
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(myFilter, dbReadOnly) ' Filter Records

'Write Field Names into the sheet columns
For intColIndx = 0 To rs.Fields.Count - 1
  TargetRange.Offset(0, intColIndx).Value = rs.Fields(intColIndx).Name
Next

'Write Recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
'Close out the operation
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

---