- ©«¤l
- 81
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 95
- ÂI¦W
- 317
- §@·~¨t²Î
- xp
- ³nÅ骩¥»
- office 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-2-29
- ³Ì«áµn¿ý
- 2024-11-13
|
¦^´_ 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 |
|