Hur man läser data från webbsidor online med Excel VBA

Denna övning visar hur man läser data från en webbplats, i detta fall aktuella valutakurser från Yahoo.com.

Det antas att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".

Förutsättningarna inkluderar kunskap om att fylla i kombinationsrutor och definiera namn.

Arbetsboken består av två ark:

Användargränssnittet:

Kombinationsrutorna Köp och Sälj refererar till det lokala databladet nedan.

Tabellen på D11 klistras in automatiskt från webbplatsen och behåller användarformatering.

Xlsm för denna övning kan laddas ner här..

Skapa applikationen själv.

Skapa ett ark med två kombinationsrutor. Kombinationsrutorna refererar till celler på ett andra ark.

Kombinationsrutorna anropar också koden för att läsa webbplatsen med hjälp av Ändra händelse.

Valutanamn kan klistras in i det andra arket ("Valutor") från listan nedan, sorteringsordningen är en fråga om personlig preferens:

Klistra in data i kolumner B och E.

Definiera namn och använd indexfunktioner enligt illustrationen:

Eftersom namn har definierats på det lokala databladet "Valutor", har blad "Main" bara att hänvisa till de definierade namnen för att få värdet, dvs = SÄLJ och = KÖP för "Main" -cellerna E10 respektive G10.

Vi kommer att programmera Excel för att läsa webben. Det har dock sin egen inbyggda process på Data-bandet, en instans som du kanske vill spela in som ett makro för att bygga olika scenarier. Observera att dator- eller webbläsarkonfigurationer kan påverka webbsidesskript negativt.

Koden

Sätt i en modul och ange följande:

Option Explicit

Sub Ticker()
  Dim currBuy As String
  Dim currSell As String

  Range("D11:F14").ClearContents      'prepare the ground for the web data
  currBuy = Range("BUY")          'get variable values from Defined Names previously set up
  currSell = Range("SELL")
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=" & currBuy & currSell & "=X", Destination:=Range("$D$11"))
    .Name = "q?s=" & currSell & currBuy & "=X"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = """table1"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
End sub

Sub DropDown2_Change()    'This event will call the routine above. Object name might differ.
    Call Ticker
End Sub

Sub DropDown1_Change()
    Call Ticker
End Sub

Spara arbetsboken som typ xlsm.

Testa koden genom att ändra värdena på kombinationsrutorna och sedan smarta upp formaten för den klistrade tabellen (fyra decimaler, rutnät, skuggningar?).

Korruption av Excel-filer

Excel är ibland känt för att skada filer när de sparas och försöker därefter sin egen självåterställningsrutin som ofta, enligt min erfarenhet, helt enkelt inte fungerar. Detta kan vara katastrofalt för användaren, eftersom det är källfilen (möjligen din enda kopia) som förstörs. Skadade Excel-filer kan dock repareras med verktyg från tredje part, vilket sparar mycket tid och ansträngning.

Författarintroduktion:

Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive reparation rar Arkiv och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com