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

[µo°Ý] [½Ð±Ð]­ÓªÑ¦¨¥æ¸ê°T¥HVBAÂ^¨ú¤§°ÝÃD...

[µo°Ý] [½Ð±Ð]­ÓªÑ¦¨¥æ¸ê°T¥HVBAÂ^¨ú¤§°ÝÃD...

¦U¦ì¤j¤j½Ð±Ð¤@¤U:

§Ú·Q±qÃÒ¥æ©Òªº­ÓªÑ¤é¦¨¥æ¸ê°TÂ^¨ú¸ê®Æ,
§Æ±æ¬O¯à°÷¥ÑEXCELªí®æ¤º¿é¤J"ªÑ²¼¥N½X"&"¦~"&"¤ë",¨Ó§ó·sdata,

¥H¤U¥¨¶°¬O°Ñ¦Ò§O¤Hªº¼gªk¶i¦æ­×§ï,
¦ý¬O¤@ª½¨S¿ìªk±N¸ê®Æ§ì¶i¨Ó,
½Ð°Ý¦U¦ì¤j¤j¬O¥¨¶°¦³­þ¸Ì¼g¿ù¤F¶Ü?
ÁÙ¬O¥i¥H¦³§ó¦nªº¼gªk???

ÁÂÁÂ!!
  1. Sub ­ÓªÑ¤é¦¨¥æ¸ê°T()
  2. '
  3.     Dim ptxt As String
  4.     Dim STK_NO, myear, mmon
  5.     myear = InputBox("¿é¤J¬d¸ß¥Á°ê¦~", "­ÓªÑ¤é¦¨¥æ¸ê°T")
  6.     If myear = "" Then Exit Sub
  7.     mmon = InputBox("¿é¤J¬d¸ß¤ë", "­ÓªÑ¤é¦¨¥æ¸ê°T")
  8.     If mmon = "" Then Exit Sub
  9.         STK_NO = InputBox("¿é¤J­ÓªÑ¥N½X", "­ÓªÑ¤é¦¨¥æ¸ê°T")
  10.     If STK_NO = "" Then Exit Sub
  11.     With ActiveSheet.QueryTables.Add(Connection:= _
  12.         "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/STOCK_DAYMAIN.php" _
  13.         , Destination:=Range("A1"))
  14.         .PostText = "ajax=true&myear=" & myear & "&mmon=" & mmon & "&STK_NO=" & STK_NO
  15.         .PreserveFormatting = False
  16.         .RowNumbers = False
  17.         .FillAdjacentFormulas = False
  18.         .RefreshOnFileOpen = False
  19.         .BackgroundQuery = True
  20.         .RefreshStyle = xlInsertDeleteCells
  21.         .SavePassword = False
  22.         .SaveData = True
  23.         .AdjustColumnWidth = True
  24.         .RefreshPeriod = 0
  25.         .WebSelectionType = xlSpecifiedTables
  26.         .WebFormatting = xlWebFormattingNone
  27.         .WebTables = "8"
  28.         .WebPreFormattedTextToColumns = True
  29.         .WebConsecutiveDelimitersAsOne = True
  30.         .WebSingleBlockTextImport = False
  31.         .WebDisableDateRecognition = False
  32.         .WebDisableRedirections = False
  33.         .Refresh BackgroundQuery:=False
  34.             
  35.     End With
  36. End Sub
½Æ»s¥N½X
·s¤â¤W¸ô

¦^´_ 1# cudui


    Sub ­ÓªÑ¤é¦¨¥æ¸ê°T()
'
'
    Dim ptxt As String
    'Dim STK_NO, mmon As String
    myear = InputBox("¿é¤J¬d¸ß¥Á°ê¦~", "­ÓªÑ¤é¦¨¥æ¸ê°T")
   
    If myear = "" Then
       Exit Sub
    Else
       myear = myear + 1911
    End If
    mmon = InputBox("¿é¤J¬d¸ß¤ë,1-9¤ë»Ý¸É0¦p02¤ë", "­ÓªÑ¤é¦¨¥æ¸ê°T")
    If mmon = "" Then Exit Sub
   
    STK_NO = InputBox("¿é¤J­ÓªÑ¥N½X", "­ÓªÑ¤é¦¨¥æ¸ê°T")
    If STK_NO = "" Then Exit Sub
           
    mym = myear & mmon
     With ActiveSheet.QueryTables.Add(Connection:= _
         "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & mym & "/" & mym & "_F3_1_8_" & STK_NO & ".php?STK_NO=" & STK_NO & "&myear=" & myear & "&mmon=" & mmon _
        , Destination:=Range("A1"))
'
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
        .WebTables = "8"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
           
   
End Sub

     ¸Õ¸Õ¬Ý§a!!

TOP

¦^´_ 2# chen_cook


ÁÂÁ¤j¤j, §Ú¦³¸Õ¹L¥i¦æ¤F!!

¥t¥~½Ð°Ý...
¥H¤U³o¤GºØ¼gªkªº·N¸q¦³¤£¦P¶Ü?
¬°Ô£²Ä¤@ºØ´N±o¤£¥Xdata©O?¬O¤£¬O¤Ö¼g¤°»ò??
(²Ä¤GºØ¤ñ¸û¬Ý±oÀ´,¦]¬°¼gIQY®É¤]¬O¥H³s¨ì¬Û¦PªºURL¥h§ìdata)
  1.     With ActiveSheet.QueryTables.Add(Connection:= _
  2.         "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/STOCK_DAYMAIN.php" _
  3.         , Destination:=Range("A1"))
  4.         .PostText = "ajax=true&myear=" & myear & "&mmon=" & mmon & "&STK_NO=" & STK_NO
½Æ»s¥N½X
  1.      With ActiveSheet.QueryTables.Add(Connection:= _
  2.          "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & mym & "/" & mym & "_F3_1_8_" & STK_NO & ".php?STK_NO=" & STK_NO & "&myear=" & myear & "&mmon=" & mmon _
  3.         , Destination:=Range("A1"))
  4. '
½Æ»s¥N½X
·s¤â¤W¸ô

TOP

¦U¦ì¤j¤j¦A½Ð±Ð¤@¤U,
¦pªG­n³]©w¦¨¸ê®Æ¬OÁa¦V´¡¤J (°õ¦æ¥¨¶°«á,´¡¤J·s¸ê®Æ,¦Ó­ì¸ê®Æ¦V¤U²¾)
¸Ó«ç»ò³]©w©O???

TKS!
·s¤â¤W¸ô

TOP

¦^´_ 3# cudui


    §A­ì©lªºHTTP:¬q¤£¬O²£¥Í¸ê®Æ«áªººô­¶,·íµM§ì¤£¨ì°Ú!!!

TOP

¦^´_ 4# cudui


    ³o¸ê®Æ¬O·|Åܰʪº(¦pªG¤£¬O­Ó¾ã¤ë),©Ò¥HÀ³¸Ó·í¸ê®Æ¨Ó·½,¦b¥t¤@­Ósheet®M¤J(©Î¨ä¥¦Äæ¦ì®M¥Î¸ê®Æ)...
    ½Ð¤W¶Ç§AªºÀɮקa!!ª©¤j·|¨ó§Uªº!!!

TOP

¦^´_ 6# chen_cook


    ÁÂÁ¤j¤j~
   §ÚÁÙ¬O¤p¾Ç¥Í,©Ò¥H¥u¯à¬Ý¤£¯à¦¬¤]¤£¯à¶Ç~~>_<

   §Úªº·Qªk¬O¨Ó·½¸ê®Æ¨C¦¸°õ¦æ¥¨¶°³£¦b¦P¤@­Ósheet·s¼W,&±Æ§Ç&§R°£¬Û¦P¦C,
   ¦A¨ì§Oªºsheet¥h¶i¦æ¹B¥Î,
   ³o¼Ë¤ñ¸û¤£·|¶}¤Ó¦hsheet...
   ¤S©Î°õ¦æ¤@¦¸¥¨¶°,´N¯à¤@¦¸¼´¨ì­ÓªÑªñ4~5­Ó¤ëªº¦¨¥æ¦æ±¡,
   ¥u¬O"¸ó¦~"ªº¼gªk¹ï¤p¾Ç¥Í¨Ó»¡´N¦³ÂIÃø¤F...(ex 101.3¤ë/2¤ë/1¤ë , 100¦~12¤ë/11¤ë)
·s¤â¤W¸ô

TOP

¦^´_ 7# cudui

¸Õ¤FActiveCell.Offsetªº«ü¥O,
³o¼Ë´N¥i¥H¸Ñ¨M¸ê®Æ¦V¤U©µ¦ù,
¥u³Ñ¦p¦ó¼g¥X ³sÄò§ì´X­Ó¤ë(§t¦Û°Ê§P§O"¸ó¦~")ªº°ÝÃD¤F!!
  1.         
  2.    lastdata = Range("A65536").End(xlUp).Select   
  3.    
  4.     mym = myear & mmon
  5.      With ActiveSheet.QueryTables.Add(Connection:= _
  6.          "URL;http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/genpage/Report" & mym & "/" & mym & "_F3_1_8_" & STK_NO & ".php?STK_NO=" & STK_NO & "&myear=" & myear & "&mmon=" & mmon _
  7.         ,Destination:=ActiveCell.Offset(3, 0))
½Æ»s¥N½X
·s¤â¤W¸ô

TOP

¦^´_ 8# cudui


    For .. next °j°é°t¦X§Aªº Offest,,¥i¥H¹F¦¨§A­nªºµ²ªG??¦p¦ó°µ¥ý¦Û¤v·Q§a!!!
    ¤£À´§Aªº³sÄò5­Ó¤ëªº·N«ä .. ¦pªG¬O¥»¦¸§ì¨ú¬°°_©lªº¸Ü mmon + 1 ±À¶i,¦P¬O§PÂ_¬O§_¤j©ó12¶i¦ì¦~ myear + 1..
    À³¸Ó¦b¨M©w³o­Óvbaªº¥Î³~®É,´N±N·Qªk³]©w¦n...ª©¤j¥u·|¨Ì§Aªº°ÝÃD´£¥X¥i¦æªº§@ªk,·íµMÁÙ¦³§ó¦nªº¼gªk(§Ú¤]¥u¬OÀ´¤@ÂI,­è¦n§Aªº°ÝÃD§Ú¬ÝªºÀ´).....   
    ¥Ø«e§A¥u§ì¤W¥«,­Y¬O¤WÂd©O??¥¦´N¤£¬O¨º»ò®e©ö,­n¥hª¦¤å§äµª®×.....
     ªÑ²¼¥N¸¹­Y¿ù»~¬O§ì¤£¨ìdatabase??³o³¡¥÷§A·Q¨ì¤F¶Ü??

TOP

¦^´_ 9# chen_cook


    ÁÂÁÂcook´£¿ô..
    ¤W¥«¤WÂdªº¦æ±¡,§Ú³£¦³§ä¨ìiqyªº¼gªk,
    ©Ò¥H¥u³Ñ¤U½Ķ¦¨vbaªº»yªk,¦³¤F¥i°Ñ¦Òªº¼gªk,«á­±ªº©µ¦ù´N®e©ö¦h¤F...
    ¥Ñ©ódata¨Ó¦ÛÃÒ¥æ©Ò©MÂd¶R,´N¤£¥Î¾á¤ß­ÓªÑ¥N¸¹¥¿½T»P§_ªº°ÝÃD~~

    ¤§©Ò¥Hdata­n¯à©¹«e±À5­Ó¤ë(¥H­ÓªÑ¤é¦¨¥æ¦æ±¡¬°¨Ò),¬O¬°¤F­n¯à°÷ºâ¥X¥­§¡½u¡B¨ÄÂ÷²vµ¥°Ñ¼Æ...
    ¦]¬°§O¤Hªº¼gªk³£­n¨C¤Ñ§ó·s¤~¦æ,¤@¤¤Â_´N«Ü³Â·Ð,
    ©Ò¥H¤~·Q­n§ä¤èªk¸É¨¬...

    ¤p¾Ç¥Íªºµ{«×®t±o¤Ó¦h,
    ¥u¦n¨C«e¶i¤@ÂI,´Nª¦ª¦¤å¡B·Q·Q¼gªk...
    ²{¦b³£¬O§ä¤£¨ì¤H°Ýªº,¤~·|¶K¤W¨Ó°Ý°Ýª©¤j~~
·s¤â¤W¸ô

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD