Howdy all.
Trying to use range names and SQL to check for new names that may
appear in the RLS BYR Name column of the tbl_imported_data worksheet.

Have another file that works using ADO and SQL but uses the worksheet
names (in brackets and with $ at end of name) in the SQL statement
instead of the range names. Basically, obtaining all names in
tbl_imported_data and checking to see if they are in the current
SVBuyers column listing .

Is it possible to use range names in the SQl Not In query as a similar
err (rngRLSBYRName$ is not a valid name) now appears when I separated
the statement into 2 recordset?

Thanks in advance for any comments/suggestions you may have.

Rey


Error messages:

without [] and $ on range names
runtime err -2147217865
The Microsoft Jet database engine could not find the object
'rngCurrentBuyers'. Make sure the object exists and that your spell
its name and the path name correctly.

w/ brackets and $
runtime err -2147467259
'rngCurrentBuyers$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long.


Code:

Sub CheckForNewNames()
' purpose: check for new names in tbl_imported
' using named ranges and ADO

Dim thisWrkBook As Workbook
Dim buyerSheet As Worksheet ' Buyers and Codes worksheet
Dim dataSheet As Worksheet ' tbl_imported_data worksheet
Dim rngCurrentBuyers As Range ' SVBuyers column in Buyers and
Codes
Dim rngRLSBYRName As Range ' RLS BYR Name column in
tbl_imported_data
Dim strNewNames As String
Dim arNewNames() As String 'array of new names
Dim conn As New ADODB.Connection

Dim strSql As String
Dim strConnection As String
Dim lngRecCount As Long
Dim x As Long
Dim strNewBuyers As String

' test to get data from indiv columns
Dim rs As New ADODB.Recordset ' names of buyers not
currently listed in SVBuyerrs column


strNewNames = ""
strNewBuyers = ""

' set up sheets
Set buyerSheet = Worksheets("Buyers and Codes")
Set dataSheet = Worksheets("tbl_imported_data")

' set up the ranges
Set rngRLSBYRName = dataSheet.Range("J:J").EntireColumn '
tbl_imported_data

Set rngCurrentBuyers = buyerSheet.Range("C:C").EntireColumn '
Buyers and Codes

' sql not in statement
strSql = "SELECT [RLS BYR Name] AS NewNames FROM [rngRLSBYRName$]"

strSql = strSql & " WHERE [RLS BYR Name] NOT IN (SELECT SVBuyers
FROM [rngCurrentBuyers$])"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Testing\Filter_tbl_Imported_Data
\Testing_RangesAndADO.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = strConnection
.CursorLocation = adUseClient ' needed else rs contains nothing -1
.Open

End With

'Application.DisplayStatusBar = True

rs.Open strSql, conn, adOpenKeyset, adLockOptimistic <-- fails
here

lngRecCount = rs.RecordCount

rs.MoveFirst

' display names in msgBox
' or add another sheet w/names


' closing
rs.Close
Set rs = Nothing

If conn.State = adStateOpen Then
conn.Close
Set conn = Nothing
End If



End Sub

Hi,

I've had a quick look and think you need to change the following line:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [rngRLSBYRName$]"

to this:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [" & rngRLSBYRName.value &
"]"

Sam



"Rey" wrote:

Howdy all.
Trying to use range names and SQL to check for new names that may
appear in the RLS BYR Name column of the tbl_imported_data worksheet.

Have another file that works using ADO and SQL but uses the worksheet
names (in brackets and with $ at end of name) in the SQL statement
instead of the range names. Basically, obtaining all names in
tbl_imported_data and checking to see if they are in the current
SVBuyers column listing .

Is it possible to use range names in the SQl Not In query as a similar
err (rngRLSBYRName$ is not a valid name) now appears when I separated
the statement into 2 recordset?

Thanks in advance for any comments/suggestions you may have.

Rey


Error messages:

without [] and $ on range names
runtime err -2147217865
The Microsoft Jet database engine could not find the object
'rngCurrentBuyers'. Make sure the object exists and that your spell
its name and the path name correctly.

w/ brackets and $
runtime err -2147467259
'rngCurrentBuyers$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long.


Code:

Sub CheckForNewNames()
' purpose: check for new names in tbl_imported
' using named ranges and ADO

Dim thisWrkBook As Workbook
Dim buyerSheet As Worksheet ' Buyers and Codes worksheet
Dim dataSheet As Worksheet ' tbl_imported_data worksheet
Dim rngCurrentBuyers As Range ' SVBuyers column in Buyers and
Codes
Dim rngRLSBYRName As Range ' RLS BYR Name column in
tbl_imported_data
Dim strNewNames As String
Dim arNewNames() As String 'array of new names
Dim conn As New ADODB.Connection

Dim strSql As String
Dim strConnection As String
Dim lngRecCount As Long
Dim x As Long
Dim strNewBuyers As String

' test to get data from indiv columns
Dim rs As New ADODB.Recordset ' names of buyers not
currently listed in SVBuyerrs column


strNewNames = ""
strNewBuyers = ""

' set up sheets
Set buyerSheet = Worksheets("Buyers and Codes")
Set dataSheet = Worksheets("tbl_imported_data")

' set up the ranges
Set rngRLSBYRName = dataSheet.Range("J:J").EntireColumn '
tbl_imported_data

Set rngCurrentBuyers = buyerSheet.Range("C:C").EntireColumn '
Buyers and Codes

' sql not in statement
strSql = "SELECT [RLS BYR Name] AS NewNames FROM [rngRLSBYRName$]"

strSql = strSql & " WHERE [RLS BYR Name] NOT IN (SELECT SVBuyers
FROM [rngCurrentBuyers$])"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Testing\Filter_tbl_Imported_Data
\Testing_RangesAndADO.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = strConnection
.CursorLocation = adUseClient ' needed else rs contains nothing -1
.Open

End With

'Application.DisplayStatusBar = True

rs.Open strSql, conn, adOpenKeyset, adLockOptimistic <-- fails
here

lngRecCount = rs.RecordCount

rs.MoveFirst

' display names in msgBox
' or add another sheet w/names


' closing
rs.Close
Set rs = Nothing

If conn.State = adStateOpen Then
conn.Close
Set conn = Nothing
End If



End Sub

Howdy Sam.
Thanks for replying.
Tried suggestion and received msg type mismatch.
Separate the statement into 2 and it failed on first statement...

Will change back to using worksheet name and see how that goes...

Again, thanks for replying.

Rey

On Jan 29, 2:36*am, Sam Wilson
wrote:
Hi,

I've had a quick look and think you need to change the following line:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [rngRLSBYRName$]"

to this:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [" & rngRLSBYRName.value &
"]"

Sam

OK, another thing to try if you're getting a type mismatch:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM 'x" & rngRLSBYRName.value &
"x' "

(you need to delete the x before and after the & bit, I've just put them in
there so you can spot the single quotation marks


"Rey" wrote:

Howdy Sam.
Thanks for replying.
Tried suggestion and received msg type mismatch.
Separate the statement into 2 and it failed on first statement...

Will change back to using worksheet name and see how that goes...

Again, thanks for replying.

Rey

On Jan 29, 2:36 am, Sam Wilson
wrote:
Hi,

I've had a quick look and think you need to change the following line:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [rngRLSBYRName$]"

to this:

strSql = "SELECT [RLS BYR Name] AS NewNames FROM [" & rngRLSBYRName.value &
"]"

Sam