ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Stop Command Question (https://www.excelbanter.com/excel-programming/448897-stop-command-question.html)

[email protected][_2_]
Stop Command Question
 
I have a macro (let's say in macro1 in workbook1) I was checking out, so I put a stop command in the code so that execution would pause and I could see what data the macro had transferred to the worksheet and see if everything was correct. The stop command works fine, just as expected. However, if I open workbook1 from a macro2 in workbook2 and then run macro1 using Application.Run, the stop command doesn't halt execution, macro1 just runs to completion. Is that always the case that if you call a macro from a macro in a different workbook, stop commands don't function?..TIA, ron

joeu2004[_2_]
Stop Command Question
 
" wrote:
if I open workbook1 from a macro2 in workbook2 and then
run macro1 using Application.Run, the stop command doesn't
halt execution, macro1 just runs to completion. Is that
always the case that if you call a macro from a macro in a
different workbook, stop commands don't function?

Works for me. See the example below. But the devil might be in the
details, which you neglect to provide. Post all macros; or better, post the
URL of an example Excel file uploaded to a file-sharing website. See a list
of some free websites below.

What worked for me....

In a regular module in book1:

Sub doit1()
Workbooks.Open "C:\...complete path...\book2.xlsm"
Application.Run "book2.xlsm!doit2"
End Sub

In a regular module in book2.xlsm, save and closed when doit1 above is
executed:

Sub doit2()
Stop
MsgBox "called: book2!doit2"
End Sub

FYI, I discovered that the Worksbooks.Open statement is superfluous to some
degree. VBA will open book2.xlsm in the current working directory (or
PATH?) automatically when Application.Run is executed, if it is not open
already.

However, if you wrote "book2!doit2" instead on the Application.Run
statement, and book2.xlsx (not macro-enabled) exists in the current working
directory (or PATH?), Application.Run will open book2.xlsx and try to call
doit2 in book2.xlsx instead of book2.xlsm, even if the latter is open.

Could that explain the behavior that you are seeing?


[email protected][_2_]
Stop Command Question
 
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 Y1 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").selecte dindex = 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/flights/DirectorAction.do?nextAction=redrawOutbound&breadc rumbStart=1&dispNewTimeStamp=Y&sortOrder=TOTAL_FAR E_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

joeu2004[_2_]
Stop Command Question
 
" wrote:
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 -----

" wrote in 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 Y1 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").selecte dindex
= 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/flights/DirectorAction.do?nextAction=redrawOutbound&breadc rumbStart=1&dispNewTimeStamp=Y&sortOrder=TOTAL_FAR E_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


[email protected][_2_]
Stop Command Question
 
Joe...Thank you for taking the time to solve my problem. You were correct, I had failed to change the Application.run to call the new "_2" debugging routine. Thank you for your other tips and suggestions too...Best regards, ron

joeu2004[_2_]
Stop Command Question
 
" wrote:
Joe...Thank you for taking the time to solve my problem.
You were correct, I had failed to change the Application.run
to call the new "_2" debugging routine.

Ha! It was that simple?! I find it always helps to have another pair of
eyes look at "impossible" problems, even if they don't know what you're
doing. Glad to be of service.



All times are GMT +1. The time now is 07:01 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com