ADO to get data form closed Excel

[Guest]

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.

 

[Guest]

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