ADO to get data form closed Excel
 

I am using ado to get data from a closed excel file. The problem is the data
I am looking for is in different columns in different excel files. I only
know the colum heading (this could be in colum 10 in some files and in column
15 in other files.. I would not know the colum number before hand but I can
find out the column number by using ADO before trying to read the data)

I am using the follwoing code to get the data:

Sub GetDataFromHBWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21",
ActiveCell, False

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
==========================
The problem is I have to give the source range in "A1:A200" format. Is
there a way to pass the source range in Range(Cells(1,1),Cells(1,200)) format?

OR

How can I covert the Range(Cells(1,1),Cells(1,200)) fromat to
Range("A1:A200) format without writing extensive code?

OR

Is there any other way to get data form closed workbook? I would not know
the sorce range before hand. I have to search for the column with the
heading name.

ADO to get data form closed Excel
 

Hi,

Convert to xlA1 address (string) using the Address property of the range
object:
Dim Rg As Range
'... set the range
MsgeBox Rg.Address(False,False,xlA1)

in your case:
Range(Cells(1,1),Cells(1,200)) .Address(False,False,xlA1)

Regards,
Sebastien

"Mohan" wrote:

I am using ado to get data from a closed excel file. The problem is the data
I am looking for is in different columns in different excel files. I only
know the colum heading (this could be in colum 10 in some files and in column
15 in other files.. I would not know the colum number before hand but I can
find out the column number by using ADO before trying to read the data)

I am using the follwoing code to get the data:

Sub GetDataFromHBWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21",
ActiveCell, False

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
==========================
The problem is I have to give the source range in "A1:A200" format. Is
there a way to pass the source range in Range(Cells(1,1),Cells(1,200)) format?

OR

How can I covert the Range(Cells(1,1),Cells(1,200)) fromat to
Range("A1:A200) format without writing extensive code?

OR

Is there any other way to get data form closed workbook? I would not know
the sorce range before hand. I have to search for the column with the
heading name.