Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recordset / Named Range Questions
In workbook Book1.xls I have a named range "Scores." On Sheet1, the
range looks like the following Header1 Header2 Header3 1 2 3 a b c I am reading the data in the range using the code from "Professional Excel Development" at the bottom of this post. 1. If I define "Scores" as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) when I try to read the range I get the error message "The Microsoft Jet database engine could not find the object 'Scores'. Make sure the object exists and that you spell its name and the path name correctly." However, if I define the range as =Sheet1!$A$1:$C$3 I can read the range without problem. Is there a way to allow "Scores" to automatically update when new data are added and still be able to use the code below? I want to avoid hardcoding the address of the data to be copied. 2. Using the code below, when HDR=YES I get the following when copying the data 1 2 3 If I have HDR=NO I get the following. Header1 Header2 Header3 (blank row) a b c Is there a reason I can't read both rows of data in the range? 3. If I am using a sheet as the data storage layer in the workbook, is it good/bad/acceptable practice to use the code below to read stored data in the same workbook as the code, or should it only be used for data stored in other workbooks? Thank you. Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Excel\Book1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";" szSQL = "SELECT * FROM Scores" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recordset / Named Range Questions
If it is in the same workbook
v = Range("Scores").Value would put all the data in an array set rng = Range("Scores").Value set rng = rng.offset(1,0).Resize(rng.rows.count-1) v = rng.Address would put just the data with no headers in an array Using ADO on an open workbook causes a memory leak as I recall and it seems 1) much more complex What does it buy you - what are you going to do with it once it is in a recordset. http://support.microsoft.com/default...;319998&Produc... BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If scores is in a closed workbook, then I am not sure it is defined when the workbook is closed. It isn't something I have tested, but I would not be surprised that it needs to be in an open workbook to be valid. You migh consider putting beforeclose code to define a hard coded scores and then save the workbook. The disadvantage is that you force the workbook to be saved. Back to your data Application.ScreenUpdating = False ' suppress any workbook level events in myfile.xls Application.EnableEvents = False set bk = workbooks.Open("C:\Myfolder\Myfile.xls") set rng = bk.Names("Scores").RefersToRange rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1") bk.close savechanges:=False Application.EnableEvents = True Application.ScreenUpdating = True would be another way to get your data if the workbook where it is stored doesn't take a long time to open -- Regards, Tom Ogilvy "Mark Driscol" wrote: In workbook Book1.xls I have a named range "Scores." On Sheet1, the range looks like the following Header1 Header2 Header3 1 2 3 a b c I am reading the data in the range using the code from "Professional Excel Development" at the bottom of this post. 1. If I define "Scores" as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) when I try to read the range I get the error message "The Microsoft Jet database engine could not find the object 'Scores'. Make sure the object exists and that you spell its name and the path name correctly." However, if I define the range as =Sheet1!$A$1:$C$3 I can read the range without problem. Is there a way to allow "Scores" to automatically update when new data are added and still be able to use the code below? I want to avoid hardcoding the address of the data to be copied. 2. Using the code below, when HDR=YES I get the following when copying the data 1 2 3 If I have HDR=NO I get the following. Header1 Header2 Header3 (blank row) a b c Is there a reason I can't read both rows of data in the range? 3. If I am using a sheet as the data storage layer in the workbook, is it good/bad/acceptable practice to use the code below to read stored data in the same workbook as the code, or should it only be used for data stored in other workbooks? Thank you. Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Excel\Book1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";" szSQL = "SELECT * FROM Scores" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recordset / Named Range Questions
Thank you, Tom.
The workbook Book1.xls is closed. I could open it and read the range in question, but I was hoping to avoid this. (Among other reasons, I'm trying my hand at using recordsets, which I have not used before.) Do you know why I would get the results mentioned in my second question? If all the data are numeric or all text, I get correct results. However, with some rows as text and some as numeric, I don't pull back all the data. Thanks. Mark Tom Ogilvy wrote: If it is in the same workbook v = Range("Scores").Value would put all the data in an array set rng = Range("Scores").Value set rng = rng.offset(1,0).Resize(rng.rows.count-1) v = rng.Address would put just the data with no headers in an array Using ADO on an open workbook causes a memory leak as I recall and it seems 1) much more complex What does it buy you - what are you going to do with it once it is in a recordset. http://support.microsoft.com/default...;319998&Produc... BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If scores is in a closed workbook, then I am not sure it is defined when the workbook is closed. It isn't something I have tested, but I would not be surprised that it needs to be in an open workbook to be valid. You migh consider putting beforeclose code to define a hard coded scores and then save the workbook. The disadvantage is that you force the workbook to be saved. Back to your data Application.ScreenUpdating = False ' suppress any workbook level events in myfile.xls Application.EnableEvents = False set bk = workbooks.Open("C:\Myfolder\Myfile.xls") set rng = bk.Names("Scores").RefersToRange rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1") bk.close savechanges:=False Application.EnableEvents = True Application.ScreenUpdating = True would be another way to get your data if the workbook where it is stored doesn't take a long time to open -- Regards, Tom Ogilvy "Mark Driscol" wrote: In workbook Book1.xls I have a named range "Scores." On Sheet1, the range looks like the following Header1 Header2 Header3 1 2 3 a b c I am reading the data in the range using the code from "Professional Excel Development" at the bottom of this post. 1. If I define "Scores" as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) when I try to read the range I get the error message "The Microsoft Jet database engine could not find the object 'Scores'. Make sure the object exists and that you spell its name and the path name correctly." However, if I define the range as =Sheet1!$A$1:$C$3 I can read the range without problem. Is there a way to allow "Scores" to automatically update when new data are added and still be able to use the code below? I want to avoid hardcoding the address of the data to be copied. 2. Using the code below, when HDR=YES I get the following when copying the data 1 2 3 If I have HDR=NO I get the following. Header1 Header2 Header3 (blank row) a b c Is there a reason I can't read both rows of data in the range? 3. If I am using a sheet as the data storage layer in the workbook, is it good/bad/acceptable practice to use the code below to read stored data in the same workbook as the code, or should it only be used for data stored in other workbooks? Thank you. Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Excel\Book1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";" szSQL = "SELECT * FROM Scores" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recordset / Named Range Questions
su
http://www.dicks-blog.com/archives/2...ed-data-types/ also http://support.microsoft.com/default...b;en-us;257819 How To Use ADO with Excel Data from Visual Basic or VBA -- Regards, Tom Ogilvy "Mark Driscol" wrote: Thank you, Tom. The workbook Book1.xls is closed. I could open it and read the range in question, but I was hoping to avoid this. (Among other reasons, I'm trying my hand at using recordsets, which I have not used before.) Do you know why I would get the results mentioned in my second question? If all the data are numeric or all text, I get correct results. However, with some rows as text and some as numeric, I don't pull back all the data. Thanks. Mark Tom Ogilvy wrote: If it is in the same workbook v = Range("Scores").Value would put all the data in an array set rng = Range("Scores").Value set rng = rng.offset(1,0).Resize(rng.rows.count-1) v = rng.Address would put just the data with no headers in an array Using ADO on an open workbook causes a memory leak as I recall and it seems 1) much more complex What does it buy you - what are you going to do with it once it is in a recordset. http://support.microsoft.com/default...;319998&Produc... BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If scores is in a closed workbook, then I am not sure it is defined when the workbook is closed. It isn't something I have tested, but I would not be surprised that it needs to be in an open workbook to be valid. You migh consider putting beforeclose code to define a hard coded scores and then save the workbook. The disadvantage is that you force the workbook to be saved. Back to your data Application.ScreenUpdating = False ' suppress any workbook level events in myfile.xls Application.EnableEvents = False set bk = workbooks.Open("C:\Myfolder\Myfile.xls") set rng = bk.Names("Scores").RefersToRange rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1") bk.close savechanges:=False Application.EnableEvents = True Application.ScreenUpdating = True would be another way to get your data if the workbook where it is stored doesn't take a long time to open -- Regards, Tom Ogilvy "Mark Driscol" wrote: In workbook Book1.xls I have a named range "Scores." On Sheet1, the range looks like the following Header1 Header2 Header3 1 2 3 a b c I am reading the data in the range using the code from "Professional Excel Development" at the bottom of this post. 1. If I define "Scores" as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) when I try to read the range I get the error message "The Microsoft Jet database engine could not find the object 'Scores'. Make sure the object exists and that you spell its name and the path name correctly." However, if I define the range as =Sheet1!$A$1:$C$3 I can read the range without problem. Is there a way to allow "Scores" to automatically update when new data are added and still be able to use the code below? I want to avoid hardcoding the address of the data to be copied. 2. Using the code below, when HDR=YES I get the following when copying the data 1 2 3 If I have HDR=NO I get the following. Header1 Header2 Header3 (blank row) a b c Is there a reason I can't read both rows of data in the range? 3. If I am using a sheet as the data storage layer in the workbook, is it good/bad/acceptable practice to use the code below to read stored data in the same workbook as the code, or should it only be used for data stored in other workbooks? Thank you. Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Excel\Book1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";" szSQL = "SELECT * FROM Scores" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recordset / Named Range Questions
Thank you very much, Tom.
Mark Tom Ogilvy wrote: su http://www.dicks-blog.com/archives/2...ed-data-types/ also http://support.microsoft.com/default...b;en-us;257819 How To Use ADO with Excel Data from Visual Basic or VBA -- Regards, Tom Ogilvy "Mark Driscol" wrote: Thank you, Tom. The workbook Book1.xls is closed. I could open it and read the range in question, but I was hoping to avoid this. (Among other reasons, I'm trying my hand at using recordsets, which I have not used before.) Do you know why I would get the results mentioned in my second question? If all the data are numeric or all text, I get correct results. However, with some rows as text and some as numeric, I don't pull back all the data. Thanks. Mark Tom Ogilvy wrote: If it is in the same workbook v = Range("Scores").Value would put all the data in an array set rng = Range("Scores").Value set rng = rng.offset(1,0).Resize(rng.rows.count-1) v = rng.Address would put just the data with no headers in an array Using ADO on an open workbook causes a memory leak as I recall and it seems 1) much more complex What does it buy you - what are you going to do with it once it is in a recordset. http://support.microsoft.com/default...;319998&Produc... BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If scores is in a closed workbook, then I am not sure it is defined when the workbook is closed. It isn't something I have tested, but I would not be surprised that it needs to be in an open workbook to be valid. You migh consider putting beforeclose code to define a hard coded scores and then save the workbook. The disadvantage is that you force the workbook to be saved. Back to your data Application.ScreenUpdating = False ' suppress any workbook level events in myfile.xls Application.EnableEvents = False set bk = workbooks.Open("C:\Myfolder\Myfile.xls") set rng = bk.Names("Scores").RefersToRange rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1") bk.close savechanges:=False Application.EnableEvents = True Application.ScreenUpdating = True would be another way to get your data if the workbook where it is stored doesn't take a long time to open -- Regards, Tom Ogilvy "Mark Driscol" wrote: In workbook Book1.xls I have a named range "Scores." On Sheet1, the range looks like the following Header1 Header2 Header3 1 2 3 a b c I am reading the data in the range using the code from "Professional Excel Development" at the bottom of this post. 1. If I define "Scores" as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) when I try to read the range I get the error message "The Microsoft Jet database engine could not find the object 'Scores'. Make sure the object exists and that you spell its name and the path name correctly." However, if I define the range as =Sheet1!$A$1:$C$3 I can read the range without problem. Is there a way to allow "Scores" to automatically update when new data are added and still be able to use the code below? I want to avoid hardcoding the address of the data to be copied. 2. Using the code below, when HDR=YES I get the following when copying the data 1 2 3 If I have HDR=NO I get the following. Header1 Header2 Header3 (blank row) a b c Is there a reason I can't read both rows of data in the range? 3. If I am using a sheet as the data storage layer in the workbook, is it good/bad/acceptable practice to use the code below to read stored data in the same workbook as the code, or should it only be used for data stored in other workbooks? Thank you. Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Excel\Book1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES"";" szSQL = "SELECT * FROM Scores" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Named Range questions | Excel Discussion (Misc queries) | |||
sql stored procedure results in a recordset questions | Excel Programming | |||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined | Excel Programming | |||
Named range questions | Excel Programming | |||
Named range questions | Excel Programming |