- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
3#
發表於 2014-4-22 20:39
| 只看該作者
回復 2# smart3135
試試看- Option Explicit
- Sub 抓季損益表資料()
- Dim Rng As Range, AR(), URL As String, E As Variant
- For E = Sheets.Count To 2 Step -1
- Sheets(E).Delete '工作表刪除
- Next
- AR = Array(2303, 2485, 2030)
- Sheets(1).Range("A1:A3") = Application.WorksheetFunction.Transpose(AR)
- Set Rng = Sheets(1).Range("A1:A3")
- For Each E In Rng '迴圈 儲存格
- ' For Each E In AR '迴圈 也可以用陣列
- With Sheets.Add(, Sheets(1)) '新增工作表
- .Name = E
- URL = "URL;https://djinfo.cathaysec.com.tw/Z/ZC/ZCQ/ZCQ.DJHTM?A=" & E
- With .QueryTables.Add(Connection:=URL, Destination:=.Range("$A$1"))
- .Name = "ZCQ.DJHTM?A=" & E
- .PreserveFormatting = True
- .BackgroundQuery = True
- .RefreshStyle = xlInsertDeleteCells
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "3"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .Refresh BackgroundQuery:=False
- End With
- End With
- Next
- End Sub
|
|
發表於 2014-4-23 09:46
| 
