Hi,

I need to import all sheets from a some closed workbooks to my activ
workbook

some like:

row1: workbookname - worksheet_name1
row2-xxx: --all content of worksheet(1)--

rowXX: workbookname - worksheet_name2
rowXX: --all content of worksheet(2)--

All in the same worksheet, one below the other.

I´m using a sub:

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange A
String, _
TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};"
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(2, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub

InvalidInput:
MsgBox "Error opening file." & vbCrLf & SourceFile & " not found!"
vbExclamation, "Import"

End Sub

But .. this only form for workSheet(1)

some idea?

Tnx

--
johnpetrus
-----------------------------------------------------------------------
johnpetrusa's Profile: http://www.excelforum.com/member.php...fo&userid=2395
View this thread: http://www.excelforum.com/showthread.php?threadid=37577