Have you tried this:
Call GetDataFromClosedWorkbook("C:\mySS.xls", "'mySheetName'!F5:H7",
Range("Target"), False)

Might work!


"grahamd" wrote:

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, sheet name and cell name that i want to read
into my recorset

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