ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó¥HVBAÂ^¨úºô¯¸¤Wªº¸ê®Æ¡H

¨ä¹ê§A¿ý»s¤@¤U´N¥i±o¨ì¥N½X¡A¦A§â2610§ï¦¨Àx¦s®æ¦ì§}¦p[a1], ¦p¦¹¦Ó¤w¡C
  1. Sub Macro2()
  2.     With ActiveSheet.QueryTables.Add(Connection:= _
  3.         "URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
  4.         Destination:=Range("A3"))
  5.         .Name = "company_" & [a1]
  6.         .FieldNames = True
  7.         .RowNumbers = False
  8.         .FillAdjacentFormulas = False
  9.         .PreserveFormatting = True
  10.         .RefreshOnFileOpen = False
  11.         .BackgroundQuery = True
  12.         .RefreshStyle = xlInsertDeleteCells
  13.         .SavePassword = False
  14.         .SaveData = True
  15.         .AdjustColumnWidth = True
  16.         .RefreshPeriod = 0
  17.         .WebSelectionType = xlSpecifiedTables
  18.         .WebFormatting = xlWebFormattingNone
  19.         .WebTables = "9"
  20.         .WebPreFormattedTextToColumns = True
  21.         .WebConsecutiveDelimitersAsOne = True
  22.         .WebSingleBlockTextImport = False
  23.         .WebDisableDateRecognition = False
  24.         .WebDisableRedirections = False
  25.         .Refresh BackgroundQuery:=False
  26.     End With
  27. End Sub
½Æ»s¥N½X

TOP

³o¬Oexcelªº»¡©ú¤¤ªº½d¨Ò¼gªk¡A¤ñ¿ý»s±oªº¥N½X²¼ä¦h¤F
  1. Sub qq()
  2. Set shFirstQtr = ActiveSheet
  3. Set qtQtrResults = shFirstQtr.QueryTables _
  4.     .Add(Connection:="URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
  5.         Destination:=shFirstQtr.Cells(3, 1))
  6. With qtQtrResults
  7.     .WebFormatting = xlNone
  8.     .WebSelectionType = xlSpecifiedTables
  9.     .WebTables = "9"
  10.     .Refresh
  11. End With
  12. End Sub
½Æ»s¥N½X

TOP

ÁÂÁÂoobirdª©¥D¤j¤j¡C
·PÁ±zÀ°¤p§Ì¶}±Ò³o¤@®°¤jªù¡A
¦P®É¤]À°¤p§Ì·h¶}¤ß¤¤ªº¤@¶ô
¤j¥ÛÀY¡C
¤@»yÂI¿ô¦p¦¹¦n¥ÎªºEXCEL
¿ý»s¥\¯à¡A¤p§Ì«o±N¥¦¿ò§Ñ¡C

¥t¤p§Ì·Q«Å§i³]©w
DIM qtQtrResults AS ......
¤~¯àÅý¥¦¦bWITH qtQtrResults®É
¯àÅã¥Ü¥X¨äÄݩʤΥ\¯à©O¡H
With qtQtrResults
    .WebFormatting = xlNone
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "9"
    .Refresh
End With

·P®¦¤j¤j¡I

TOP

Dim qtQtrResults As QueryTable

TOP

ÁÂÁÂoobirdª©¥D¤j¤j¡C
·PÁ±z¤Þ»â¤p§Ì¶i¤J
¥t¤@­Ó·sªº»â°ì¡C

·P®¦¤j¤j¡I

TOP

Sub qq()
Set shFirstQtr = ActiveSheet
Set qtQtrResults = shFirstQtr.QueryTables _
    .Add(Connection:="URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
        Destination:=shFirstQtr.Cells(3, 1))
With qtQtrResults
    .WebFormatting = xlNone
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "9"
    .Refresh
End With
End Sub
§Ú·Q°Ý°Ý¨º»ò¦b³o¬q¥N½X¤¤
.Refreshstyle ¥i¥H´¡¦b¨º­Ó¦ì¸m¡H¡H
¦]¬°§Ú·Q³o­Ó¬OÂл\¸ê®Æ¦Ó¤£¬O¤£Â_´¡¤J

TOP

¤w§ä¨ì.Refreshstyleªº¦ì¸m
¦ý§ä¤£¨ì«ç¼Ë¥u¾É¤J²Ä¤@Ä檺¸ê®Æ¢I¢I
VBA·s¤â

TOP

½Ð°Ý ¦pªG¥u·Q§ì ºô¯¸¤Wªº³¡¤À¸ê®Æ ¤U¨Ó¡A VBA ­n¦p¦ó¼g ?
  1. Sub ´Á³f¥æ©ö¤f¼Æ()
  2. Set shFirstQtr = ActiveSheet
  3. Set qtQtrResults = shFirstQtr.QueryTables _
  4.     .Add(Connection:="URL;http://www.taifex.com.tw/chinese/3/7_12_3_tbl.asp", _
  5.         Destination:=shFirstQtr.Cells(3, 1))
  6. With qtQtrResults
  7.     .WebFormatting = xlNone
  8.     .WebSelectionType = xlSpecifiedTables
  9.     .WebTables = "2"
  10.     .Refresh
  11. End With
  12. End Sub
½Æ»s¥N½X

§ì´Á³f¤f¼Æ.jpg (124 KB)

´Á³f¥æ©ö¤f¼Æ

§ì´Á³f¤f¼Æ.jpg

TOP

¦^´_ 5# GBKEE

¤j¤j½Ð°Ý¤@¤U¡A§Ú¥Î¤U­±³o­Óµ{¦¡§ì¥Xªº¸ê®Æ¸ò­ì©lºô¯¸ªº¸ê®Æ¤ñ¸û¤U¡A¤U­±¦³¤@¬q¨S¦³§ì¤£¨ì¡A¬O¤£¬O¦³°Ñ¼Æ¨S³]¦n? ­nwait¶Ü?¦]¬°¤U­±¦³Åã¥ÜLoading more data...¦ý¬O§Ú¤£ª¾¹D«ç¼ËÅý¥L¶]§¹

­ì©lºô¯¸: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d

Sub QueryTable()
    Const xlURL As String = "https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d"
    With ActiveSheet.QueryTables.Add("URL;" & xlURL, Destination:=Range("$A$1"))
        .WebFormatting = xlWebFormattingNone
        .TablesOnlyFromHTML = False
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .Refresh 0
    End With
End Sub

TOP

¦^´_ 5# GBKEE

GBKEE¤j¤j¡A§Ú­ì¥»·Q¥Î³o´X¤Ñ°Ý§Aªº¡A§ìCrumb¦ì¸m¨Ó¤U¸ücsvÀɮסA¦ý¨S¦³¦¨¥\¡A©Ò¥H·Q´«¤U­±¨º­Óºô§}¸Õ¸Õ¡A¦ý§ì¥Xªº¸ê®Æ¤£§¹¾ã¡A¦pªG¥i¥H§ì¨ú§¹¾ã´N¤Ó¦n¤F
https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d
¬õ®Ø¤U­±¬O­n§ìªº¸ê®Æ:

javascript:;

Data 1.PNG (52.44 KB)

Data 1.PNG

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD