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

¥xªÑ¤ëÀ禬

¥xªÑ¤ëÀ禬

½Ð°Ý¦³¤H¤À¨É­n¦p¦ó§ì¨ú¤½¶}¸ê°T¥xªÑªñ6¦~¥þ³¡¤ëÀ禬¸ê®Æ£v

¤ëÀ禬.rar (440.12 KB) ¦^´_ 1# ddhh4053


    ¦]»¡©ú«Ü²²¤,³o¬O§A­nªº¶Ü??

TOP

¦^´_ 3# tsuneng

monitor.JPG (389.19 KB)

monitor.JPG

TOP

¦^´_ 3# tsuneng


    Dim myWeb(3) As String
y$ = 101: m$ = 5          <---¥i­×¥¿§ì¨ú¦~¤ë

   myWeb(0) = "sii"
   myWeb(1) = "otc"
   myWeb(2) = "rotc"
   myWeb(3) = "pub"
      
For i = 0 To 3
   With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & y$ & "_" & m$ & ".html", Destination:=Cells(1, 1))
        '.Name = "zcx_" + stockCode$ + ".asp_5"
        .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 = "2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True   'False
        .Refresh BackgroundQuery:=False
    End With
    Ed = [J65535].End(xlUp).row
    Range("A1:J" & Ed).Copy Worksheets("¤ëÀ禬").Cells(x + 2, 1)
    x = Worksheets("¤ëÀ禬").[a65536].End(xlUp).row
    Cells.Select
    Selection.Delete Shift:=xlUp
   
Next i

'
End Sub

TOP

¦^´_ 5# chen_cook

ÁÂÁ¤j¤jªº¦^À³, TRY ¤j¤j¤½§iªºcode ¦³¤£©ú¥Õ³B ½Ð±Ð

1.     y$ = 101: m$ = 5       <---¥i­×¥¿§ì¨ú¦~¤ë <==> ­×§ï¦¨  Syear = Year(Date) - 1911: Smon = Month(Date) - 1     

     "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & Syear & "_" & Smon & ".html", Destination:=Cells(1, 1))

      °õ¦æ µ{¦¡«á·|¦b¤U¦æ¤§³Bµo¥Í¿ù»~¡A(°õ¦æ¶¥¬q¿ù»~,°}¦C¶W¥X½d³ò)

       Range("A1:J" & Ed).Copy Worksheets("¤ëÀ禬").Cells(x + 2, 1)

2, ­n¦p¦ó¤@¦¸¨ú±o¦h¤ë¥÷ªº¤ëÀ禬¸ê®Æ

TOP

¦^´_ 6# tsuneng


    Sub ¤ëÀ禬()
'
' Macro2 Macro
' SuperXP ¦b 2012/7/7 ¿ý»sªº¥¨¶°
'
Dim myWeb(3) As String

   myWeb(0) = "sii"
   myWeb(1) = "otc"
   myWeb(2) = "rotc"
   myWeb(3) = "pub"
     
y$ = Range("B1"): m$ = Range("C1"): Ey$ = Range("D1"): Em$ = Range("E1") 'B1&C1 ©l¦~¤ë E1&D1 ²×¦~¤ë

For Lp = 1 To (Ey$ - y$) * 12 - m$ + 1 + Em$
  x = 0: Sheets.Add.Name = "sample": Sheets("sheet1").Select
  For i = 0 To 3
   With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & y$ & "_" & m$ & ".html", Destination:=Cells(1, 1))
        '.Name = "zcx_" + stockCode$ + ".asp_5"
        .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 = "2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True   'False
        .Refresh BackgroundQuery:=False
    End With
    Ed = [J65535].End(xlUp).row
    Range("A1:J" & Ed).Copy Worksheets("sample").Cells(x + 2, 1)
    x = Worksheets("sample").[a65536].End(xlUp).row
    Cells.Select
    Selection.Delete Shift:=xlUp
  Next i
   
    Sheets("sample").Name = "C" & y$ & m$
    If m$ = 12 Then
        y$ = y$ + 1
        m$ = 1
    Else
        m$ = m$ + 1
    End If
   
    If Ey$ = y$ And Em$ < m$ Then
    Exit For
    End If
   
Next Lp

'
End Sub

TOP

¦^´_ 6# tsuneng


    y$ = 101: m$ = 5       <---¥i­×¥¿§ì¨ú¦~¤ë <==> ­×§ï¦¨  Syear = Year(Date) - 1911: Smon = Month(Date) - 1
        §Úªº°µªk¬O y$ = year(today())-1911: m$=month(today())
    §A¨S¦³ sheets("¤ëÀ禬") ¦A¥ý§ïSHEET NAME!!
   ³sÄò§ìªº¤w³qª¾§A¤F...¥ý¥Î§Úªº¶]¯à¥X²{¸ê®Æ«á,µ¥¬ÝÀ´¤F¦A¦Û¤w§ï!!

TOP

¦^´_ 3# chen_cook


    §A¦n¡A¹³2317ÂE®ü¬ÛÃöªºÀ禬¸ê®Æ³£¨S¦³¡A¬Oº|±¼¤F¶Ü?
devidlin

TOP

¦^´_ 8# devidlin


    ¨º³o¬O¤°»ò??

2317.JPG (145.52 KB)

2317.JPG

TOP

¦^´_ 9# chen_cook


  §A¦n¡A¤p§Ì«üªº¬O³o­ÓÀɮ׬°¦óµL2317ÂE®üªº¸ê®Æ¡AÁÂÁ¡C

¦X¨ÖÀ禬.zip (41.61 KB)

devidlin

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD