Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with altering a download macro
the following is the front end of a macro to connect to a web site and
download into excel in comma delimited format. the website has been changed from dowloading the file in comma delimited from website to saving download file to desktop then import into excel. Sub GetOptionChain() ' SINGLE DOWNLOAD ' Dim wbk_Tmp As Workbook Dim Cl As Range Dim Symbol As String Dim connectURL As String Dim LastRow As Integer Dim SearchFrac As String Dim Pos As Integer, x As Integer, y As Integer Dim Numerator As Integer, Denominator As Integer Dim FracVal As String, Fraction As String Dim SelectIt As String Dim DateHold As Variant SelectIt = "Some options chains are too large for Excel to" & vbLf SelectIt = SelectIt & "download itself. If you find the macro cannot" & vbLf SelectIt = SelectIt & "download the option chain, you can download it" & vbLf SelectIt = SelectIt & "yourself, using the ""Download Direct from CBOE""" & vbLf SelectIt = SelectIt & "link below the button for the macro. You can" & vbLf SelectIt = SelectIt & "then use the macro to format the file you downloaded." & vbLf SelectIt = SelectIt & "Try to download with the macro first, though." & vbLf & vbLf SelectIt = SelectIt & "Download the file from the web? (Otherwise you will" & vbLf SelectIt = SelectIt & "have to find it on your hard drive.)" x = MsgBox(SelectIt, vbYesNoCancel + vbDefaultButton1, "Download or Text File") Select Case x Case vbNo SelectIt = Application.GetOpenFilename() Application.ScreenUpdating = False If SelectIt = "False" Then Exit Sub Workbooks.Open FileName:=SelectIt GoTo Process Case vbCancel Exit Sub End Select Symbol = InputBox("Enter a stock symbol to retrieve its option chain from CBOE:", "Stock Symbol") If Symbol = "" Then Exit Sub Application.ScreenUpdating = False connectURL = "URL;http://quote.cboe.com/QuoteTable.dat" ''''''''''http://quote.cboe.com/QuoteTableDownload.asp ////////////////////////// new connction site 'Connect and put results in A1 Workbooks.Add With ActiveSheet.QueryTables.Add(Connection:=connectURL , Destination:=Range("A1")) .BackgroundQuery = True .TablesOnlyFromHTML = True .PostText = "TICKER=" & Symbol .Refresh BackgroundQuery:=False 'DL text data .SaveData = True End With Process: Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1)) 'DoneWithFrac: Range("A:A,H:H").Replace What:="-", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False On Error GoTo 0 'Parse year/mo/strike/underlying/code info into separate columns Columns("I:M").Insert Shift:=xlToRight Columns("B:F").Insert Shift:=xlToRight Range(Range("A4"), Range("A4").End(xlDown)).TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) Range(Range("M4"), Range("M4").End(xlDown)).TextToColumns Destination:=Range("M4"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) END SUB how would I modify the following marco ? the old site used by connect URL was http://quote.cboe.com/QuoteTable.dat and the macro imorted it as comma delimited the new site is http://quote.cboe.com/QuoteTableDownload.asp what modifications would be required to original macro include the save to desktop then import into excel thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
items chosen to download are NEVER all there after download comple | New Users to Excel | |||
macro to download a file. | Excel Discussion (Misc queries) | |||
Macro did not run after download file from net | Excel Worksheet Functions | |||
Macro did not run after download file from net | Excel Worksheet Functions | |||
Help with altering a SaveAs macro . . . | Excel Programming |