Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Can Someone tell How I can reference these to a NamedRange or can't this be
done I Get the error # 3265 Item cannot be found in collection VENDOR_ID VEND_PART_NUM VEND_DESC Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work
Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Same Error
Sheets(wsName).Range("A" & i) = Range("DBDataField1").Value and this will list VENDOR_ID where it should be 44 Maybe its not ment to be "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Here's more of the code
On Error GoTo ErrorHandling Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim wsName As String Dim i As Integer wsName = [DestWkstName].Value Sheets(wsName).Cells.ClearContents i = [DestStartCellRef].Value 'Determine the ref number of the selected query 'and record it in the LU_OptSelected range name With Application.Names("LU_OptSelected") intOptRef = CInt(Left(Right(.RefersTo, 3), 2)) End With 'Post a status message to the QryMaster sheet [QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _ = "Running Query" Application.ScreenUpdating = False 'Use for Access (jet) strConn = "Provider=" & [DBDataProvider] & ";" _ & "Data Source=" & [DBDataSource] & "" 'Use for jet strSQL1 = [SQLCode] & ";" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strConn rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly Do While rs1.EOF = False 'Heres where I'm trying to also use a named range Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
I think you need
Dim RecSetColumnName As String RecSetColumnName = Range("NameOfCellOnWorksheet").Value Range("A1").Value = RecSet.Fields(RecSetColumnName).Value It is a matter of personal preference and coding style, but I would move away for the shortcut notation that uses the [ ] characters and write out the objects you are really using. It makes code much easier to read, debug, and maintain. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Here's more of the code On Error GoTo ErrorHandling Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim wsName As String Dim i As Integer wsName = [DestWkstName].Value Sheets(wsName).Cells.ClearContents i = [DestStartCellRef].Value 'Determine the ref number of the selected query 'and record it in the LU_OptSelected range name With Application.Names("LU_OptSelected") intOptRef = CInt(Left(Right(.RefersTo, 3), 2)) End With 'Post a status message to the QryMaster sheet [QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _ = "Running Query" Application.ScreenUpdating = False 'Use for Access (jet) strConn = "Provider=" & [DBDataProvider] & ";" _ & "Data Source=" & [DBDataSource] & "" 'Use for jet strSQL1 = [SQLCode] & ";" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strConn rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly Do While rs1.EOF = False 'Heres where I'm trying to also use a named range Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the rs1!Range("FieldName").Value I had got the Idea from Ron Coderre QryMaster Thanks anyway guys "Chip Pearson" wrote: I think you need Dim RecSetColumnName As String RecSetColumnName = Range("NameOfCellOnWorksheet").Value Range("A1").Value = RecSet.Fields(RecSetColumnName).Value It is a matter of personal preference and coding style, but I would move away for the shortcut notation that uses the [ ] characters and write out the objects you are really using. It makes code much easier to read, debug, and maintain. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Here's more of the code On Error GoTo ErrorHandling Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim wsName As String Dim i As Integer wsName = [DestWkstName].Value Sheets(wsName).Cells.ClearContents i = [DestStartCellRef].Value 'Determine the ref number of the selected query 'and record it in the LU_OptSelected range name With Application.Names("LU_OptSelected") intOptRef = CInt(Left(Right(.RefersTo, 3), 2)) End With 'Post a status message to the QryMaster sheet [QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _ = "Running Query" Application.ScreenUpdating = False 'Use for Access (jet) strConn = "Provider=" & [DBDataProvider] & ";" _ & "Data Source=" & [DBDataSource] & "" 'Use for jet strSQL1 = [SQLCode] & ";" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strConn rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly Do While rs1.EOF = False 'Heres where I'm trying to also use a named range Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut references, those using the [ ] and ! characters and replace them with the verbose object names and properties, you'll see right away what is causing the problem. And then the next time you have to go down this road (and there is always a next time), you'll have a well written procedure template into which you just plug the the right object names and you'll be done. The verbose syntax is means more typing this around, but you'll find advantages later for debugging and code reuse. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Thanks Chip and Rick for your Time but still no luck I'm going to give up. Everything was going good up until the rs1!Range("FieldName").Value I had got the Idea from Ron Coderre QryMaster Thanks anyway guys "Chip Pearson" wrote: I think you need Dim RecSetColumnName As String RecSetColumnName = Range("NameOfCellOnWorksheet").Value Range("A1").Value = RecSet.Fields(RecSetColumnName).Value It is a matter of personal preference and coding style, but I would move away for the shortcut notation that uses the [ ] characters and write out the objects you are really using. It makes code much easier to read, debug, and maintain. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Here's more of the code On Error GoTo ErrorHandling Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim wsName As String Dim i As Integer wsName = [DestWkstName].Value Sheets(wsName).Cells.ClearContents i = [DestStartCellRef].Value 'Determine the ref number of the selected query 'and record it in the LU_OptSelected range name With Application.Names("LU_OptSelected") intOptRef = CInt(Left(Right(.RefersTo, 3), 2)) End With 'Post a status message to the QryMaster sheet [QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _ = "Running Query" Application.ScreenUpdating = False 'Use for Access (jet) strConn = "Provider=" & [DBDataProvider] & ";" _ & "Data Source=" & [DBDataSource] & "" 'Use for jet strSQL1 = [SQLCode] & ";" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strConn rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly Do While rs1.EOF = False 'Heres where I'm trying to also use a named range Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying something with Record Set
Scratch It I Got Thanks So Much Chip
"Chip Pearson" wrote: I think you need Dim RecSetColumnName As String RecSetColumnName = Range("NameOfCellOnWorksheet").Value Range("A1").Value = RecSet.Fields(RecSetColumnName).Value It is a matter of personal preference and coding style, but I would move away for the shortcut notation that uses the [ ] characters and write out the objects you are really using. It makes code much easier to read, debug, and maintain. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Here's more of the code On Error GoTo ErrorHandling Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim strSQL1 As String, strConn Dim wsName As String Dim i As Integer wsName = [DestWkstName].Value Sheets(wsName).Cells.ClearContents i = [DestStartCellRef].Value 'Determine the ref number of the selected query 'and record it in the LU_OptSelected range name With Application.Names("LU_OptSelected") intOptRef = CInt(Left(Right(.RefersTo, 3), 2)) End With 'Post a status message to the QryMaster sheet [QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _ = "Running Query" Application.ScreenUpdating = False 'Use for Access (jet) strConn = "Provider=" & [DBDataProvider] & ";" _ & "Data Source=" & [DBDataSource] & "" 'Use for jet strSQL1 = [SQLCode] & ";" Set cnn = New ADODB.Connection Set rs1 = New ADODB.Recordset cnn.Open strConn rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly Do While rs1.EOF = False 'Heres where I'm trying to also use a named range Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID 'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM 'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC i = i + 1 rs1.MoveNext Loop rs1.Close cnn.Close "Rick Rothstein (MVP - VB)" wrote: Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work Try it this way (where I assume the name of your range is NamedRange1... Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open new record with selected fields from previous record | Excel Discussion (Misc queries) | |||
Record macro doesn't record shape properties | Excel Programming | |||
Finding last record in month for each of several types of record. | Excel Programming | |||
Record Macro - Record custom user actions | Excel Programming | |||
Need help autopopulating next new record with previous record data | Excel Programming |