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