VBA: 建立Web查詢擷取基金曆史淨值

文章背景：工作生活中，有時需要将網頁中的資料導入Excel，然後進行資料處理。為了便于資料更新，可以建立Web查詢。下面以擷取某隻基金的機關淨值為例，檢視該基金近一個月的機關淨值。

網址：http://jingzhi.funds.hexun.com/database/jzzs.aspx?fundcode=270010

在

查詢基金淨值

的按鈕中添加如下的宏代碼：

Option Explicit

Sub QueryStarter()

    'This is an adaptation from some code found at:
    'https://stackoverflow.com/questions/19306832/web-query-vba-refresh
    
    Dim time_ini As Date, ws As Worksheet, url As String
    
    '計時
    time_ini = Timer
    
    Set ws = Worksheets.Add
    
    url = "URL;" & Worksheets("Web查詢").Range("B1")
    
    With ws.QueryTables.Add(Connection:=url, Destination:=ws.Range("A1"))
    
        .Name = "My Query"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        
    End With
    
    MsgBox "Done!  " & vbCrLf & "用時：" & Format(Timer - time_ini, "0.0s")

End Sub           

複制

代碼運作效果：http://mpvideo.qpic.cn/0bf2naaccaaaqiandnc6ynqfa2gdefuaaiia.f10002.mp4?dis_k=5dea541298abe82e64b6e62700b75b78&dis_t=1663655364&vid=wxv_1799230978578513923&format_id=10002&support_redirect=0&mmversion=false

參考資料：

[1] Excel VBA程式設計實踐寶典[M]

[2] Excel/VBA for Creative Problem Solving, Part 3 (Projects)(https://www.coursera.org/learn/excel-vba-for-creative-problem-solving-part-3-projects)

[2] Web Query VBA Refresh(https://stackoverflow.com/questions/19306832/web-query-vba-refresh)