使用VBA将网页数据传输到Excel工作表 [英] Transferring webpage data to an Excel Worksheet using VBA

问题描述

这是我的第一篇文章.我是VBA的新手，但我对VB6非常熟悉.我写了一些代码，将纳斯达克的文本粘贴到工作表中.终于可以了.在年度损益表"的上方和下方散布着许多无关紧要的数据.我想解析重要数据并将其放置在可以自动进行分析的位置.我认为我可以搜索单元格，直到找到:年度损益表，然后提取到另一张纸上.任何建议将不胜感激.这就是我所拥有的:

This is my first post. I am new at VBA but I'm pretty familiar with VB6. I wrote some code that takes the text from nasdaq and pastes it into a worksheet. It finally works. There is a lot of extraneous data scattered around above and below The Annual Income Statement. I would like to parse out and place the important data in a place where I can automate analysis. I'm thinking that I could search the cells until I find: Annual Income Statement and extract to a different sheet. Any suggestions would be very appreciated. Here's what I've got:

Sub TransferWebData()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate "http://www.nasdaq.com/symbol/gd/financials" 
    Do Until .ReadyState = 4: DoEvents: Loop
    IE.ExecWB 17, 0 'SelectAll
    IE.ExecWB 12, 2 'Copy selection

    Sheets("GD").Range("A1").Select
    Sheets("GD").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    IE.Quit
End With
End Sub

推荐答案

您还可以导入多个股票行情显示的损益表行项目.

You could also import Income Statement line items for multiple stock tickers.

Sub ImportYrlyFS()
ThisSheet = ActiveSheet.Name
Range("A2").Select
Do Until ActiveCell.Value = ""
Symbol = ActiveCell.Value
Sheets(ThisSheet).Select
Sheets.Add
Dim QT As QueryTable
Symbol = UCase(Symbol)
myurl = "http://finance.yahoo.com/q/is?s=" & Symbol & "+Income+Statement&annual"
Set QT = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & myurl, _
Destination:=Range("A1"))
With QT
.WebSelectionType = xlSpecifiedTables
.WebTables = "9"
.Refresh BackgroundQuery:=False
End With
QT.Delete
Sheets(ActiveSheet.Name).Name = Symbol
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

您的工作表应如下所示.

Your sheet should look like this.

这篇关于使用VBA将网页数据传输到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！