Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
click an internet pop-up "OK" button
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").CurrentR egion.ClearContents Workbooks(wbCSV).Activate Range("a1").CurrentRegion.Copy Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpe cial 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
click an internet pop-up "OK" button
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").CurrentR egion.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").PasteSpe cial 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" wrote: 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").CurrentR egion.ClearContents Workbooks(wbCSV).Activate Range("a1").CurrentRegion.Copy Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpe cial 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
click an internet pop-up "OK" button
On Mar 8, 11:45 pm, Joel wrote:
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").CurrentR egion.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").PasteSpe cial 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" wrote: 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").CurrentR egion.ClearContents Workbooks(wbCSV).Activate Range("a1").CurrentRegion.Copy Workbooks(wbPT).Worksheets(2).Range("a1").PasteSpe cial 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- Hide quoted text - - Show quoted text - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Click on cell (or "button"), takes you to appropriate information | Excel Worksheet Functions | |||
"Next" button for reading NG from Internet | New Users to Excel | |||
Excel forms button "click" | Excel Programming | |||
how to stop program with loop by click "Cancel" button | Excel Programming | |||
commnd button - when i rt click, "assign macro" isnt an option... | Excel Programming |