Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
the SourceRange must have a format like
"sheet1$A2:D100" note the $ is used as the pipe. the rangeref must be A1 relative notation. "Build like this... Set rngQry = Range("A1:d100") strqry = rngQry.Worksheet.Name & "$" & _ rngQry.Address(0, 0, xlA1) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool graham d 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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
Hi graham
Maybe usuful http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "graham d" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
Hi graham
Try this VB ADO Remember to Reference Microsoft ActiveX Data Objects 2.x (I like 2.5) Jet will not return both strings and numbers in the same recordset so use rs for one, rs1 for the another. You can create as many as you like Private Sub CommandButton1_Click() Dim DB_NAME As String Dim DB_CONNECT_STRING As String 'You must fully quality the path to your file 'I like to locate the file with properties and 'copy paste - ADO really bitches if not perfect 'On this computer it was as follows DB_NAME = ("C:\Documents and Settings\") _ & ("The Cat Man\My Documents\ProgressBar.xls") DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0" _ & ";Data Source=" & DB_NAME _ & ";Extended Properties=""Excel 8.0;HDR=Yes;"";" 'Create the connection Dim cnn As New ADODB.Connection Set cnn = New Connection cnn.Open DB_CONNECT_STRING 'Test to see if we are connected If cnn.State = adStateOpen Then MsgBox "Welcome to! " & DB_NAME, vbInformation, _ "Good Luck TK" Else MsgBox "Sorry. No Data today." End If 'Create the recordset Dim Rs As ADODB.Recordset Set Rs = New Recordset Dim Rs1 As ADODB.Recordset Set Rs1 = New Recordset 'Determines what records to show Dim strSQL As String strSQL = "Select * from [Sheet1$B9:B20]" strSQL1 = "Select * from [Sheet1$A9:A10]" 'Retreive the records Rs.CursorLocation = adUseClient Rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic Rs1.Open strSQL1, cnn, adOpenStatic, adLockBatchOptimistic 'Copy the records to the worksheet Worksheets("Sheet2").Range("E2").CopyFromRecordset Rs Worksheets("Sheet2").Range("D2").CopyFromRecordset Rs1 'Close the connection cnn.Close Set cnn = Nothing 'Destroy the Recordset Set Rs = Nothing Set Rs1 = Nothing Exit Sub End Sub Good Luck TK graham d 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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
"TK" wrote ...
Jet will not return both strings and numbers in the same recordset so use rs for one, rs1 for the another. Where did you get this idea? This is clearly incorrect. A quick demo: Sub Test() Dim Con As Object Set Con = CreateObject("ADODB.Connection") With Con ' Create Jet data source .Open "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Test.xls;" & _ "Extended Properties='Excel 8.0';" ' Create table with numeric and text columns .Execute "" & _ "CREATE TABLE MyTable (" & _ " MyNumberCol FLOAT NULL," & _ " MyTextCol VARCHAR(255) NULL);" ' Create data .Execute "" & _ "INSERT INTO MyTable" & _ " (MyNumberCol, MyTextCol)" & _ " VALUES (55.55, 'test');" ' Open recordset Dim rs As Object Set rs = .Execute("" & _ "SELECT MyNumberCol, MyTextCol" & _ " FROM MyTable;") MsgBox "" & _ "MyNumberCol is " & _ TypeName(rs.fields("MyNumberCol").Value) & vbCrLf & _ "MyTextCol is " & _ TypeName(rs.fields("MyTextCol").Value) rs.Close .Close End With End Sub Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
Retraction, Clarification, Whatever Obviously you are correct in your admonishment and any even the casual student of SQL know that all sorts of selection and action queries can be sent to a DB. My statement should have read: With the code Im posting I was unable to return both numbers and text from the same column so I simply sent a second query to the workbook. That being qualified, the code is fast, stable, free and answers the specific question. " have a closed work book I wish to extract results from into my open workbook" TK "Jamie Collins" wrote: "TK" wrote ... Jet will not return both strings and numbers in the same recordset so use rs for one, rs1 for the another. Where did you get this idea? This is clearly incorrect. A quick demo: Sub Test() Dim Con As Object Set Con = CreateObject("ADODB.Connection") With Con ' Create Jet data source .Open "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Test.xls;" & _ "Extended Properties='Excel 8.0';" ' Create table with numeric and text columns .Execute "" & _ "CREATE TABLE MyTable (" & _ " MyNumberCol FLOAT NULL," & _ " MyTextCol VARCHAR(255) NULL);" ' Create data .Execute "" & _ "INSERT INTO MyTable" & _ " (MyNumberCol, MyTextCol)" & _ " VALUES (55.55, 'test');" ' Open recordset Dim rs As Object Set rs = .Execute("" & _ "SELECT MyNumberCol, MyTextCol" & _ " FROM MyTable;") MsgBox "" & _ "MyNumberCol is " & _ TypeName(rs.fields("MyNumberCol").Value) & vbCrLf & _ "MyTextCol is " & _ TypeName(rs.fields("MyTextCol").Value) rs.Close .Close End With End Sub Jamie. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
"TK" wrote ...
Jet will not return both strings and numbers in the same recordset so use rs for one, rs1 for the another. Where did you get this idea? This is clearly incorrect. My statement should have read: With the code Im posting I was unable to return both numbers and text from the same column so I simply sent a second query to the workbook. I don't understand. Your second query references a second column i.e. strSQL = "Select * from [Sheet1$B9:B20]" strSQL1 = "Select * from [Sheet1$A9:A10]" so it cannot achieve your stated goal of returning 'both numbers and text from the same column'. If you have numbers and text in your column but you are getting null values, Jet may be determining the data type using the 'majority type' of the rows scanned instead of all values being coerced as 'Text'. For details on the relevant registry settings and how you may change them in your favor, see: http://www.dicks-blog.com/excel/2004...al_data_m.html Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
..The OP stated: €ś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€ť strSQL1 polls a cell, and with the proper notation will poll any cell you like, any column you like which by definition (€ś1 of 1€ť) would be the majority data type. But you know all this, so why not append or suggest ways to improve the procedure or offer one of your own. I merely offered a little plug and play code to get the OP who was trying to learn to write to a closed workbook moving in the right direction. You suggest to use my code he must adjust the Jet registry keys. Who will do that? €śdetails on the relevant registry settings and how you may change them in your favor, see€ť I abdicate to the readers. TK "Jamie Collins" wrote: "TK" wrote ... Jet will not return both strings and numbers in the same recordset so use rs for one, rs1 for the another. Where did you get this idea? This is clearly incorrect. My statement should have read: With the code Im posting I was unable to return both numbers and text from the same column so I simply sent a second query to the workbook. I don't understand. Your second query references a second column i.e. strSQL = "Select * from [Sheet1$B9:B20]" strSQL1 = "Select * from [Sheet1$A9:A10]" so it cannot achieve your stated goal of returning 'both numbers and text from the same column'. If you have numbers and text in your column but you are getting null values, Jet may be determining the data type using the 'majority type' of the rows scanned instead of all values being coerced as 'Text'. For details on the relevant registry settings and how you may change them in your favor, see: http://www.dicks-blog.com/excel/2004...al_data_m.html Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
"TK" wrote ...
I merely offered a little plug and play code to get the OP who was trying to learn to write to a closed workbook moving in the right direction. I merely challenged some statements you made which I saw as being incorrect. You suggest to use my code he must adjust the Jet registry keys. I thought *you* may have been having problems but it seems I thought wrong. €śdetails on the relevant registry settings and how you may change them in your favor, see€ť Who will do that? Unless the administrator has locked them down, why not change them in one's favor if they are causing problems? Isn't that the point of having these values in the registry rather than hard coding them? why not append or suggest ways to improve the procedure or offer one of your own. I didn't want to answer a question that has already been answered (I assume; I haven't checked your code). But since you've laid down the gauntlet, here's my attempt: Sub test() Dim vntResult As Variant vntResult = GetCellContentsFromClosedWorkbook( _ "C:\Tempo\db.xls", "Sheet1", "A2") If vntResult = vbEmpty Then MsgBox "Error fetching cell contents." Exit Sub End If If IsNull(vntResult) Then MsgBox "Result is null." & vntResult Else MsgBox "Result=" & CStr(vntResult) End If End Sub Public Function GetCellContentsFromClosedWorkbook( _ ByVal FullFilename As String, _ ByVal SheetName As String, _ ByVal CellAddress As String _ ) As Variant Dim Con As Object Dim rs As Object Dim strCon As String Dim strSql1 As String Dim strCellAddress As String Dim lngStart As Long Dim lngEnd As Long Const CONN_STRING As String = "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=<FULL_FILENAME;" & _ "Extended Properties='Excel 8.0;HDR=NO'" Const SQL As String = "" & _ "SELECT F1 FROM [" & _ "<SHEET_NAME$" & _ "<CELL_ADDRESS:<CELL_ADDRESS]" ' Build connection string strCon = CONN_STRING strCon = Replace(strCon, _ "<FULL_FILENAME", FullFilename) ' Build sql statement strSql1 = SQL ' Get first cell from address strCellAddress = Replace(CellAddress, _ "$", vbNullString) On Error Resume Next lngStart = InStr(strCellAddress, "!") + 1 lngEnd = InStr(lngStart, strCellAddress, ":") strCellAddress = Mid$(strCellAddress, _ lngStart, lngEnd - lngStart) On Error GoTo 0 ' Build sql text strSql1 = SQL strSql1 = Replace(strSql1, _ "<SHEET_NAME", SheetName) strSql1 = Replace(strSql1, _ "<CELL_ADDRESS", strCellAddress) ' Open connection to temp workbook Set Con = CreateObject("ADODB.Connection") With Con .ConnectionString = strCon On Error Resume Next .Open Set rs = .Execute(strSql1) GetCellContentsFromClosedWorkbook = _ rs.fields(0).Value On Error GoTo 0 .Close End With End Function Jamie. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - recordset - closed excel workbook
"Ron de Bruin" wrote ...
I know the sheet name and cell that i want to read into my recordset Maybe usuful http://www.rondebruin.nl/ado.htm Ron, I couldn't get your code to return a value from a single cell. I tried "A2" and "A2:A2". The code needs a little tweak, methinks (hint: HDR=NO). Jamie -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined | Excel Programming | |||
Recordset or Object is closed HELP PLEASE! | Excel Programming | |||
Recordset Problem - object is closed | Excel Programming | |||
how to run macro of closed excel workbook using VBA | Excel Programming | |||
open range (within workbook) as ado recordset - excel vba | Excel Programming |