Here's the segment in wb2 that calls macro 1 in wb1
Workbooks.Open Filename:="C:\Users\Ron\Documents\MS
Files\Excel\BUDX\Airfares - Travelocity Search.xls"
Application.Run "'Airfares - Travelocity
Search.xls'!Best_Airfares_by_Location"
Here's the macro in wb2 that contains the stop commands.
Typos? I presume you mean "the macro in __wb1__ that contains the stop"
statements. And the segment above obviously calls
Best_Airfares_by_Location, not "macro 1". Finally, I presume the
Application.Run statement actually calls Best_Airfares_by_Location_2 (with
"_2"), since that is the name of the procedure that you posted.
(Alternatively, perhaps you renamed your debugging procedure and forgot to
change the Application.Run call. That would certainly explain why the stop
statements in the "_2" procedure are not called. :->)
All you have demonstrated is that flow of control never reaches the stop
statements. Since all but one stop statement follows a MsgBox call, I
presume you would have noted which MsgBox you saw if flow of control went
there.
That leaves the stop statement following Range("A9").Select. But again,
perhaps the procedure never gets that far. You haven't provided sufficient
context for us to know.
To prove or disprove whether stop statements work at all, put a stop
statement before the first executable statement in the procedure -- whatever
procedure is actually called by the Application.Run statement. And put a
breakpoint at the Application.Run statement to confirm that you get there.
(In fact, you could single-step through the Application.Run statement,
obviating the need for the first stop statements. But the point of exercise
is to demonstrate that stop statements work in general under the
circumstances.)
I think you will find the stop statment per se is not the problem. On the
other hand, there is ample opportunity for programming flaws in the
procedure itself.
I do not have experience with network programming using VBA per, much less
acquiring and parsing HTML from IE in VBA. So I cannot help you debug your
design.
However, some things are obviously suspicious.
For example, your only On Error statements are On Error Resume Next. First,
it is not necessary to execute On Error Resume Next more than once, much
less in a loop, if that is your only On Error statement. Second and more
significantly, failing to execute On Error GoTo 0 subsequently will cause
any unexpected errors to go undetected.
Arguably, that might suggest that you should always reach the stop statement
following Range("A9").Select. But perhaps the problem you are wrestling
with is an infinite loop caused or aided by the undetected errors. I don't
know; you never say.
Also, I notice that you have Application.Wait statements. Each __follows__
a Do-Loop statement. That makes little sense to me. I can understand
needing to wait for an IE action to complete. But I would expect the delay
either __before__ the loop (i.e. after initiating the IE action) or
__within__ the loop.
(Note: I am not taking the time to try to understand the nature of the
loops to see where the delay makes sense to me, based on my network
programming experience in general.)
Finally, as an aside, I would put a call to DoEvents after each update of
Application.StatusBar. In my experience, that is necessary in order to
ensure that the statebar updates are reliable. Calling DoEvents yields the
CPU to Excel. Arguably, it might be less necessary on multi-CPU
architectures.
(In fact, for the same reason, it might be prudent to call DoEvents after
each IE operation. However, I don't know how VBA implements those
operations. Perhaps it yields the CPU automagically, for example waiting
for networking status.)
Good luck with your debugging. I don't believe your difficulties have
anything to do with the behavior of the stop statement in procedures called
from other workbooks using Application.Run.
----- original message -----
I don't think so, cuz I don't have any .xlsx files. Here's part of my code
Here's the segment in wb2 that calls macro 1 in wb1
Workbooks.Open Filename:="C:\Users\Ron\Documents\MS
Files\Excel\BUDX\Airfares - Travelocity Search.xls"
Application.Run "'Airfares - Travelocity
Search.xls'!Best_Airfares_by_Location"
Here's the macro in wb2 that contains the stop commands. This macro opens
IE, navigates to the Travelocity website and brings back "best fares" for
destinations I have listed in wb2. Sometimes it doesn't put a fare into wb2
so I put in some stops to try and figure out where the data was being
dropped.
Dim airport(10)
Dim airport_price(10)
Sub Best_Airfares_by_Location_2()
' Supply a status bar caption
Application.StatusBar = "Determining the fares to the listed cities"
' Identify the airport codes for the trips of interest
Worksheets("Locations").Activate
Cells.Find(What:="Best Price", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(-1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
number_trips = Selection.Columns.Count
ActiveCell.Select
For I = 1 To number_trips
airport(I) = ActiveCell.Offset(0, I - 1)
Next
' Connect to the Travelocity website
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "
http://www.travelocity.com/"
.Top = 0
.Left = 30
.Height = 400
.Width = 400
' Loop until the page is fully loaded
page_check = ""
Do Until InStr(1, page_check, "Enter your origin and destination
cities", vbTextCompare) > 0
On Error Resume Next ' an error is thrown if innerhtml is
not yet available
page_check = ie.Document.body.innerhtml
Loop
End With
' Begin to loop through the trips
For Y = 1 To number_trips
' Determine if this is a single- or multi-destination trip
aa = Len(airport(Y))
bb = Replace(airport(Y), "-", "", 1, -1, vbTextCompare)
cc = Len(bb)
numb_hyphens = aa - cc
' Update the status bar
If numb_hyphens = 0 Then
Application.StatusBar = "Determining the lowest fare for DEN-" &
airport(Y)
Else
Application.StatusBar = "Determining the lowest fare for " &
airport(Y)
End If
' If Y>1 then reload the RT Flights web page
If Y > 1 Then
ie.Navigate "
http://www.travelocity.com/"
page_check = ""
Do Until InStr(1, page_check, "Enter your origin and destination
cities", vbTextCompare) > 0
On Error Resume Next ' an error is thrown if
innerhtml is not yet available
page_check = ie.Document.body.innerhtml
Loop
Application.Wait (Now + TimeValue("0:00:05"))
End If
' Proceed differently depending if this is a multi-destination flight or not
If numb_hyphens = 0 Then ' then it is a single destination trip
' Input the necessary information
ie.Document.getelementbyID("sub-nav-fo").Click ' need to
click to make sure the "round-trip" radio button is selected and that the
round-trip options are displayed
ie.Document.getelementbyID("fo-from").Value = "DEN"
ie.Document.getelementbyID("fo-to").Value = airport(Y)
depart_date = Format((Now() + 30), "mm/dd/yyyy")
return_date = Format((Now() + 35), "mm/dd/yyyy")
ie.Document.all.Item("radioexactDates").Click ' select exact
dates
ie.Document.getelementbyID("fo-fromdate").Value = depart_date
ie.Document.getelementbyID("fo-todate").Value = return_date
ie.Document.getelementbyID("fo-fromtime").selectedindex = 27
ie.Document.getelementbyID("fo-fromtime").selectedindex = 27
ie.Document.all.Item("fo-adults").selectedindex = 1
ie.Document.forms("form-fo").submit
' Load the web page, select all and copy, then assign the clipboard contents
to a variable and parse
' the data for the lowest fare
page_check2 = ""
Do Until (InStr(1, page_check2, "Your flight from",
vbTextCompare) > 0 Or InStr(1, fare_data, "Your trip to", vbTextCompare))
On Error Resume Next ' an error is thrown if
innerhtml is not yet available
page_check2 = ie.Document.body.innerhtml
Loop
Application.Wait (Now + TimeValue("0:00:20"))
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
' In order to assign the clipboard contents to a variable, Tools-References:
Microsoft Forms 2.0 Object
' Library must be selected
Set my_clipbd = CreateObject("htmlfile")
fare_data = my_clipbd.ParentWindow.ClipboardData.GetData("text")
Set my_clipbd = Nothing
' Empty the clipboard to speed up closing
Application.CutCopyMode = False
' Extract the data
pos_1 = InStr(1, fare_data, "flights starting at",
vbTextCompare)
pos_2 = InStr(pos_1, fare_data, "$", vbTextCompare)
pos_3 = InStr(pos_2, fare_data, "total per person",
vbTextCompare)
airport_price(Y) = Mid(fare_data, pos_2, pos_3 - pos_2)
airport_price(Y) = Replace(airport_price(Y), Chr(10), "", 1, -1,
vbBinaryCompare)
airport_price(Y) = Replace(airport_price(Y), Chr(13), "", 1, -1,
vbBinaryCompare)
airport_price(Y) = Trim(airport_price(Y))
If Len(airport_price(Y)) < 2 Then ' this covers the case
were the price is empty or $
rr = MsgBox("The single-destination airfare was not
retrieved; click ""OK"" to debug", vbOKOnly, "Error Message")
Stop
Else
End If
Else ' make sure the
"multi-destination" radio button is selected
ie.Navigate
"
http://travel.travelocity.com/flights/GoToNewSearch.do"
page_check = ""
Do Until InStr(1, page_check, "Multi-destination",
vbTextCompare) > 0
On Error Resume Next ' an error is thrown if
innerhtml is not yet available
page_check = ie.Document.body.innerhtml
Loop
Application.Wait (Now + TimeValue("0:00:05"))
ie.Document.all.Item("multicity").Click
depart_date = Format((Now() + 30), "mm/dd/yyyy")
pos_0 = 1
For z = 1 To numb_hyphens + 1
' Extract the airport names; the first and last airport will be "DEN", all
of the other
' "leavingFrom" airports will match the preceding "goingTo" airport
pp = WorksheetFunction.Substitute(airport(Y), "-", "8", z)
pos_1 = InStr(1, pp, "8", vbTextCompare) ' then you're at
the last airport
If pos_1 = 0 Then ' pos_1=0 for the last airport in the
series, e.g. there are no dashes after it
airport(z) = Mid(airport(Y), pos_0, Len(airport(Y)) -
(pos_0 - 1))
Else
airport(z) = Mid(airport(Y), pos_0, pos_1 - (pos_0))
End If
pos_0 = pos_1 + 1
Select Case z
Case 1 ' first airport
ie.Document.getelementbyID("leavingFrom1").Value =
airport(z)
ie.Document.getelementbyID("fromdateMC1").Value =
depart_date
ie.Document.getelementbyID("leavingTime1").selectedindex
= 27
Case numb_hyphens + 1 ' last airport
ie.Document.getelementbyID("goingTo" & z - 1).Value =
airport(z)
Case Else
ie.Document.getelementbyID("goingTo" & z - 1).Value =
airport(z)
ie.Document.getelementbyID("leavingFrom" & z).Value =
airport(z)
ie.Document.getelementbyID("fromdateMC" & z).Value =
depart_date
ie.Document.getelementbyID("leavingTime" &
z).selectedindex = 27
End Select
depart_date = Format(Now() + 30 + (5 * (z)), "mm/dd/yyyy")
Next
ie.Document.all.Item("adults").selectedindex = "1"
ie.Document.getelementbyID("submitButton").Click
page_check3 = ""
Do Until InStr(1, page_check3, "Your multi-destination trip",
vbTextCompare) > 0
On Error Resume Next ' an error is thrown if
innerhtml is not yet available
page_check3 = ie.Document.body.innerhtml
Loop
Application.Wait (Now + TimeValue("0:00:05"))
' Make sure the results are sorted by price
ie.Navigate
"
http://travel.travelocity.com/fligh...NewTimeStamp=Y&sortOrder=TOTAL_FARE_ASCENDING"
page_check4 = ""
Do Until InStr(1, page_check4, "Your multi-destination trip",
vbTextCompare) > 0
On Error Resume Next ' an error is thrown if
innerhtml is not yet available
page_check4 = ie.Document.body.innerhtml
Loop
Application.Wait (Now + TimeValue("0:00:05"))
' Select all and copy the web page to the clipboard
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
' In order to assign the clipboard contents to a variable, Tools-References:
Microsoft Forms 2.0 Object
' Library must be selected
Set my_clipbd = CreateObject("htmlfile")
fare_data = my_clipbd.ParentWindow.ClipboardData.GetData("text")
Set my_clipbd = Nothing
' Empty the clipboard to speed up closing
Application.CutCopyMode = False
' Assign the web page html to a variable and parse the data for the lowest
fare
pos_5 = InStr(1, fare_data, "includes taxes and fees",
vbTextCompare)
pos_6 = InStr(pos_5, fare_data, "$", vbTextCompare)
pos_7 = InStr(pos_6, fare_data, "total price", vbTextCompare)
airport_price(Y) = Trim(Mid(fare_data, pos_6, pos_7 - pos_6))
airport_price(Y) = Replace(airport_price(Y), Chr(10), "", 1, -1,
vbBinaryCompare)
airport_price(Y) = Replace(airport_price(Y), Chr(13), "", 1, -1,
vbBinaryCompare)
airport_price(Y) = Trim(airport_price(Y))
If Len(airport_price(Y)) < 2 Then ' this covers the case
were the price is empty or $
rr = MsgBox("The multi-destination airfare was not
retrieved; click ""OK"" to debug", vbOKOnly, "Error Message")
Stop
Else
End If
End If
Next
' Return control of status bar to Excel
Application.StatusBar = False
' Position for data entry
' HERE IS WHERE I START USING THE STOPS
Worksheets("Locations").Activate
Cells.Find(What:="Current Price", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
Selection.EntireRow.Insert
Rows("9:9").Select
Selection.Font.Bold = False
Range("A9").Select
Selection.Cut Destination:=Range("A8")
For I = 1 To number_trips
ActiveCell.Offset(-1, I).Value = airport_price(I)
If Len(ActiveCell.Offset(-1, I).Value) < 2 Then ' this covers
the case were the price is empty or $
rr = MsgBox("The airfare placed in the worksheet is incomplete;
click ""OK"" to debug", vbOKOnly, "Second Error Check")
Stop
Else
End If
' Is this the lowest fare yet?
If ActiveCell.Offset(-1, I) < ActiveCell.Offset(-2, I) Then
ActiveCell.Offset(-2, I) = airport_price(I)
End If
Next
' Enter the date and day of the week
Cells.Find(What:="fly date", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
ActiveCell.Offset(3, 0).Activate
ActiveCell.Value = Format((Now() + 30), "mm/dd/yyyy")
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = Format(ActiveCell.Offset(0, -1).Value, "ddd")
' Error check for missing data, remove when problem solved
Range("A9").Select
Stop
For H = 1 To number_trips
If Len(ActiveCell.Offset(-1, H).Value) < 2 Then ' this covers
the case were the price is empty or $
rr = MsgBox("The airfare placed in the worksheet is incomplete;
click ""OK"" to debug", vbOKOnly, "Final Error Check")
Stop
Else
End If
Next
' Close IE
ie.Quit
' Posotion for close
Range("AZ150").Select
ActiveWindow.LargeScroll Down:=-6
ActiveWindow.LargeScroll ToRight:=-3
End Sub
