Hi Ron,
Your examples are quite useful and I did checked them out before posting my
questions. I downloaded your code file and found it very interesting. But
unfortunately, all your examples open the source workbook which is what I try
to avoid.
Mr. Erlandsen has many examples which use ADO and copy (or transfer) the
data without opening the workbooks. Unfortunately again, all of them suppose
that the fields names are present on the first data row.
I tried to change the code in different ways but nothing works. I either
loose the 1st row of data or get that row as field names.
Here is some code that I adapted from one version of his examples:
' **************************************************
Sub DoGetData()
' Jac Tremblay - 2007-02-09
Dim strFile As String
Dim strRange As String
strFile = " C:\Data.xls"
strRange = "BdData"
GetDataFromClosedWorkbook strFile, strRange, Range("A1"), False
End Sub
' **************************************************
' If you want to import a lot of data from a closed workbook you can
' do this with ADO and the macro below. If you want to retrieve data
' from another worksheet than the first worksheet in the closed workbook,
' you have to refer to a user defined named range. The macro below can
' be used like this (in Excel 2000 or later):
' GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", _
' "A1:B21", Active - Cell, False
' GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", _
' "MyDataRange", Range("B3"), True
' **************************************************
Sub GetDataFromClosedWorkbook(SourceFile As String, _
SourceRange As String, TargetRange As Range, _
IncludeFieldNames As Boolean)
' Requires a reference to the Microsoft ActiveX Data Objects library.
' If SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile.
' If SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile.
' SourceRange must include the range headers.
'
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 ' Open the database connection.
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 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 ' Close the database connection.
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub
' **************************************************
I got the code from
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado
Thank you Ron for your reply. I still hope to find an answer to my problem.
When I do, I will share it with the community.
