使用VBA从CSV超链接提取表中的一列到Excel使用VBA

Question

我想从此超链接中提取opt_class列https://www.cboe.org/publish/restrictionsall/cboerestrictedseries.csv并使用VBA导入到Excel。我没有使用VBA的经验，所以任何帮助！

这是我到目前为止。当该运行时，它把所有的数据到1个细胞在Excel

子试验（） 昏暗oXMLHTTP作为对象 昏暗sPageHTML作为字符串 昏暗SURL作为字符串

sURL = "https://www.cboe.org/publish/restrictionsall/cboerestrictedseries.csv" 

Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP") 
oXMLHTTP.Open "GET", sURL, False 
oXMLHTTP.send 
sPageHTML = oXMLHTTP.responseText 

ThisWorkbook.Sheets(1).Cells(1, 1) = sPageHTML 

MsgBox "Completed" 

Answer 1

Sub dataImport() 

     Dim wbImport  As Workbook 
     Dim wksImport  As Worksheet 
     Dim rngFind   As Range 

     '/ Open the CSV 
     Set wbImport = Workbooks.Open("https://www.cboe.org/publish/restrictionsall/cboerestrictedseries.csv") 
     Set wksImport = wbImport.Worksheets(1) 

     '/ Remove date stamp 
     wksImport.Rows(1).EntireRow.Delete 

     '/ Search for OPT_CLASS header 
     Set rngFind = wksImport.UsedRange.Cells.Find("OPT_CLASS") 

     If Not rngFind Is Nothing Then 
      '/ Found it 
      '/ Copy and paste to column A in Sheet1 of your macro workbook 
      rngFind.Resize(rngFind.End(xlDown).Row).Copy ThisWorkbook.Worksheets("Sheet1").Cells(1, 1) 
      Application.CutCopyMode = False 

      '/Close the CSV file 
      wbImport.Close False 

     Else 
      '/ Didn't find it. 
      MsgBox "No such header" 
     End If 

    End Sub