View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Mike Mike is offline
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