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

¤¤­^¦r¦ê¥u¯d¤¤¤å

¤¤­^¦r¦ê¥u¯d¤¤¤å

¥»©«³Ì«á¥Ñ Scarlett ©ó 2021-5-28 22:26 ½s¿è

½Ð±Ð¦p¦ó§âª¦ÂΤU¨Óªº¸ê®Æ,§R°£Range("B9:B85")¤¤ªº­^¤å,¥u¯d¤U¤¤¤å?

Â^¨ú.PNG (50.39 KB)

Â^¨ú.PNG

test.rar (133.43 KB)

¦^´_ 1# Scarlett


¬Ý¨Ó¦³³W«h¡A½Ð°Ý¨Ï¥Î¸ê®Æ­åªR¥i¥H§¹¦¨¶Ü?

TOP

¦^´_ 1# Scarlett

³o¬Oºô¸ô¤W§äªºASC + MID ¤Á³Î ¤èªk ¯ÊÂI¬O «ÜºC ¦]¬°¬O¶]¨C¤@­Ó¦r ¬Ý¬Ý¦³¨S¦³¤j¤jÄ@·NÀ°¦£ À³¸Ó¦³¨ä¥L¤è¦¡ ¥i¥Hª½±µ¶K¾ã­Óªí®æ
test0529.rar (138.25 KB)

TOP

¥»©«³Ì«á¥Ñ quickfixer ©ó 2021-5-30 00:33 ½s¿è

01¾Ç¨Óªº
±q­ì©l¸ê®Æ³B²z·|¤ñ¸û§Ö,¤£¥Î1¬í¾ã­¶¥þ§ì¤U¨Ó,33­Óªí®æ,¦Û¤v¬Ý­n¯d¤°»ò
ÅܼÆstock    strYear    strSeason,¦Û¤v´«¤W
Sub test()
    Dim URL As String, HTMLsourcecode As Object, GetXml As Object
    Set HTMLsourcecode = CreateObject("htmlfile")
    Set GetXml = CreateObject("msxml2.xmlhttp")
    URL = "https://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=2012&SYEAR=2020&SSEASON=3&REPORT_ID=C"
    Cells.Clear
    Application.ScreenUpdating = False
    With GetXml
        .Open "GET", URL, False
        .send
        HTMLsourcecode.body.innerhtml = convertraw(.ResponseBody)
        For k = 0 To HTMLsourcecode.all.tags("table").Length - 1
            Set Table = HTMLsourcecode.all.tags("table")(k).Rows
            For i = 0 To Table.Length - 1
                lastrow = lastrow + 1
                For j = 0 To Table(i).Cells.Length - 1
                    If InStr(Table(i).Cells(j).innerhtml, "SPAN class=zh") > 0 Then
                    ActiveSheet.Cells(lastrow, j + 1) = Trim(Replace(Split(Table(i).Cells(j).innerhtml, "</SPAN>")(0), "<SPAN class=zh>", ""))
                    Else
                    ActiveSheet.Cells(lastrow, j + 1) = Trim(Table(i).Cells(j).innertext)
                    End If
                Next j
            Next i
        Next k
    End With
    Application.ScreenUpdating = False
    Set HTMLsourcecode = Nothing
    Set GetXml = Nothing
End Sub
Function convertraw(rawdata)
    Dim rawstr
    Set rawstr = CreateObject("adodb.stream")
    With rawstr
        .Type = 1
        .Mode = 3
        .Open
        .Write rawdata
        .Position = 0
        .Type = 2
        .Charset = "big5"
        convertraw = .ReadText
        .Close
    End With
    Set rawstr = Nothing
End Function

TOP

¥»©«³Ì«á¥Ñ Scarlett ©ó 2021-5-30 11:59 ½s¿è

¦^´_ 4# quickfixer ÁÂÁ¤j¤j,§Ú¬O¼g¦bEnd Sub¤§¤W¡C

TOP

¥»©«³Ì«á¥Ñ Scarlett ©ó 2021-5-30 12:00 ½s¿è

¦^´_ 3# °a¤ªºµÁÂÁ¤j¤j,³o´N§Ú·Q­nªº®æ¦¡¡A¦a½T³t«×¤]¬O­Ó°ÝÃD¡C

TOP

Sub zz()
Dim a
a = Range("b9:b" & [b65536].End(3).Row).Value
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "[\!-~\s]*"
    For i = 1 To UBound(a)
        a(i, 1) = .Replace(a(i, 1), "")
    Next
    [b9].Resize(i - 1, 1) = a
End With
End Sub

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-5-31 03:37 ½s¿è

¦^´_ 6# Scarlett

·PÁ ikboy «e½ú ªº¥¿«hªí¹F¦¡ §ï¤F¤@¤U ³t«×§Ö«Ü¦h¤F  ÁÂÁÂ

test0531.rar (176.79 KB)

TOP

¦^´_ 6# Scarlett

©êºp¨Sª`·N¨ì ³o¬q§ï¤@¤U ·PÁÂ

    IE.Quit
'    Range("B10:B85").Delete
    [A7].Resize(i, j) = Brr
    Columns.AutoFit

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD