Heres my query

I have a closed work book I wish to extract results from into my open
workbook

1. I know the path where the closed spreadsheet file lives
2. I know the sheet name and cell that i want to read into my recordset

however!! NB This sheet is sent to me by someone external and there are
no named ranges in it.

Ive found a code snippet that I thought might suit

Sub GetDataFromClosedWorkbook(ByVal SourceFile As String, _
ByVal SourceRange As String, _
ByVal TargetRange As Range,
ByVal IncludeFieldNames As Boolean)

..
..
..
' it set up the connection to my file

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
dbConnection.Open dbConnectionString

' and calls the execute method to run the query
Set rs = dbConnection.Execute("[" & SourceRange & "]")
..
..
End Sub


The Call works fine provided we use parameters like
Call GetDataFromClosedWorkbook("C:\mySS.xls", "F5:H7", Range("Target"),
False)


But I need to be more specific instead of saying "F5:H7" I need to say
WorkSheets("mySheetName").Range("F5:H7")

ive tried using the .Address() method to return a string
i.e. WorkSheets("mySheetName").Range("F5:H7").Address()

but to no avail

NB: The ADODB method dbConnection.Execute(.....)
expects a string


Any Ideas

Thanks

Graham









*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!