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