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

[µo°Ý] ½Ð°Ý¦³¤°»ò¤èªk¥i¥H¦bexcel¤W¦Û°Ê§ó·s¥Ø«e©Ò¦³¤W¥«ÂdªÑ²¼¦¬½L»ù

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-13 13:27 ½s¿è

¦^´_ 12# peter460191
§Aªº°ÝÃD¤w¦b http://forum.twbts.com/thread-3704-1-1.html °Q½×¹L
§Úªº¯à¤O¥u¯àÀ°§A¶×¤J¤å¦r

TOP

¦^´_ 13# GBKEE


    «D±`ÁÂÁÂGBKEE ª©¥D
peter460191

TOP

¥H¤Uªºµ{¦¡½X´£¨Ñ°Ñ¦Ò......


Sub ¦¨¥æ¸ê°T¨ú±o()
    Application.ScreenUpdating = False
    DATE_REQ = CDate(InputBox("½Ð¿é¤J¥æ©ö¤é´Á, ®æ¦¡ 2011/9/6", , Format(Now(), "yyyy/m/d")))
   
    yyyymm = Year(DATE_REQ) & Format(Month(DATE_REQ), "00")
    yyyymmdd = Year(DATE_REQ) & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
    yyymmdd = Year(DATE_REQ) - 1911 & "/" & Format(Month(DATE_REQ), "00") & "/" & Format(Day(DATE_REQ), "00")
    Application.DisplayAlerts = False
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/genpage/Report" & yyyymm & "/A112" & yyyymmdd & "ALLBUT0999_1.php?select2=ALLBUT0999&chk_date=" & yyymmdd & "" _
        , Destination:=Range("A1"))
        .Name = "04"
        .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 = "10"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").ColumnWidth = 9.63
    Rows("4:4").Select
    Selection.Delete Shift:=xlUp
    Range("C4").Select
end sub

TOP

¦^´_ 16# jsleee

·PÁ  jsleee
«ö·Ó©Ò´£¨Ñªºµ{¦¡¯uªº¥i¥H¥Î­C!
¦ý¦n¹³¥u¦³¤W¥«ªº¡A¦pªG·Q­n¤W¥«¥[¤WÂd¡B¿³Âd¤Î¥iÂà¶Å(CB)­n¦p¦ó­×§ï,ÁÂÁ±zªº«ü¾É¡C
peter460191

TOP

¿³Âdªºµ{¦¡½X¦p¤U¡G

Sub ¿³Âd()
    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://nweb.gretai.org.tw/emgstk/ch/emgstk.htm", Destination:=Range( _
        "$A$1"))
        .Name = "emgstk_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    Columns("A:A").ColumnWidth = 15.88
    Columns("B:B").ColumnWidth = 13.13
End Sub

TOP

¥iÂà¶Åªº³¡¤À°Ñ¦Ò¦p¤U¡G

Sub ¥iÂà¶Å()
    Application.ScreenUpdating = False
    DATE_REQ = CDate(InputBox("½Ð¿é¤J¥æ©ö¤é´Á, ®æ¦¡ 2011/9/6", , Format(Now(), "yyyy/m/d")))
   
    yyyymmdd = Year(DATE_REQ) & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
    Application.DisplayAlerts = False
   
    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.otc.org.tw/ch/inc/js/ReportCSV.php?NAME=/ch/bond_trading_info/bonds_info/daily/data/rsta0113." & yyyymmdd & "-C.csv&SHOW=1&LEVEL=4" _
        , Destination:=Range("$A$1"))
        .Name = "rsta0113." & yyyymmdd & "-C.csv&SHOW=1&LEVEL=4_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    Columns("A:A").ColumnWidth = 17.75
End Sub

TOP

¤WÂdªº³¡¤À ¦p¤U¡G

Sub ¤WÂd()
    Application.ScreenUpdating = False
    DATE_REQ = CDate(InputBox("½Ð¿é¤J¥æ©ö¤é´Á, ®æ¦¡ 2011/9/6", , Format(Now(), "yyyy/m/d")))
   
    yyymmdd = Year(DATE_REQ) - 1911 & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
    Application.DisplayAlerts = False
    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.otc.org.tw/ch/stock/aftertrading/DAILY_CLOSE_quotes/RSTA3104_" & yyymmdd & ".html" _
        , Destination:=Range("$A$1"))
        .Name = "RSTA3104_" & yyymmdd & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    Columns("A:A").ColumnWidth = 7
End Sub

TOP

¦^´_ 21# jsleee


    «D±`·PÁÂ jsleee
¯uªº«D±`ÁÂÁ§A¡A§Ú·|ªá¤@ÂI®É¶¡¦n¦n¬ã¨s¬ã¨s¡C
peter460191

TOP

¥»©«³Ì«á¥Ñ peter460191 ©ó 2011-9-16 13:38 ½s¿è

¦^´_ 18# peter460191


    ¦A½Ð±Ð  jsleee «e½ú

§Ú§â¥þ³¡ªºµ{¦¡©ñ¦b¤@°_¡A¦ý¥u·|¥X²{¤@ºØ¡A·Q½Ð°Ý­n¦p¦óÅý¥¦¦P®É¥X²{¡A¥t¥~Åܧó¤é´Á¡A°£¤F³z¹L¥¨¶°°õ¦æ¥~¡AÁÙ¦³§ó§Ö©Î§ó¤è«Kªº¤èªk¶Ü !½Ð°Ý¦³¤èªkÅýÀɮ׶}±Ò´N¦Û°Ê°õ¦æ³Ì·sªº¤é´Á¶Ü?
½Ðjsleee «e½ú«ü¾É¤@¤U¡A¦A¤@¦¸·PÁ±z!
peter460191

TOP

¦^´_ 25# peter460191

¸Õ¸Õ¬Ý¥H¤Uªºµ{¦¡½X¡G¦ý½Ð¥ý¦b§Aªº Excel ¤u§@ªí «Ø¥ß¤T­Ó¤u§@ªí¡A¦WºÙ¤À§O¬° "¤WÂd"¡B"¥iÂà¶Å"¡B"¿³Âd"¡A§_«hµ{¦¡·|¥X¿ù....


Sub ¤WÂd_¥iÂà¶Å_¿³Âd()
    '¤WÂd
    Application.ScreenUpdating = False
    Sheets("¤WÂd").Select
    DATE_REQ = CDate(Format(Now(), "yyyy/m/d"))
   
PPP1:    yyymmdd = Year(DATE_REQ) - 1911 & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
    Application.DisplayAlerts = False
    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.otc.org.tw/ch/stock/aftertrading/DAILY_CLOSE_quotes/RSTA3104_" & yyymmdd & ".html" _
        , Destination:=Range("$A$1"))
        .Name = "RSTA3104_" & yyymmdd & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    On Error GoTo 0
    Columns("A:A").ColumnWidth = 7
    If Cells(1, "A") = "" Then
        DATE_REQ = DATE_REQ - 1
        GoTo PPP1
    End If

   
    Sheets("¥iÂà¶Å").Select
    yyyymmdd = Year(DATE_REQ) & Format(Month(DATE_REQ), "00") & Format(Day(DATE_REQ), "00")
    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.otc.org.tw/ch/inc/js/ReportCSV.php?NAME=/ch/bond_trading_info/bonds_info/daily/data/rsta0113." & yyyymmdd & "-C.csv&SHOW=1&LEVEL=4" _
        , Destination:=Range("$A$1"))
        .Name = "rsta0113." & yyyymmdd & "-C.csv&SHOW=1&LEVEL=4_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    On Error GoTo 0
    Columns("A:A").ColumnWidth = 17.75
   
    '¿³Âd
    Sheets("¿³Âd").Select
    Cells.Select
    On Error Resume Next
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://nweb.gretai.org.tw/emgstk/ch/emgstk.htm", Destination:=Range( _
        "$A$1"))
        .Name = "emgstk_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
    On Error GoTo 0
    Columns("A:A").ColumnWidth = 15.88
    Columns("B:B").ColumnWidth = 13.13

End Sub

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD