Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook - know sheet name and cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Names is Defined by Cell value on Sheet 1 (named Summary | Excel Discussion (Misc queries) | |||
Consolidation of data from cell in active sheet of closed workbook | Excel Worksheet Functions | |||
How to refenence ranges on a closed workbook | Excel Discussion (Misc queries) | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
Recordset or Object is closed HELP PLEASE! | Excel Programming |