- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-10 05:55 ½s¿è
¦^´_ 3# bhsm
¥u¯d£¸±i[±`¥Î¥N½X] ¤u§@ªí´N¥i¥H- Dim Sh As Worksheet
- Sub ¥Dµ{¦¡()
- Dim Rng As Range, AR(1 To 2) As String, Web_Table(1 To 2), i As Integer, R As Range
- AR(1) = "URL;https://tw.stock.yahoo.com/q/q?s=xxxx"
- AR(2) = "URL;https://tw.stock.yahoo.com/d/s/company_xxxx.html"
- Web_Table(1) = "7"
- Web_Table(2) = "8"
- Set Sh = Sheets("±`¥Î¥N½X")
- Set Rng = Sh.[a2:a27]
- Rng.Interior.ColorIndex = xlNone
- Rng.Offset(, 1).Resize(, 4) = ""
-
- Web¬d¸ß§R°£
- Web¬d¸ß»s©w
- On Error GoTo L2
-
- For Each R In Rng
- For i = 1 To 2
- With Sh.QueryTables("_" & i)
- .Connection = Replace(AR(i), "xxxx", Trim(R))
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = Web_Table(i)
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = True
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False 'ªÑ¸¹¿ù»~®É·|¦³¿ù»~
- If i = 1 Then
- R.Cells(1, 2) = Mid(.ResultRange.Cells(3, 1), Len(Trim(R)) + 1)
- R.Cells(1, 3) = .ResultRange.Cells(3, 3)
- R.Cells(1, 3).NumberFormatLocal = "#0.00"
- Else
- R.Cells(1, 4) = .ResultRange(3, 4)
- End If
- End With
- Next
- R.Cells(1, 5) = Val(R.Cells(1, 4)) / R.Cells(1, 3)
- R.Cells(1, 5).NumberFormatLocal = "0.00%"
- L1:
- Next
-
- Web¬d¸ß§R°£
- Exit Sub
- L2:
- Err.Clear
- R.Interior.Color = vbYellow
- GoTo L1
- End Sub
- Private Sub Web¬d¸ß»s©w()
- Dim i As Integer
- For i = 1 To 2
- With Sh.Range("AA" & IIf(i = 1, 1, 50))
- With Sh.QueryTables.Add("URL;about:Tabs", .Cells) 'ªÅªººô§}
- .Name = "_" & i
- .Refresh BackgroundQuery:=False
- End With
- End With
- Next
- End Sub
- Private Sub Web¬d¸ß§R°£()
- Dim Q As QueryTable
- For Each Q In Sh.QueryTables
- Q.ResultRange.Clear
- Q.Delete
- Next
- End Sub
½Æ»s¥N½X |
|