| 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
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 |
|
#8
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying something with Record Set
Chip
One more thing Please Say if dataField4 is Null Or empty how can the code still run Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value "Chip Pearson" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying something with Record Set
Say if dataField4 is Null Or empty how can the code still run
I haven't tested this lately, but if memory serves, Excel will convert a database NULL to the text string "null" when it puts the value in a cell. Whether that is good design is open to debate. A database Empty will be simply a blank cell with no content. If you store a database Null value in a Variant data type before transferring it to a worksheet cell , e.g., Dim V As Variant V = RecSet.Fields("SomeField").Value and SomeField is Null, the variable V will contain a Null value (E.g., Debug.Print IsNull(V) prints TRUE) but when that value is transfered to a worksheet cell, it is converted to the String "Null". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip One more thing Please Say if dataField4 is Null Or empty how can the code still run Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value "Chip Pearson" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying something with Record Set
Chip I turned all to Variant
This is the formula that is in DBDataFields5 =INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0 =IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected) INDIRECT(D33)) Returns "" If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd) If the DBDataField5 = "" 'Error Not sure what i'm missing maybe the LIGHT will come on later Dim dataField1 As Variant, dataField2 As Variant, dataField3 As Variant, _ dataField4 As Variant, dataField5 As Variant dataField1 = Range("DBDataField1").Value dataField2 = Range("DBDataField2").Value dataField3 = Range("DBDataField3").Value dataField4 = Range("DBDataField4").Value dataField5 = Range("DBDataField5").Value Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value Sheets(wsName).Range("E" & i).Value = rs1.Fields(dataField5).Value "Chip Pearson" wrote: Say if dataField4 is Null Or empty how can the code still run I haven't tested this lately, but if memory serves, Excel will convert a database NULL to the text string "null" when it puts the value in a cell. Whether that is good design is open to debate. A database Empty will be simply a blank cell with no content. If you store a database Null value in a Variant data type before transferring it to a worksheet cell , e.g., Dim V As Variant V = RecSet.Fields("SomeField").Value and SomeField is Null, the variable V will contain a Null value (E.g., Debug.Print IsNull(V) prints TRUE) but when that value is transfered to a worksheet cell, it is converted to the String "Null". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip One more thing Please Say if dataField4 is Null Or empty how can the code still run Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value "Chip Pearson" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying something with Record Set
Mike,
If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd) That is because Fields is a 0-based "array" (not really a real Array or Collection, but works more or less like a combination of the two). That is, RecSet.Fields(0) is the same as RecSet.Fields("FirstFieldName"). You can access fields either by their Name --- RecSet.Fields("MyFieldName") -- or by their position in the record set -- RecSet.Fields(1). The position number 0 refers to the first field in the record set. The following can be used to return the last field in the RecSet if you don't happen to know its name: With RecSet V = .Fields(.Fields.Count - 1) End With If the DBDataField5 = "" 'Error That is because there is no Field whose name is an empty string. You'll get a "Field Name Not Found In RecordSet" error or something to that effect. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip I turned all to Variant This is the formula that is in DBDataFields5 =INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0 =IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected) INDIRECT(D33)) Returns "" If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd) If the DBDataField5 = "" 'Error Not sure what i'm missing maybe the LIGHT will come on later Dim dataField1 As Variant, dataField2 As Variant, dataField3 As Variant, _ dataField4 As Variant, dataField5 As Variant dataField1 = Range("DBDataField1").Value dataField2 = Range("DBDataField2").Value dataField3 = Range("DBDataField3").Value dataField4 = Range("DBDataField4").Value dataField5 = Range("DBDataField5").Value Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value Sheets(wsName).Range("E" & i).Value = rs1.Fields(dataField5).Value "Chip Pearson" wrote: Say if dataField4 is Null Or empty how can the code still run I haven't tested this lately, but if memory serves, Excel will convert a database NULL to the text string "null" when it puts the value in a cell. Whether that is good design is open to debate. A database Empty will be simply a blank cell with no content. If you store a database Null value in a Variant data type before transferring it to a worksheet cell , e.g., Dim V As Variant V = RecSet.Fields("SomeField").Value and SomeField is Null, the variable V will contain a Null value (E.g., Debug.Print IsNull(V) prints TRUE) but when that value is transfered to a worksheet cell, it is converted to the String "Null". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip One more thing Please Say if dataField4 is Null Or empty how can the code still run Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value "Chip Pearson" wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying something with Record Set
Mike,
If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd) That is because Fields is a 0-based "array" (not really a real Array or Collection, but works more or less like a combination of the two). That is, RecSet.Fields(0) is the same as RecSet.Fields("FirstFieldName"). You can access fields either by their Name --- RecSet.Fields("MyFieldName") -- or by their position in the record set -- RecSet.Fields(1). The position number 0 refers to the first field in the record set. The following can be used to return the last field in the RecSet if you don't happen to know its name: With RecSet V = .Fields(.Fields.Count - 1) End With If the DBDataField5 = "" 'Error That is because there is no Field whose name is an empty string. You'll get a "Field Name Not Found In RecordSet" error or something to that effect. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip I turned all to Variant This is the formula that is in DBDataFields5 =INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0 =IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected) INDIRECT(D33)) Returns "" If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd) If the DBDataField5 = "" 'Error Not sure what i'm missing maybe the LIGHT will come on later Dim dataField1 As Variant, dataField2 As Variant, dataField3 As Variant, _ dataField4 As Variant, dataField5 As Variant dataField1 = Range("DBDataField1").Value dataField2 = Range("DBDataField2").Value dataField3 = Range("DBDataField3").Value dataField4 = Range("DBDataField4").Value dataField5 = Range("DBDataField5").Value Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value Sheets(wsName).Range("E" & i).Value = rs1.Fields(dataField5).Value "Chip Pearson" wrote: Say if dataField4 is Null Or empty how can the code still run I haven't tested this lately, but if memory serves, Excel will convert a database NULL to the text string "null" when it puts the value in a cell. Whether that is good design is open to debate. A database Empty will be simply a blank cell with no content. If you store a database Null value in a Variant data type before transferring it to a worksheet cell , e.g., Dim V As Variant V = RecSet.Fields("SomeField").Value and SomeField is Null, the variable V will contain a Null value (E.g., Debug.Print IsNull(V) prints TRUE) but when that value is transfered to a worksheet cell, it is converted to the String "Null". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mike" wrote in message ... Chip One more thing Please Say if dataField4 is Null Or empty how can the code still run Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value "Chip Pearson" wrote: 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 |
| 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 | |||
Linear Mode
