Extract data from closed workbooks

[juergenkemeter]

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

[Ecniv]

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.

[Webtest]

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.

[juergenkemeter]

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

[Webtest]

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)

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