You allude to the fact that the first sheet in the series will be different
depending on the sheet being checked. In you example you use
Open Orders - P 10 sheet being checked
Open Orders - P 1 first sheet in the series

However, in your code, you don't check sheets that don't start with "Order
Status"

so how is the first sheet in the series to be determined
? if the name has 2 numbers on the right, then use the first digit of the
two?
? Would "Order Status" then be replace with "Open Orders"

Assume that is the case

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then
sName = Replace(sh.Name,"Order Status","Open Orders")
sName = Left(sName,len(sName)-1)
on Error Resume Next
set sh = worksheets(sName)
on Error goto 0
bskip = False
if not sh is nothing then
if application.Countif(sh.Range("A:A","*" & _
Right(sh.Name,2) & "*") 0 then
bskip = True
end if
end if
' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."
if not bskip then
sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
end if
End If
Next

Take the right "*" off in the countif if column 1 will contain the two digit
number only on the right end of the string.

--
Regards,
Tom Ogilvy

"Dallman Ross" wrote:

I have VBA code to run web queries on a set of sheets.
I want to add an If-statement such that

If Right(.Name,2) ...

where the dots would finish the statement "is contained
in Range("A1") of the first sheet in the series."

E.g., sheet is named "Open Orders - P 10"; there may
or may not be a need to run that query. If the text
(not number) "10" is found in A1 of the sheet
"Open Orders - P 1", then let's go ahead and bother to
run the web query.

My code so far, which works but doesn't have the above
qualifier:

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then

' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."

sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
End If
Next

Help?

In , Tom Ogilvy
spake thusly:

You allude to the fact that the first sheet in the series will be different
depending on the sheet being checked. In you example you use
Open Orders - P 10 sheet being checked
Open Orders - P 1 first sheet in the series

However, in your code, you don't check sheets that don't start with "Order
Status"

Tom,

I mightily appreciate your tackling this. I didn't have access
to the workbook in question for a couple of days, so I didn't get
back to this when I wanted to. But now I'm looking at it again.
I'm most grateful for the help.

I misspoke, and that caused the confusion with regard to the
sheet names. Sorry. The sheets are called "Order Status - P1"
through "... - P 10". No sheet in the book is called "Open Orders ..."
But the book itself is called "Open Orders - Queries". So that's
why I managed to mix the names up and confuse both you and me in the
process.


so how is the first sheet in the series to be determined
? if the name has 2 numbers on the right, then use the first
digit of the two?
? Would "Order Status" then be replace with "Open Orders"


Aside from the name mix-up, here is my thinking on running through the
sheets. There are the numbered sheets, and there are two other
sheets with WebQueries I'll be updating, and there is the main
sheet, called "MergeSheet", which has no WebQuery and isn't part
of my run.

I put in my current macro:

Debug.Print sh.Name & " completed..."

so I could see and show you what the names are that get recognized
by the For-loop.

Order Status - P 1 completed...
Order Status - P 2 completed...
Order Status - P 3 completed...
Order Status - P 4 completed...
Order Status - P 5 completed...
Order Status - P 6 completed...
Order Status - P 7 completed...
Order Status - P 8 completed...
Order Status - P 9 completed...
Order Status - P 10 completed...
Order Status - New completed...
Order Status - Pending completed...

So that's fine and what I want. I was merely trying not to run the
queries when they aren't necessary. For the sheets with a "page
number" in their sheetname, that would be: if something (actually a
string) that looks like that number appears in cell A1 of the FIRST
sheet, run the current sheet's query; otherwise, don't.

Here is what it says in A1 of sheet1 now:

Page 1 2 3 4 5 6 7 8 9

(What happens is, when I open the workbook I go to that Sheet1
and log in to the web query page manually to initialize it with
username and password. Sheet1's data range properties are set to
run that query automatically every 10 minutes -- which is [barely]
often enough to keep the source web page from closing the login
and requiring a new manual login with password.

(I don't run the other sheets automatically, because (1) it is
disruptive to my work when the queries happen, so I'd rather they
didn't unless I need the updates; and (2) sometimes I need updates
more often than every 10 minutes, but other times I don't need them
for hours at a time.

(So anyway, the query runs, and Sheet1's A1 cell gets populated
with that text which happens to show me how many sheets of
data there are right now.)

Right now I wouldn't need to run the Sheet10 query, but I'd
need all the others. This is what I was asking about initially.
And I do have that part more or less working now.

The current code is this (changed some since I posted it the other
day). I will say more below this:

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then

' if the right two chars of the current sheet's name _
appear on or after char 5 of the 1st sheet's A1 ...

If InStr(5, worksheets("Order Status - P 1").Range("A1"), _
Right(sh.Name, 2)) 0 Then

Debug.Print "yeah" ' this works
End If

sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
End If
Next

Sheet0.Select ' this is "MergeSheet" and doesn't have a query to run
End Sub


Okay, well, 'Debug.Print "yeah"' is obviously just for me
to get myself going. It works, and here is the answer to
your second question, which, again, was:

? if the name has 2 numbers on the right, then use the first
digit of the two?

The info in Sheet1!A1 has whitespace between each listed page
number. If there is a page 10 listed, it looks like so in A1:

Page 1 2 3 4 5 6 7 8 9 10

So if I grab the right two chars of my current sheetname and
look for that in that string, well, if the right two chars are
" 1" we'll find it; and if the right two chars are "10", we'll
also find it in the above example. Earlier pages always run,
so there's never going to be a situation where the info in A1
says "Page 8 9 10" but doesn't also say "Page 1". So
that's why I decided to look here to decide whether to run the
query for each page.

Actually, the A1 message will appear on all the pages in this
subset. But what's in A1 on the later sheets might be old data.
So by looking on Sheet1, I'm sure it's current.

I will study your code and try to implement it as needed.
I'm thinking I ought to go with a Case statement, though.
That's because I want to run

* Order Status - P 1: every time, and use its A1 as described above
* The other "... - P x" sheets provided they appear in that
list.

* Order Status - New: every time (but it doesn't have the A1 info
and it isn't incuded in what's on the Sheet1!A1 list)

* Order Status - Pending: run if the U.S. stock markets are CLOSED;
otherwise, don't bother running it. It, too, is not showin in
Sheet1!A1.

There's one more sheet, called "Order Stat. - Executed - P 1", that
I run only occasionally and hence manually. I abbreviated the word
"Stat." in the sheetname quite on purpose so I could keep it out of
my loop.

Okay, <whew! that was a long explanation, but if you are able
to help further, It would be very cool. Meanwhile, as said, I'll
study what you gave me so far.

Dallman

P.S. Would you or would someone please explain how the "on Error"
thing works?

=================================================
Assume that is the case

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then
sName = Replace(sh.Name,"Order Status","Open Orders")
sName = Left(sName,len(sName)-1)
on Error Resume Next
set sh = worksheets(sName)
on Error goto 0
bskip = False
if not sh is nothing then
if application.Countif(sh.Range("A:A","*" & _
Right(sh.Name,2) & "*") 0 then
bskip = True
end if
end if
' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."
if not bskip then
sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
end if
End If
Next

Take the right "*" off in the countif if column 1 will contain the two digit
number only on the right end of the string.

(I saw your other message with the correction about column to row.)

In , Tom Ogilvy
spake thusly:

Still working through how your code works with the "bskip" thing,
Tom. Meanwhile, I have mine further along and almost working,
even if it is really ugly. (Could use some tips on cleaning it
up.) The only part that isn't working is the test for whether
we are outside 9:30 a.m. and 4 p.m. weekdays (and it's not a
holiday) :-) in the place I indicated with a dummy "TRUE" value.
If we're outside market hours, I'll run the query for the "Pending"
sheet; otherwise, I don't need to.


Sub OrderWebQueries()
'
' OrderWebQueries Macro
' Macro recorded 8/15/2007 by Dallman Ross
' Shortcut Ctrl + U

' updates Order Status sheet WebQueries

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then

sh.Activate
Range("A2").Select

' if the right two chars of the current sheet's name _
appear on or after char 5 of the 1st sheet's A1 ...

If InStr(5, worksheets("Order Status - P 1").Range("A1"), _
Right(sh.Name, 2)) 0 Then

Debug.Print sh.Name & " (If) about to run..."

Selection.QueryTable.Refresh _
BackgroundQuery:=True

ElseIf IsEmpty(sh.Range("A1")) Then
If True Or sh.Name = "Order Status - New" Then
' replace "True" with outside-market-hours test _
for "Pending" sheet

Debug.Print sh.Name & " (ElseIf) about to run..."

Selection.QueryTable.Refresh _
BackgroundQuery:=True

End If
End If
End If
Next
Sheet0.Select ' MergeSheet

End Sub


Your original suggestion is below for archival purposes:

=dman=

==========================
Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then
sName = Replace(sh.Name,"Order Status","Open Orders")
sName = Left(sName,len(sName)-1)
on Error Resume Next
set sh = worksheets(sName)
on Error goto 0
bskip = False
if not sh is nothing then
if application.Countif(sh.Range("A:A","*" & _
Right(sh.Name,2) & "*") 0 then
bskip = True
end if
end if
' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."
if not bskip then
sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
end if
End If
Next

Take the right "*" off in the countif if column 1 will contain the two digit
number only on the right end of the string.

Tom Ogilvy helped me out last week and gave me some good ideas.
I've worked on my macro some more and now have the below code.
Any comments are welcome -- I'm learning. E.g., I don't know
how to use On-Error statements, so I didn't include anly.

This seems to work fine now. Basicaly, run web queries on
pages if certain conditions are met. First, the right part
of the sheet name should appear in A1 of the first sheet on
which queries get run. Second, run one of the othe sheets
regardless. Third, run the "Pending" sheet only if outside
market hours.

Here's my code.

-------------

Sub OrderWebQueries()
'
' Macro last edited 8/16/2007 by Dallman Ross
' Shortcut Ctrl + U

' updates Open Order data via web queries

Dim sh As Worksheet
Dim pNumStr As String

pNumStr = ThisWorkbook.Worksheets("Order Status - P 1").Range("A1")

For Each sh In ThisWorkbook.Worksheets

sh.Activate
If Left(sh.Name, 12) = "Order Status" Then

' if the right two chars of the current Sheet's _
name appear (space-delimited) on or after char 5 _
of the 1st sheet's A1 ...

If InStr(5, pNumStr, Right(sh.Name, 2)) 0 _
Or sh.Name = "Order Status - New" Then

Call doQuery

ElseIf sh.Name = "* Pending" Then

Dim hhTime As Long
hhTime = Format(Time, "hh")

' N.B.: my local time is Central European

If hhTime < 15 Or hhTime 22 Or _
Weekday(Now, vbMonday) 5 Then

Call doQuery

End If
End If
End If
Next
Sheet0.Select ' MergeSheet
End Sub


Private Sub doQuery()
Dim sh As Worksheet

Set sh = ActiveSheet
Debug.Print sh.Name

sh.Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
End Sub

--
dman

==========================
In , Tom Ogilvy
spake thusly:

Dim sh As Worksheet

For Each sh In ThisWorkbook.worksheets
If Left(sh.Name, 12) = "Order Status" Then
sName = Replace(sh.Name,"Order Status","Open Orders")
sName = Left(sName,len(sName)-1)
on Error Resume Next
set sh = worksheets(sName)
on Error goto 0
bskip = False
if not sh is nothing then
if application.Countif(sh.Range("A:A","*" & _
Right(sh.Name,2) & "*") 0 then
bskip = True
end if
end if
' This is where the new If-statement will go _
that begins "If Right(.name,2) ..."
if not bskip then
sh.Activate
Range("A2").Select
Selection.QueryTable.Refresh _
BackgroundQuery:=True

Debug.Print sh.Name & " completed..."
end if
End If
Next

Take the right "*" off in the countif if column 1 will contain the two digit
number only on the right end of the string.

In , Dallman Ross <dman@localhost.
spake thusly:

This seems to work fine now. Basicaly, run web queries on
pages if certain conditions are met. First, the right part
of the sheet name should appear in A1 of the first sheet on
which queries get run. Second, run one of the othe sheets
regardless. Third, run the "Pending" sheet only if outside
market hours.

I found a bug. I fixed it and changed a couple of other
minor things. Here's the bug, in case anybody's "listening."


ElseIf sh.Name = "* Pending" Then

Needed to be

ElseIf sh.Name Like "* Pending" Then

The main other thing I changed is adding an OR-statement:


If InStr(5, pNumStr, Right(sh.Name, 2)) 0 _
Or sh.Name = "Order Status - New" Then

Became

If InStr(5, pNumStr, Right(sh.Name, 2)) 0 _
Or sh.Name = "Order Status - New" _
Or Not IsEmpty(Range("A4")) Then

That's because the sheets that I don't always run
queries on might be out of date with old data. They
are supposed not to run because they are empty.

=dman=