"Not" and "Not Like"

  • Thread starter Francesco Sblendorio
  • Start date
F

Francesco Sblendorio

If WSh.Name <> "Admin" and <> "Server * Projection Seed Values"

First of all, this line lacks 'WSh.Name' before '<> "Server *... '
Obviously this test will not perform a LIKE compare.
To solve your problem, you can do so:

If WSh.Name <> "Admin" and Left(WShName, 7) <> "Server " and Right(WShName,
23) <> " Projection Seed Values" Then

Be attention: "Server " ends with a space, " Projection Seed Values" begins
with a space.
 
G

Grant Reid

Hi

I need to perform arbitary actions on all sheets in my workbook except for a
sheet with the name "Admin" and any sheets with the following naming
convention "Server * Projection Seed Values". Am at my wits end with this
one. Can anyone help. See my code below;

Dim qt As QueryTable
Dim WSh As Worksheet

For Each WSh In ThisWorkbook.Worksheets
If WSh.Name <> "Admin" and <> "Server * Projection Seed Values"
Then '<------ This is the problem area
'Perform action here
End If
Next WSh

End Sub

Many Thanks - Grant
 
F

Francesco Sblendorio

If WSh.Name <> "Admin" and Left(WShName, 7) <> "Server " and Right(WShName,
23) <> " Projection Seed Values" Then
Click to expand...


ahem... I wrote "WShName" instead of "WSh.Name"
 
A

Andy Pope

Hi Grant,

You need to add WSh.name to the second part of the test as well.

If WSh.Name <> "Admin" and WSh.Name <> "Server * Projection Seed
Values" Then

Cheers
Andy
 
F

Francesco Sblendorio

If WSh.Name <> "Admin" and (Left(WShName, 7) <> "Server " OR Right(WShName,
23) <> " Projection Seed Values") Then

7 and 23 are lengths of "Server " and " Projection Seed Values"
 
G

Grant Reid

Hi

Many thanks to those who reponded , but...... I'm still not getting the
expected result. I'm trying to refresh all query tables in the workbook,
except for a worksheet named "Admin" and all worksheets with a naming
convention of "Server 1 Database Space Summary", "Server 2 Database Space
Summary", "Server 3 Database Space Summary" etc etc. I've tried variations
of the responses posted thus far but without success. Perhaps I'm missing
the obvious or hitting a late afternoon blank. I've re-posted my code below
hoping that someone will highlight the problem for me. Many thanks in
advance

Sub RefreshExisting()

Dim qt As QueryTable
Dim WSh As Worksheet
Dim strQueryName As String

For Each WSh In ThisWorkbook.Worksheets
For Each qt In WSh.QueryTables
strQueryName = WSh.Range("AM2")
If WSh.Name <> "Admin" And WSh.Name <> "Server * Projection Seed
Values" Then
If InStr(qt.Name, strQueryName) Then
qt.Refresh BackgroundQuery:=False
End If
End If
Next qt
Next WSh
End Sub

Kind Regards - Grant
 
B

Bob Phillips

If WSh.Name <> "Admin" and _
Not WSh.Name like "Server * Projection Seed Values" Then

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Grant Reid

Hi Francesco

I tried all the suggestions and the only ones that did the trick for me were
the solutions posted by you and Bob. Very elegant, both of them.

I do not know why the solution posted by Andy did'nt do it for me. Perhaps
it was late afternoon brain-fog ;-)

Once again, many thanks to you all

Kind Regards - Grant
 
B

Bob Phillips

and I had thought my little gem had got lost in all the fog<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Andy Pope

I left out the LIKE operator.

Grant said:
Hi Francesco

I tried all the suggestions and the only ones that did the trick for me were
the solutions posted by you and Bob. Very elegant, both of them.

I do not know why the solution posted by Andy did'nt do it for me. Perhaps
it was late afternoon brain-fog ;-)

Once again, many thanks to you all

Kind Regards - Grant
Click to expand...
 
Top