[µo°Ý] ¨Ï¥Î msXML2.xmlHTTP ¨ú±oªÑ²¼¥æ©ö¸ê®Æ

Scott090

½Ð°Ý¤j®v«e½ú¦p¦ó¥H xmlhttp ªº¤èªk¨ú±oºô­¶¤¤¦p¤U¹Ïªº¸ê®Æ¡G

¤U¸ü (32.86 KB)

2017-6-26 17:58

1. Sub WebData()
   
2.     Dim strURL$
   
3.     Dim respAA
   
4.     strURL = "https://tw.stock.yahoo.com/q/q?s=2330" 'ºô­¶¦a§}
   
5.     [A1].CurrentRegion = ""
   
6.     Dim oXmlhttp As Object, oHtmldoc As Object
   
7.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   
8.     Set oHtmldoc = CreateObject("htmlfile")
   
9.     With oXmlhttp
   
10.         .Open "Get", strURL, False
    
11.         .send
    
12.         
    
13.         '-------------------------------------------------
    
14.         '¦p¦ó¨ú±oºô­¶¤¤ªºªí®æ¸ê®Æ?
    
15.         '¤Î ªí®æ¤¤ ²Ä3Äæ²Ä2¦C¸ê®Æ?
    
16.         '-------------------------------------------------
    
17.         
    
18.         
    
19.     End With
    
20. End Sub

½Æ»s¥N½X

«D±`·P®¦

Scott090

¦^´_ 1# Scott090


½Ð°Ý¨º¤@¦ì°ª¤âÄ@·N¨ó§U

ºô­¶¦a§}¦p¤U¡Gl   
https://tw.stock.yahoo.com/q/q?s=2330

ÁÂÁÂ

Scott090

¦^´_ 3# prin.huang


    «D±`·P®¦
´£¨Ñªºcode ¨¬·í¤@­Ó sample¡A ±N¦n¦nªº¬ãŪ¡A¤£¸Ñ¤§³B¦A½Ð¯q´N±Ð

¦A¦¸ªºÁÂÁÂ

Scott090

¦^´_ 5# GBKEE


    ¤Ó¦n¤F¡A¤S±o¨ì¶W¯Åª©¥Dªº¤j§@
¦Û·í¦n¦nªº¬ã²ß

·PÁÂ

Scott090

¦^´_ 3# prin.huang

Sub main() §@­×¹¢¦p¤U¡A½Ð°Ñ¦Ò

1. Sub main()
   
2. Dim URL$, VV As Boolean, AB() As String
   
3. 
   
4. URL = "https://tw.stock.yahoo.com/q/q?s=2330"
   
5. 
   
6. 'AB = GetWebTb1(URL, 6, 1, 1, VV)
   
7. 'If VV = True Then ActiveSheet.Range("A1") = AB
   
8. 
   
9. AB = GetWebTb1(URL, 7, 1, 1, VV)    '¥Ø¼Ðªí®æ¥Ñ1¶}©l­pºâ¬O²Ä7­Ó
   
10. If VV = True Then ActiveSheet.Range("A1").Resize(UBound(AB, 1) + 1, UBound(AB, 2) + 1) = AB     'AB°}¦C©ñ¤J¤u§@ªíÀx¦s®æ
    
11. 
    
12. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 8# prin.huang


    ¥Î QueryTablesªº¤è¦¡Ą̊úºô­¶¸ê®Æ¡A¿ý»sµ{¦¡½X¡A
µ{¦¡½X¤º .WebTables = "7" ´N¬O¥Ñ1ºâ°_±Æ¦æ²Ä7­Óªí®æ

1. Sub ¥¨¶°1()
   
2. '
   
3. ' ¥¨¶°1 ¥¨¶°
   
4. '
   
5. 
   
6. '
   
7.     With ActiveSheet.QueryTables.Add(Connection:= _
   
8.         "URL;https://tw.stock.yahoo.com/q/q?s=2330", Destination:=Range("$A$1"))
   
9.         .Name = "q?s=2330_1"
   
10.         .FieldNames = True
    
11.         .RowNumbers = False
    
12.         .FillAdjacentFormulas = False
    
13.         .PreserveFormatting = True
    
14.         .RefreshOnFileOpen = False
    
15.         .BackgroundQuery = True
    
16.         .RefreshStyle = xlInsertDeleteCells
    
17.         .SavePassword = False
    
18.         .SaveData = True
    
19.         .AdjustColumnWidth = True
    
20.         .RefreshPeriod = 0
    
21.         .WebSelectionType = xlSpecifiedTables
    
22.         .WebFormatting = xlWebFormattingNone
    
23.         .WebTables = "7"            'Åã¥Ü©Ò­nªº¸ê®Æªí®æ¬O²Ä7­Ó
    
24.         .WebPreFormattedTextToColumns = True
    
25.         .WebConsecutiveDelimitersAsOne = True
    
26.         .WebSingleBlockTextImport = False
    
27.         .WebDisableDateRecognition = False
    
28.         .WebDisableRedirections = False
    
29.         .Refresh BackgroundQuery:=False
    
30.     End With
    
31.     ActiveWorkbook.Connections("³s½u").Delete
    
32. End Sub

½Æ»s¥N½X

¥H¤W¡A½Ð°Ñ¦Ò
ÁÂÁÂ

Scott090

µo²{³o­Óºô¯¸«Üºë±m¡A¥DÃD¬O¡G
"ImportXML, GoogleLookup alternatives for Excel"
http://www.ideativi.it/blog/532/importxml_googlelookup_alternatives_for_excel.aspx

¤º§t¤@­ÓEXCELÀÉ¥i¨Ñ°Ñ¦Ò¡G
" •16/09/2013 importxml.1.10.xlsx (first public release)"

¥H¤W¤À¨É