- 帖子
- 1572
- 主題
- 16
- 精華
- 2
- 積分
- 1521
- 點名
- 0
- 作業系統
- xp
- 軟體版本
- office 2003
- 閱讀權限
- 150
- 性別
- 男
- 註冊時間
- 2010-5-1
- 最後登錄
- 2016-1-13
|
6#
發表於 2011-10-18 11:17
| 只看該作者
依你現有代碼，加上循環就行了。
此例取得今年的資料：- Sub Macro1()
- d = DateValue("2011/10/17")
- i = 3
- Do
- dt = Application.Text(d, "yyyy/mm/dd")
- Set s = Sheet2
- s.UsedRange.Clear
- With s.QueryTables.Add(Connection:= _
- "URL;http://www.trademag.org.tw/ExchangeRate.asp?vdate=" & dt, _
- Destination:=s.Range("$A$1"))
- .Name = "17"
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .BackgroundQuery = True
- .RefreshStyle = xlInsertDeleteCells
- .SavePassword = False
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "2"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- End With
- With Sheet1
- For j = s.[iv3].End(1).Column To 3 Step -1
- a = s.Cells(3, j).Resize(41, 1).Value
- .Cells(i, 2) = s.Cells(1, j)
- .Cells(i, 3).Resize(1, 41) = Application.Transpose(a)
- i = i + 1
- Next
- End With
- d = d - 7
- Loop Until Year(d) < 2011
- End Sub
|
|
發表於 2011-10-17 17:24
| 
