- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 260
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-12-1
              
|
2#
發表於 2013-3-27 10:36
| 只看該作者
回復 1# leondavinci727 - Sub activateMacro()
- Dim dic As Object, ky, A As Range, r%
- Set dic = CreateObject("Scripting.Dictionary")
- Dim com_no As String
- com_no = Worksheets("類D").Range("$K$3").Value
- With ActiveSheet.QueryTables("類D")
- .Connection = "URL;http://www.twse.com.tw/ch/trading/exchange/BFIAMU/genpage/Report201303/" & com_no & "_F3_1_5.php?chk_date=102/03/26"
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .BackgroundQuery = True
- .RefreshStyle = xlOverwriteCells
- .SavePassword = False
- .SaveData = True
- .AdjustColumnWidth = False
- .RefreshPeriod = 0
- .WebFormatting = xlWebFormattingNone
- .WebTables = "8"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- End With
- r = 35
- Do Until Cells(r, 1) = ""
- dic(Cells(r, 1).Value) = Range(Cells(r + 1, 1), Cells(r + 1, 1).End(xlToRight)).Value
- r = r + 2
- Loop
- For Each ky In dic.keys
- With Sheets(ky)
- Set A = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
- A.Resize(, UBound(dic(ky), 2)) = dic(ky)
- End With
- Next
- End Sub
|
|
發表於 2013-3-27 10:02
| 
