click an internet pop-up "OK" button

  • Thread starter Thread starter matt
  • Start date Start date
M

matt

I have coded a program that takes the ticker symbols from one
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs it into another
worksheet(2), runs a calculation based off the pricing information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.

The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small message box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....."

I want to know if there is a way to click the pop-up "OK" button for
the "Could not open...." message box. I could write an "On Error"
statement to catch the end/debug window after clicking "OK", but I
don't know how to get passed the "Could not open...." message box. (I
want to run this on several ticker symbols, and it would be a pain to
have to manually click the "OK" button each time this type of
situation occurs).

I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the "Could not open...." message box are the following:
RMK, HCA, RUSMF, ITWG.

Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is selected).

Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard

Set myData = New DataObject

wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name

Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count

For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?
s=" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" _
& d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents
Workbooks(wbCSV).Activate
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial

myData.SetText ""
myData.PutInClipboard

Workbooks(wbCSV).Close savechanges:=False

Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function
 
I added some error checking statement to handle your problem. I couldn't get
any CSV files from Yahoo so I wasn't able to fully test all the code. I did
get the errors and was able to continue after the error conditions with the
new code.

Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard
Dim MyError

On Error GoTo SetError
Set myData = New DataObject

wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name

Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count

For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

MyError = False

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?" = "" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" _
& d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"

If MyError = True Then MsgBox ("Could not find ticker symbols")
MyError = False
On Error GoTo SetError

Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents

Workbooks(wbCSV).Activate
If MyError = True Then
MsgBox ("Could not find file table.csv")
MyError = False
Else
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial
End If

myData.SetText ""
myData.PutInClipboard

' Workbooks(wbCSV).Close savechanges:=False

Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next
SetError:
MyError = True
On Error Resume Next
Resume
End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function


matt said:
I have coded a program that takes the ticker symbols from one
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs it into another
worksheet(2), runs a calculation based off the pricing information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.

The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small message box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....."

I want to know if there is a way to click the pop-up "OK" button for
the "Could not open...." message box. I could write an "On Error"
statement to catch the end/debug window after clicking "OK", but I
don't know how to get passed the "Could not open...." message box. (I
want to run this on several ticker symbols, and it would be a pain to
have to manually click the "OK" button each time this type of
situation occurs).

I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the "Could not open...." message box are the following:
RMK, HCA, RUSMF, ITWG.

Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is selected).

Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard

Set myData = New DataObject

wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name

Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count

For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?
s=" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" _
& d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents
Workbooks(wbCSV).Activate
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial

myData.SetText ""
myData.PutInClipboard

Workbooks(wbCSV).Close savechanges:=False

Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next

End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function
Click to expand...
 
I added some error checking statement to handle your problem. I couldn't get
any CSV files from Yahoo so I wasn't able to fully test all the code. I did
get the errors and was able to continue after the error conditions with the
new code.

Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard
Dim MyError

On Error GoTo SetError
Set myData = New DataObject

wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name

Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count

For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value

mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")

a = monthToNumber(mon)
b = dy
c = yr - 10

d = a
e = b
f = yr

MyError = False

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?" = "" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" _
& d & "&e=" & e & "&f=" & f & "&g=d&ignore=.csv"

If MyError = True Then MsgBox ("Could not find ticker symbols")
MyError = False
On Error GoTo SetError

Workbooks(wbPT).Worksheets(2).Range("a1").CurrentRegion.ClearContents

Workbooks(wbCSV).Activate
If MyError = True Then
MsgBox ("Could not find file table.csv")
MyError = False
Else
Range("a1").CurrentRegion.Copy
Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpecial
End If

myData.SetText ""
myData.PutInClipboard

' Workbooks(wbCSV).Close savechanges:=False

Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next
SetError:
MyError = True
On Error Resume Next
Resume
End Sub

Private Function monthToNumber(ByVal mon)

Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select

End Function



matt said:
I have coded a program that takes the ticker symbols from one
worksheet(1) [i.e. A1 = ticker1, A2 = ticker2....], gets the pricing
information from Yahoo!Finance and inputs it into another
worksheet(2), runs a calculation based off the pricing information,
and copies and pastes the output to another worksheet(3). When the
calculation is complete the computer clears out the pricing
information from worksheet(2) and moves to the next ticker symbol.
Click to expand...
The problem however is that if Yahoo!Finance does NOT have pricing
information for the specified ticker, a small message box pops up that
reads "Could not open "http://ichart.finance.yahoo.com/table.csv?
s= ......." When I click "OK" the program execution returns "Run-time
error '1004': Microsoft Office Excel cannot access the file 'http://
ichart.....' There are serveral possible reasons:....."
Click to expand...
I want to know if there is a way to click the pop-up "OK" button for
the "Could not open...." message box. I could write an "On Error"
statement to catch the end/debug window after clicking "OK", but I
don't know how to get passed the "Could not open...." message box. (I
want to run this on several ticker symbols, and it would be a pain to
have to manually click the "OK" button each time this type of
situation occurs).
Click to expand...
I have included my code below. A list of tickers that work as
expected are the following: JNJ, ADBE, APOL, LLTC. A list of tickers
that produce the "Could not open...." message box are the following:
RMK, HCA, RUSMF, ITWG.
Click to expand...
Any help is appreciated. Thanks in advance. (Also, make sure that
VBE: Tools/References/Microsoft Forms 2.0 Object Library is selected).
Click to expand...
Option Explicit
Sub yahooCSV2()
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Dim wbCSV 'workbook CSV
Dim wbPT 'workbook Pricing Tool
Dim counter 'counter for the loop
Dim outputCounter 'counter for the output
Dim answer 'value of calculation
Dim m 'marker for the loop
Dim myData As DataObject 'to clear the clipboard
Click to expand...
Set myData = New DataObject
Click to expand...
wbCSV = "table.csv"
wbPT = ActiveWorkbook.Name
Click to expand...
Worksheets(1).Select
counter = Range("a1").CurrentRegion.Rows.Count
Click to expand...
For m = 1 To counter
tic = Worksheets(1).Range("a" & m).Value
Click to expand...
mon = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy")
Click to expand...
a = monthToNumber(mon)
b = dy
c = yr - 10
Click to expand...
d = a
e = b
f = yr
Click to expand...
Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?
s=" _
& tic & "&a=" & a & "&b=" & b & "&c=" & c &
"&d=" _
& d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"

myData.SetText ""
myData.PutInClipboard
Click to expand...
Workbooks(wbCSV).Close savechanges:=False
Click to expand...
Workbooks(wbPT).Activate
outputCounter = Worksheets(3).Range("a1").CurrentRegion.Rows.Count
answer = Worksheets(2).Range("j1").Value
Worksheets(3).Range("a" & outputCounter + 1).Value = tic
Worksheets(3).Range("b" & outputCounter + 1).Value = answer
Next
Click to expand...
End Sub
Click to expand...
Private Function monthToNumber(ByVal mon)
Click to expand...
Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select
Click to expand...
End Function- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Joel,

Thanks for the response. The error handler deals with the errors that
are internal to the VBA code AFTER I click the "OK" button from the
"Could not open 'http://ichart...." error message, so my problem is
still not resolved.

The generated error that pops up is a message box that has a white
bubble on the left-hand side and the white bubble has a blue, lower
case "i" in it. The caption of the message box reads "Could not open
(then a carriage return, i.e. Chr(13)) 'http://
ichart.finance.yahoo.com/table.csv?s=.....csv'."

I ran through the program again and a list of tickers (that I double
checked) that produce the aforementioned error are JW.A, HCA, ITWG,
KHD, KOSP, and TALK.

I've also included the "Workbooks.Open" line without any "_"
continuation markers to try and avoid ambiguity in the way google
posts the code to the group window. You're code was without the "s"
after the "?" in ...com/table.csv?.

Workbooks.Open Filename:="http://ichart.finance.yahoo.com/table.csv?
s=" & tic & "&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e
& "&f=" & f & "&g=d&ignore=.csv"

Again, if you know how to programmatically click the "OK" on the
"Could not open 'http://ichart.finance...." meassage box that has the
white bubble with a lower case, blue "i" in the bubble, the help would
be much apprecitated. Thanks again for the help so far.

Matt
 
Back
Top