将URL中的数据导入excel-Java 学习之路

我是excel vba的新手 . 我有网站网址中的数据 . 我需要导入工作簿表 . 我能够创建工作表和导入，而我想导入特定的工作表 .

这一项工作

Sub Test()
    Application.DisplayAlerts = False
    strURL = "https://www.nseindia.com/products/dynaContent/equities/equities/histscrip.jsp?symbolCode=1693&symbol=AXISBANK&symbol=axisbank&segmentLink=17&symbolCount=1&series=ALL&dateRange=1month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE"
    Application.Workbooks.Open (strURL)
    Application.DisplayAlerts = True
End Sub

但是，修改代码后无效 . 任何帮助将非常感激 .

Sub OpenCSV()
Application.DisplayAlerts = False
strURL = "https://www.nseindia.com/products/dynaContent/equities/equities/histscrip.jsp?symbolCode=1693&symbol=AXISBANK&symbol=axisbank&segmentLink=17&symbolCount=1&series=ALL&dateRange=1month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE"
Worksheets("dump").Range("A1").Select
With Selection
    .Open (strURL)
End With
Application.DisplayAlerts = True

End Sub

1 回答

使用查询表并指定目标工作表和范围 .

Option Explicit
Public Sub testing()
    Dim qt As QueryTable
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Const URL As String = "https://www.nseindia.com/products/dynaContent/equities/equities/histscrip.jsp?symbolCode=1693&symbol=AXISBANK&symbol=axisbank&segmentLink=17&symbolCount=1&series=ALL&dateRange=1month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE"
    Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=ws.Range("A1"))

    With qt
        .RefreshOnFileOpen = True
        .FieldNames = True
        .WebSelectionType = xlSpecifiedTables
        .WebTables = 1
        .Refresh BackgroundQuery:=False
    End With
End Sub

回复于 2024-05-19T04:05:43+08:00

将URL中的数据导入excel

1 回答

相关问题