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

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
>