Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open new record with selected fields from previous record Design by Sue Excel Discussion (Misc queries) 1 June 12th 09 02:24 PM
Record macro doesn't record shape properties Tosco[_2_] Excel Programming 3 May 30th 07 02:28 AM
Finding last record in month for each of several types of record. Richard Buttrey Excel Programming 5 April 4th 05 02:11 AM
Record Macro - Record custom user actions Sal[_4_] Excel Programming 1 December 23rd 04 03:18 PM
Need help autopopulating next new record with previous record data Harry S[_3_] Excel Programming 2 October 1st 03 10:59 PM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"